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

    Is it possible to connect to a vdb through php code

    nandini123

      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
          shawkins

          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
            nandini123

            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.

            • 3. Re: Is it possible to connect to a vdb through php code
              shawkins

              The php client - http://php.net/manual/en/book.pgsql.php

               

              Using the postgresql odbc client - PHP: odbc_connect - Manual

              • 4. Re: Is it possible to connect to a vdb through php code
                nandini123

                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
                  shawkins

                  > 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
                    nandini123

                    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
                      shawkins

                      > 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
                        nandini123

                        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
                          shawkins

                          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
                            nandini123

                            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
                              shawkins

                              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
                                nandini123

                                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
                                  shawkins

                                  > 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"