-
1. Re: Is it possible to connect to a vdb through php code
shawkins Apr 7, 2015 8:13 AM (in response to nandini123)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 Apr 7, 2015 8:16 AM (in response to shawkins)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 Apr 7, 2015 8:43 AM (in response to nandini123)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 Apr 8, 2015 7:57 AM (in response to shawkins)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 Apr 8, 2015 8:28 AM (in response to nandini123)> 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 Apr 8, 2015 11:51 PM (in response to shawkins)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 Apr 8, 2015 5:03 PM (in response to nandini123)> 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 Apr 9, 2015 7:59 AM (in response to shawkins)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 Apr 9, 2015 7:32 AM (in response to nandini123)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 Apr 9, 2015 8:14 AM (in response to shawkins)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 Apr 9, 2015 8:14 AM (in response to nandini123)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 Apr 13, 2015 2:34 AM (in response to shawkins)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 Apr 13, 2015 7:44 AM (in response to nandini123)> 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"