12 Replies Latest reply on May 3, 2016 6:22 AM by Steven Hawkins

    query that works on jdbc does not work on linux->php->odbc->teiid...

    Marco Ardito Master

      Hi,

      I have a relatively simple query like

      "SELECT * FROM ag LEFT JOIN personale_interno ON ag.id_utente_inserimento = personale_interno.idanagrafica WHERE ag.id = 12"

       

      which works perfectly on jdbc using SquirrelSQL, and also with "odbc query tool" (ODBC Query Tool download | SourceForge.net)

       

      but using that query on a linux->php->odbc web server throws an error...

       

      Attached are logs both from teiid and the pg odbc webserver client...

      (teiid is 9beta1, while the setup is the same as reported here help needed troubleshooting slow odbc usage)

      as usual, more details available if needed,

       

      I can't guess what is happening now...

       

      Marco

        • 1. Re: query that works on jdbc does not work on linux->php->odbc->teiid...
          Ramesh Reddy Master

          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...
            Marco Ardito Master

            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...
              Ramesh Reddy Master

              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...
                Marco Ardito Master

                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...
                  Marco Ardito Master

                  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...
                    Ramesh Reddy Master

                    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...
                      Marco Ardito Master

                      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...
                        Marco Ardito Master

                        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...
                          Marco Ardito Master

                          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...
                            Ramesh Reddy Master

                            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...
                              Marco Ardito Master

                              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...
                                Steven Hawkins Master

                                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.