13 Replies Latest reply on Apr 13, 2015 7:44 AM by Steven Hawkins

    Is it possible to connect to a vdb through php code

    Nandini K J Newbie

      Hi Everyone,

      I am a newbie and i am working on JBoss Studio and Virtual Databases for the first time. I need to connect to the virtual database which is deployed on the Teiid Server through my php code. Is it possible to connect to a teiid database using php code. If yes, then how to connect to the vdb. Any suggestions will be really helpful.

       

      For mysql database connection we use the following syntax,

      $con = mysql_connect($server, $username, $password) or die ("Could not connect: " . mysql_error());

       

      For teiid vdb what is the syntax to be used?

       

      Thanks in advance

      Nandini K J

        • 1. Re: Is it possible to connect to a vdb through php code
          Steven Hawkins Master

          You have several options. Teiid's ODBC transport uses the postgresql protocol.  Most native postgresql clients will work effectively connecting to us on our ODBC port, or you can use the postgresql ODBC client through php.

          • 2. Re: Is it possible to connect to a vdb through php code
            Nandini K J Newbie

            Thank you so much for your reply Steven.

            Is there any tutorial or sample codes on how to use the postgresql ODBC client through php.

            • 4. Re: Is it possible to connect to a vdb through php code
              Nandini K J Newbie

              Hi,

              I am able to connect to the teiid vdb using odbc through php code.

              The php code contains queries to connect to the mysql database, but i want to connect to the teiid database. Should i change all the mysql queries to odbc_execute queries or is there any way for the odbc connector to execute mysql queries.

               

              Thanks

              Nandini

              • 5. Re: Is it possible to connect to a vdb through php code
                Steven Hawkins Master

                > Should i change all the mysql queries to odbc_execute queries or is there any way for the odbc connector to execute mysql queries

                 

                Most ansi sql queries should work against Teiid without an issue.  If there are mysql specific functions or syntax, then the queries will need to be updated.

                • 6. Re: Is it possible to connect to a vdb through php code
                  Nandini K J Newbie

                  I have mysql functions such as mysql_query($sqlqry), mysql_fetch_array($result1), mysql_fetch_object($result1) and mysql_num_rows($result). Should i need to update these queries.

                   

                  I got the following error when tried to display a table from teiid vdb using php code.

                   

                  Warning: odbc_exec() [function.odbc-exec]: SQL error: ERROR: TEIID30504 OST: S1009 Unexpected exception while translating results: Cannot convert value '0000-00-00 00:00:00' from column 31 to TIMESTAMP. org.teiid.jdbc.TeiidSQLException: TEIID30504 OST: S1009 Unexpected exception while translating results: Cannot convert value '0000-00-00 00:00:00' from column 31 to TIMESTAMP.; Error while executing the query, SQL state S1009 in SQLExecDirect in C:\Documents and Settings\nandini_jayaram\My Documents\Ampps\www\php\ost.php on line 13

                  • 7. Re: Is it possible to connect to a vdb through php code
                    Steven Hawkins Master

                    > Should i need to update these queries.

                     

                    You need to differentiate between the php hooks - mysql_query, mysql_* - and the SQL that is being used.  More than likely yes, you'll need to change the php mysql specific functions - they are also deprecated PHP: mysql_query - Manual

                     

                    > I got the following error when tried to display a table from teiid vdb using php code.

                     

                    This is a legacy behavior of mysql. 0000-00-00 00:00:00 is not a valid JDBC timestamp - see [TEIID-1928] PSQL emulation layer has problems converting timestamp '0000 - JBoss Issue Tracker  The resolution is to set the mysql connection property zeroDateTimeBehavior to convertToNull. 

                    • 8. Re: Is it possible to connect to a vdb through php code
                      Nandini K J Newbie

                      Thanks Steven. By setting the mysql connection property zeroDateTimeBehavior to convertToNull solved my problem. But I am having an issue while trying to execute the sql syntax given below. The console is throwing me the error given below.


                      SELECT count(E.ticket_id) as ticket_count, S.staff_id , S.username ,ceil(avg(D.priority)) as colr,E.timestamp

                                              FROM

                                                  (SELECT b.*

                                                   FROM

                                                      (SELECT ticket_id, MAX(timestamp) AS timestamp

                                                       FROM ost_ticket_event

                                                       GROUP BY ticket_id

                                                  )

                                                  JOIN ost_ticket_event as b

                                                  ON b.ticket_id = l.ticket_id AND b.timestamp = l.timestamp

                                                  GROUP BY b.timestamp, b.ticket_id)     as E

                                              Right outer join ost_staff as S on S.staff_id = E.staff_id

                                              Left outer join `ost_ticket__cdata` as D ON D.ticket_id = E.ticket_id

                                              group by S.staff_id order by E.timestamp DESC

                       

                      Console Output:

                      Warning: odbc_exec() [function.odbc-exec]: SQL error: ERROR: TEIID30492 [S.username, E.timestampname] cannot be used outside of aggregate functions since they are not present in a GROUP BY clause. org.teiid.jdbc.TeiidSQLException: TEIID30492 [S.username, E.timestampname] cannot be used outside of aggregate functions since they are not present in a GROUP BY clause.; Error while executing the query, SQL state 50000 in SQLExecDirect in C:\Documents and Settings\nandini_jayaram\My Documents\Ampps\www\php\test.php on line 26

                      • 9. Re: Is it possible to connect to a vdb through php code
                        Steven Hawkins Master

                        That may not be the query for this exception as I don't see an E.timestampname.  But the error holds for S.username - it is not a grouping column, yet is referenced in the select outside of an aggregate.  That is not allowed in Teiid and most databases.

                        • 10. Re: Is it possible to connect to a vdb through php code
                          Nandini K J Newbie

                          I have renamed timestamp to timestampname.

                          SELECT count(E.ticket_id) as ticket_count, S.staff_id , S.username ,ceiling(avg(D.priority)) as colr

                                                  FROM

                                                      (SELECT b.*

                                                       FROM

                                                          (SELECT ticket_id, MAX(timestampname) AS timestampname

                                                           FROM ost_ticket_event

                                                           GROUP BY ticket_id

                                                      ) l

                                                      JOIN ost_ticket_event as b

                                                      ON b.ticket_id = l.ticket_id AND b.timestampname = l.timestampname

                                                      GROUP BY b.timestampname, b.ticket_id)     as E

                                                  Right outer join ost_staff as S on S.staff_id = E.staff_id

                                                  Left outer join ost_ticket__cdata as D ON D.ticket_id = E.ticket_id

                                                  group by S.staff_id order by S.staff_id

                           

                          This is the error,

                          Warning: odbc_exec() [function.odbc-exec]: SQL error: ERROR: TEIID30492 [S.username] cannot be used outside of aggregate functions since they are not present in a GROUP BY clause. org.teiid.jdbc.TeiidSQLException: TEIID30492 [S.username] cannot be used outside of aggregate functions since they are not present in a GROUP BY clause.; Error while executing the query, SQL state 50000 in SQLExecDirect in C:\Documents and Settings\nandini_jayaram\My Documents\Ampps\www\php\test.php on line 26

                          • 11. Re: Is it possible to connect to a vdb through php code
                            Steven Hawkins Master

                            It's still the same issue. Please see the last comment, the Teiid, SQL Server, PostgreSQL, etc. documentation on what can appear in the select clause when using aggregation.  You cannot have just a reference to the S.username column if it is not a grouping column.

                            • 12. Re: Is it possible to connect to a vdb through php code
                              Nandini K J Newbie

                              Hi,

                              I have the following query in which i'm using timestamp as a column name which is supposed to be keyword in mysql. How do I use a keyword as column name and how do i use it via alias(b.timestamp). I tried using back quotes and single quotes but it is not working. Please help me in solving this issue.

                               

                              SELECT count(*),priority from ost_ticket__cdata WHERE ticket_id in

                                                                      (SELECT b.ticket_id

                                                                       FROM (SELECT ticket_id, staff_id,MAX(timestamp) AS timestamp

                                                                             FROM ost_ticket_event

                                                                             GROUP BY ticket_id, staff_id

                                                                             ) l

                                                                       JOIN ost_ticket_event as b

                                                                       ON b.ticket_id = l.ticket_id AND b.timestamp = l.timestamp AND b.staff_id = ".$staff_id."

                                                                       GROUP BY b.timestamp, b.ticket_id) group by priority

                              • 13. Re: Is it possible to connect to a vdb through php code
                                Steven Hawkins Master

                                > I have the following query in which i'm using timestamp as a column name which is supposed to be keyword in mysql

                                 

                                Assuming that your SQL is for Teiid, then use the ansi standard double quoting for identifiers - b."timestamp"