search

edwig / ODBCQueryTool

Open ODBC Querytool for SQL RDBMS environments
Other
16 stars 5 forks source link

System i Access ODBC-Treiber][DB2 für i5/OS]SQL0104 - Token . ungültig. #7

Open ALPBRCH opened 1 year ago

ALPBRCH commented 1 year ago

Hello, unfortunately, the SQL query to IBM DB2 fails:

Error in SQL statement: ODBC-call returned [-1] : [42000][-104][IBM][System i Access ODBC-Treiber][DB2 für i5/OS]SQL0104 - Token . ungültig. Gültige Token: FOR USE SKIP WAIT WITH FETCH LIMIT ORDER UNION EXCEPT OFFSET.

SELECT * FROM TEST.TABLE

ODBC Info: Screenshot 2023-05-02 095043

Isn't DB2 supported?

regards J.Immel

edwig commented 1 year ago

Hello J.Immel,

Wel certainly it is.

As you can see in the info tree the ODBC driver is recognized and working.

The nasty snare with the ODBC “42000” state is that the ‘official description’ from the standard states

42000: Syntax error or access violation

And as I can see the SQL0104: invalid token error (German Gültige Token) I suspect that the problem

Here does not lie in the syntax of your select statement (SELECT * FROM

)

But that you face some access problem with the logged in user not being able to see the “table” in the “test” schema.

Furthermore as we google for the “FOR USE SKIP WAIT… etcetera” you can find that all sorts of problems where the select cannot follow up naturally end with this error text in DB2.

Personally I would suggest investigating in the direction of a privileges problem,

Best regards,

Edwig Huisman

From: ALPBRCH @.> Sent: dinsdag 2 mei 2023 09:54 To: edwig/ODBCQueryTool @.> Cc: Subscribed @.***> Subject: [edwig/ODBCQueryTool] System i Access ODBC-Treiber][DB2 für i5/OS]SQL0104 - Token . ungültig. (Issue #7)

Hello, unfortunately, the SQL query to IBM DB2 fails:

Error in SQL statement: ODBC-call returned [-1] : [42000][-104][IBM][System i Access ODBC-Treiber][DB2 für i5/OS]SQL0104 - Token . ungültig. Gültige Token: FOR USE SKIP WAIT WITH FETCH LIMIT ORDER UNION EXCEPT OFFSET.

SELECT * FROM TEST.TABLE

ODBC Info: https://user-images.githubusercontent.com/113665911/235609832-94ae3321-75cd-4abc-9e1f-30366da27779.jpg

Isn't DB2 supported?

regards J.Immel

— Reply to this email directly, view it on GitHub https://github.com/edwig/ODBCQueryTool/issues/7 , or unsubscribe https://github.com/notifications/unsubscribe-auth/ABFDS33WGM7I36WUHD7EYKLXEC4RVANCNFSM6AAAAAAXSVY55U . You are receiving this because you are subscribed to this thread. https://github.com/notifications/beacon/ABFDS37KKZT5ORZKNBZUKQLXEC4RVA5CNFSM6AAAAAAXSVY55WWGG33NNVSW45C7OR4XAZNFJFZXG5LFVJRW63LNMVXHIX3JMTHGJWI2LA.gif Message ID: @. @.> >

ALPBRCH commented 1 year ago

Hello Edwig, thank a lot for the fast response. It's not because of the permissions - the user has admin rights to the database and tables. It works with other SQL query tools. The last version of ODBC Query Tool that worked with DB2 was 1.04xx if I remember correctly :).
It may also be due to the IBM Access ODBC driver. Thanks for your help. Best regards Jürgen Immel

edwig commented 1 year ago

Hello Jürgen,

To verify I installed the community version 11.5.8 of DB2. During installment I selected to NOT use the OS integrated security but local database security. Alsoo I opted to install the standard "SAMPLE" database and the DB2 ODBC drivers.

Firing up Sthe ODBCQuerytool showed all tables in the table tree when using the standard "db2admin" account. But no SELECT could be run. This resulted in a strange error, namely:

[42501][-551][IBM][CLI Driver][DB2/NT64] SQL0551N The statement failed because the authorization ID does not have the required authorization or privilege to perform the operation. Authorization ID: "DB2ADMIN". Operation: "SELECT". Object: "CUSTOMER". SQLSTATE=42501

So I retried using my MS-Windows account and Ho Presto! Lo and behold! Everything worked fine! SELECT, UPDATE, everything.

The standard installation of the latstest version and ODBC drivers led to a working situation with the current ODBC Querytool. I am afraid that in your situation a different approach may be needed to find a working combination, but I think it is hereby proven that the standard software (DB2, ODBC-Driver, ODBC Querytool) is not to blame.

But do check out this post: https://www.sqlservercentral.com/forums/topic/as400-as-linked-server The error message that you reported can be found on the internet, with a root cause that people are trying to connect to, and use, a linked server.

For now, I will not further investigate in this matter, unless you can give me some more clues. Sorry.

Best regards, Edwig Huisman.

  • © Githubissues.
  • Githubissues is a development platform for aggregating issues.