-
1. Re: query that works on jdbc does not work on linux->php->odbc->teiid...
rareddy Apr 26, 2016 11:15 AM (in response to m.ardito)Looks like ODBC Web Server is implicitly starting a local transaction (like autoCommit=false) then on Teiid server side to support for that, data source need be defined as XA source. So, see if in your PHP script you are turning off autocommit or define the XA data source in Teiid.
-
2. Re: query that works on jdbc does not work on linux->php->odbc->teiid...
m.ardito Apr 26, 2016 11:43 AM (in response to rareddy)I don't set autoCommit=false, and in php it is "on by default", but I just tried in the php script:
using "odbc_autocommit($connection)" php dumps current setting, and in my script it prints "1" so, it's TRUE
anyway I tried also with
odbc_autocommit($connection,TRUE);
sadly, it doesn't change anything...
could there be a setting at odbc level (odbc.ini or odbcinst.ini)?
since on windows odbc works, I guess it should on linux odbc too...
could it be anything else than this autocommit setting?
instead/before starting the XA datasource journey, I would prefer to keep things simple, and make this odbc on webserver client behave like windows client do... if possible.
Marco
-
3. Re: query that works on jdbc does not work on linux->php->odbc->teiid...
rareddy Apr 26, 2016 11:50 AM (in response to m.ardito)Try using "iSQL" program that comes with "unixODBC" driver manager in Linux and issue the same query, that should tell you if it is the odbc driver or the PHP script that is turning on autoCommit. You can even turn on logging in odbc.ini file to debug and see if that might give a clue as to where this may be occurring.
Ramesh..
-
4. Re: query that works on jdbc does not work on linux->php->odbc->teiid...
m.ardito Apr 26, 2016 12:00 PM (in response to rareddy)I will try isql and report here...
btw the attached zip already contains ODBC logs...
Marco
-
5. Re: query that works on jdbc does not work on linux->php->odbc->teiid...
m.ardito Apr 26, 2016 12:14 PM (in response to m.ardito)tried isql:
as in php, until I issue a SQL query which joins only tables in the same VDB model, it works,
as soon as I try to join tables that are in different VDB models (in the same VDB), it fails...
the only output I get from iscq is
"[ISQL]ERROR: Could not SQLExecute"
as in
SQL> SELECT ag.id,al.cognome from ag left join apifdbcorsiapif.allievi as al on ag.id=al.cod_ditta WHERE ag.id=7496;
[ISQL]ERROR: Could not SQLExecute
while this works
SQL> SELECT ag.id,s.id from ag left join sedi as s on ag.id=s.id_unica where ag.id=7496;
+---------------------+---------------------+
| id | id |
+---------------------+---------------------+
| 7496 | 119306 |
| 7496 | 119307 |
| 7496 | 119308 |
+---------------------+---------------------+
SQLRowCount returns -1
3 rows fetched
it's a mistery (to me) why in windows odbc client the above always work, instead, btw...
Marco
-
6. Re: query that works on jdbc does not work on linux->php->odbc->teiid...
rareddy Apr 26, 2016 7:14 PM (in response to m.ardito)I tried the same scenario over two JDBC sources (posgres, mysql) did not see the same behavior with ODBC driver on Linux when I used the JOIN. I used Fedora 23 and latest postgresql ODBC driver I think it is 9.2.400?
Is there a store procedure in your join anywhere?
-
7. Re: query that works on jdbc does not work on linux->php->odbc->teiid...
m.ardito Apr 27, 2016 3:49 AM (in response to rareddy)Thanks for your try and info. Did you try just with isql?
I will try other setups on other machines... unfortunately this is a bit slower, and it could take some days.
I double checked, no stored procedures on jdbc backend (mysql in this case) or anywhere else are involved.
I tried joining real mtsql tables (each on different databases=> models) and also a table and a view (each on different databases=> models)
It always fail in the same way, it appears.
But I also tried in suirrelsql this connection:
jdbc:teiid:apifdb_apimn@mm://x.y.z.k:31000;autoCommitTxn=OFF
jdbc:teiid:apifdb_apimn@mm://x.y.z.k:31000;autoCommitTxn=OFF;disableLocalTxn=true
and the query that joins different models works, in both cases, btw...
isn't this strange? could this mean that the problem is anywhere else?
Can you share your other versions/settings on that machine?
my webserver client (ubuntu) has
> odbc-postgresql Version: 1:09.00.0310-2
> unixodbc Version: 2.2.14p2-5ubuntu3
> odbcinst.ini
[PostgreSQL Unicode]
Description = PostgreSQL ODBC driver (Unicode version)
Driver = psqlodbcw.so
Setup = libodbcpsqlS.so
Debug = 1
CommLog = 1
UsageCount = 1
> odbc.ini (teiid dsn) <=== do you spot any strange setting that could cause that?
[teiid]
Description = PostgreSQL Unicode
Driver = /usr/lib/i386-linux-gnu/odbc/psqlodbcw.so
Database = apifdb_apimn
Servername = x.y.z.k
Username = user
Password = pass
Port = 35432
Protocol = 7.4-1
ServerType = Postgres
ReadOnly = No
ShowSystemTables= No
ShowOidColumn = No
FakeOidIndex = No
UseServerSidePrepare =1
ByteaAsLongVarBinary =1
UpdatableCursors=0
ConnSettings =
Optimizer =0
Ksqo =0
Debug =0
Fetch = 10000
UseDeclareFetch =1
Trace = 1
TraceFile = PostgreSQL_test_trace.log
Debug = 1
DebugFile = PostgreSQL_test_debug.log
Thanks for any hint
Marco
-
8. Re: query that works on jdbc does not work on linux->php->odbc->teiid...
m.ardito Apr 27, 2016 5:12 AM (in response to m.ardito)Is there any way on teiid server side, to see clearly if the connected client is (or the issued query happens) "implicitly starting a local transaction"?
From the teiid logs maybe? Should I activate some particular trace/level?
This would help me to be more aware of what's really happening, or not, and when...
[edit]
Tried the same php script on windows/apache/php/odbc => teiid, and it works...
odbc driver there is 8.04.
[/edit]
Marco
-
9. Re: query that works on jdbc does not work on linux->php->odbc->teiid...
m.ardito Apr 27, 2016 9:22 AM (in response to m.ardito)Update: I installed a new virtual machine with basic ubuntu server 16.04 and standard LAMP stack.
then installed
- unixodbc (Version: 2.3.1-4.1)
- odbc-postgresql (Version: 1:09.03.0300-1)
and kept same unixodbc odbc.ini as above...
Description = PostgreSQL Unicode
Driver = /usr/lib/i386-linux-gnu/odbc/psqlodbcw.so
Database = apifdb_apimn_gforms
Servername = x.y.z.k
Username = user
Password = pass
Port = 35432
Protocol = 7.4-1
ServerType = Postgres
ReadOnly = No
ShowSystemTables= No
ShowOidColumn = No
FakeOidIndex = No
UseServerSidePrepare =1
ByteaAsLongVarBinary =1
UpdatableCursors=0
ConnSettings =
Optimizer =0
Ksqo =0
Debug =0
Fetch = 10000
UseDeclareFetch =1
Trace = 1
TraceFile = PostgreSQL_test_trace.log
Debug = 1
DebugFile = PostgreSQL_test_debug.log
Then copied the same php script and.... doesn't work.
As before, joins local to the model work, but it doesn't work when join in inter-model...
If you still think it's useful, I could try fedora, even if it's not my battleground, but imho, it's something else...
anyway, I would love to know your install/conig details in order to be able to replicate exactly your setup on fedora....
Marco
-
10. Re: query that works on jdbc does not work on linux->php->odbc->teiid...
rareddy Apr 27, 2016 9:21 AM (in response to m.ardito)unixODBC 2.3.4
Postgesql 09.05.0200
$cat ~/.odbc.ini
[loopy] #Driver=/usr/lib64/libodbcpsqlS.so Driver=/usr/lib64/psqlodbc.so Description=PostgreSQL Data Source Servername=127.0.0.1 Port=35432 Protocol=7.4-1 UserName=user Password=redhat1! Database=test ReadOnly=no ServerType=Postgres ConnSettings = UseServerSidePrepare=0 ByteaAsLongVarBinary=1 Optimizer=0 Ksqo=0 Debug=0 Fetch=10000 Trace=Yes Tracefile=sql.log Debug=1 Debug=debug.log # enable below when dealing large resultsets to enable cursoring(note prepared stmts will not work) UseDeclareFetch=0
As per the logging you can open up "org.teiid.ODBC" in TRACE mode in server's standalone-teiid.xml file.
As per PHP script, the implementations of PHP and/or driver could be different, and we already established that it was the driver as iSQL also exhibited the same symptom.
Ramesh.. -
11. Re: query that works on jdbc does not work on linux->php->odbc->teiid...
m.ardito Apr 27, 2016 9:42 AM (in response to rareddy)Found!!! it was
UseDeclareFetch=1
just setting UseDeclareFetch=0 as in your .ini makes both servers work... it seems, I'll test more and report here....
Thanks,
Marco
-
12. Re: query that works on jdbc does not work on linux->php->odbc->teiid...
shawkins May 3, 2016 6:22 AM (in response to m.ardito)Thanks Marco. Hopefully the issue was something that was already addressed with the recent changes, but if not we'll get it addressed in 9.0/8.13.x.