JZ00L: Login failed. Examine the SQLWarnings chained to this exception for the reason(s).

[Former Member]

Hi,

I am trying to connect to a Sybase Database using java, using the jConn3 library. It exists on the class path.

Sample Code:

sybDriver = (SybDriver) Class.forName( "com.sybase.jdbc3.jdbc.SybDriver").newInstance(); con = DriverManager .getConnection("jdbc:sybase:Tds:<IP>:<PORT>?serviceName=C1603", "USER", "PWD");

Statement stmt = con.createStatement();

// Execute the query ResultSet rs = stmt.executeQuery("select * from dba.attempt where disposition=1100");

However, I end up receiving an error -

Exception in thread "main" java.sql.SQLException: JZ00L: Login failed. Examine the SQLWarnings chained to this exception for the reason(s). at com.sybase.jdbc3.jdbc.ErrorMessage.raiseError(Unknown Source) at com.sybase.jdbc3.tds.Tds.for(Unknown Source) at com.sybase.jdbc3.tds.Tds.case(Unknown Source) at com.sybase.jdbc3.tds.Tds.login(Unknown Source) at com.sybase.jdbc3.jdbc.SybConnection.a(Unknown Source) at com.sybase.jdbc3.jdbc.SybConnection.handleHAFailover(Unknown Source) at com.sybase.jdbc3.jdbc.SybConnection.<init>(Unknown Source) at com.sybase.jdbc3.jdbc.SybConnection.<init>(Unknown Source) at com.sybase.jdbc3.jdbc.SybDriver.connect(Unknown Source) at java.sql.DriverManager.getConnection(Unknown Source) at java.sql.DriverManager.getConnection(Unknown Source) at example.SybaseConnectivity.makeConnection(SybaseConnectivity.java:24) at example.SybaseConnectivity.main(SybaseConnectivity.java:50)

Is there a way to fix this?

Sybase Version being used : 11.0.1.2627.

The problem occurs when we are trying to obtain data out of Sybase using Java (jconn3 library which is in the classpath). The sybase data is embedded with a third party software called Wincati from Sawtooth Technologies.

How do we apply the EBF to the sybase version so to find the SQL warnings in the console of an IDE like eclipse?

Thanks & Regards,

-Rajarshi

[former_member186998]

Hi Rajarshi,

Which SQL Anywhere version do you use?
When you use version 11, we fix the following problem.

-------------------------------------------------------------------------------
If an application was using one of the JDBC based Remote Data Access classes
(i.e. either SAJDBC or ASEJDBC), and the remote login failed, then there
was a possibility that the error reported would not contain the actual reason
for the failed login. When jConnect throws a JZ00L SQLException, the actual
reason for the failed login is usually contained in a chained SQLException,
rather than the main SQLException. The server will now report the error on
the chained exception instead of reporting the topmost SQLException.
-------------------------------------------------------------------------------

We fix this problem EBF2139 and later.
You apply latest EBF and should confirm the actual reason for the failed login.

Thanks,
Atsushi

[reimer_pods]

If the database is part of that software package you mentioned, I'd encourage you to contact the vendor before making changes like installing an EBF. You might run into issues, if that EBF isn't fully supported.

Just my 2 cents ...

[regdomaratzki]

To anyone who found this issue based on a search for jConnect and "Login failed. Examine the SQLWarnings chained to this exception for the reason(s)" I thought I would share how to gather all the SQLWarnings on the exception, because the answer was not obvious to me. The use of "SQLWarnings" in the message led me (like Chris) to believe that calls to getWarnings() would be needed, but the connection failed, so your connection object is NULL. There are actually a number of chained SQLExceptions, and to gather them all and get the actual reason for your failed connection, your exception handling code for the connection attempt will need to look similar to :

try {
    ... try to connect ...
} catch( SQLException e ) {
    e.printStackTrace();
    while (e != null) {
        System.out.println("SQL Error : State=["+ e.getSQLState() +"] "+ e.getMessage());
        e = e.getNextException();
    }
} 

It is my hope that this post gives someone their afternoon back, because I lost mine.
Reg

[former_member182948]

I think that you will be able to find the cause of the error with -z and -o options.

-z server option is displays diagnostic communication messages, and other messages, for troubleshooting purposes.
http://dcx.sap.com/index.html#1101/en/dbadmin_en11/z-database-dbengine.html

-o server option is prints all database server messages to the database server message log file. http://dcx.sap.com/index.html#1101/en/dbadmin_en11/o-database-dbengine.html

[Former Member]

Hi Koichi,

Could you be more specific as to where the -z or -o options be added?

The error occurs, when I am trying to run a Java code which tries to access the sybase tables for data.

Warm Regards,

-Rajarshi.

[former_member182948]

You can add those options to the server option of the database server.
However that setting will depend on your environment.

example:
dbsrv11 -c 3m -n myserver "C:\...\demo.db" -z -o "C:\TEMP\server-log.txt"

The following KBA will probably help you out a little bit.
2215750 - How to control verbosity of SQL Anywhere server messages and their output file

[Former Member]

Hi Koichi,

I have to reconfirm the database server settings after I have a talk with the representative from Sawtooth Technologies. The sybase database is actually embedded around a third party software named Wincati from Sawtooth. I am simply trying to make a JDBC connection in Java to retrieve data from sybase. Also, the initial error message has changed to some extent and gives back the error "Table not found". Can you please look at the following description and error message for a better understanding -

The sybase database is wrapped around a third party software called Wincati developed by Sawtooth technologies( http://www.sawtooth.com/ ). This software has has a number of software/studies loaded and is primarily used in Survey Labs to interview and gather data.

Each study/survey has a backend Sybase Database attached to it.

The credentials, IP, port and database name are correct in the above example. C1603 is a sample study which has a sybase DB attached to it.

Also, I think i got past the connection issue, but with a different error this time.

When the sample code from above is run I get the below error -

Exception in thread "main" com.sybase.jdbc3.jdbc.SybSQLException: SQL Anywhere Error -141: Table 'attempt' not found at com.sybase.jdbc3.tds.Tds.a(Unknown Source) at com.sybase.jdbc3.tds.Tds.nextResult(Unknown Source) at com.sybase.jdbc3.jdbc.ResultGetter.nextResult(Unknown Source) at com.sybase.jdbc3.jdbc.SybStatement.nextResult(Unknown Source) at com.sybase.jdbc3.jdbc.SybStatement.nextResult(Unknown Source) at com.sybase.jdbc3.jdbc.SybStatement.queryLoop(Unknown Source) at com.sybase.jdbc3.jdbc.SybStatement.executeQuery(Unknown Source) at com.sybase.jdbc3.jdbc.SybStatement.executeQuery(Unknown Source) at example.SybaseConnectivity.makeConnection(SybaseConnectivity.java:30) at example.SybaseConnectivity.main(SybaseConnectivity.java:50)

Now, I know for a fact that the table does exist.

Furthermore, when the study (C1603) is open inside Wincati Software, the code runs just fine with the correct results. However, if the study is not open inside Wincati Software, the above exception is thrown.

Do you see any reason regarding this weird connection behavior? Could be a bug? Please let me know, if you need further information.

Warm Regards,

-Rajarshi

[former_member182948]

I think that the connection issue was solved.
And I believe that you have encountered new issue from the error messages you showed.
"SQL Anywhere Error -141: Table 'attempt' not found"

Probably you would have enabled the next code.
// Execute the query ResultSet rs = stmt.executeQuery("select * from dba.attempt where disposition=1100");

If you can not access that "attempt" table, there is a possibility that the owner of the table is a different user.

In other words, the "dba" may not be the owner of the table.
Or you are using a user who can not be granted dba privilege.

[VolkerBarth]

To exclude problems on the Java side, what happens when you connect via DBISQL (or the older dbisqlc tool) to that database and run the same query - does that work?

And does a more general query against the system catalog (such as "select * from sys.systab") work from within your application code?

[VolkerBarth]

Or you are using a user who can not be granted dba privilege.

Well, that shown SELECT statement would not require dba privilege at all, it would only need SELECT privilege on that particular table...

[Former Member]

Hi Volker,

- Keeping Java aside for the moment, I use Interactive SQL/DBISQL to run the query (select * from dba.attempt where disposition=1100) and it works and I get back the desired resultset.

- However, when the same query is being run in Java (same credentials, IP, port, correct filepath) it fails, returning "table not found" error.

- The query "select * from sys.systab" returns a "permission denied" error both in DBISQL as well as Java. This is understandable since, the third party software software company Sawtooth provides us with a username and password which has read permissions on their select data tables. Even when using Java, we are merely trying to read the necessary data in the tables and storing them in a resultset.

We have been using the third party software Wincati for many years, currently we are trying to upgrade from version 4 to 6. The credentials for reading the data tables in sybase have not changed with the versions.

It baffles me, since a query which runs fine in DBISQL fails in Java. I have been using Jconn2 library in java for the connection which is in the classpath. I have tried with the Jconn3 library as well but the issue still persists.

Warm Regards,

-Rajarshi

[Former Member]

Hi Koichi & Volker,

Does it concern the ownership of the table since the query executes fine in DBISQL but fails when executed in Java?

Interestingly, when the connection is made via DBISQL using the correct credentials, DB filePath, server IP, port - the query successfully gets executed in Java as well with the correct results.

However, if the connection is not opened in DBISQL first, the query fails to get executed in Java and returns the table not found error.

Maybe this has to do with some connection settings in Java?

Warm Regards,

-Rajarshi

[VolkerBarth]

OK, then the vendor seems to have not given you access to the system catalog - by default any user is part of the public group and that is allowed to read the catalog.

So what about a query that does not require the system catalog, such as

select property('MachineName'), property('Name'), db_property('File'), db_property('Name')

This should return some information about the server engine (machine name and db server name) and current database (file location and db name).

Or a simply "select @@version"?

----

Otherwise, I have no clue. You might also ask on the separate SQL Anywhere Forum (which get much more attention by SQL Anywhere experts anyway...)

[VolkerBarth]

Does it concern the ownership of the table since the query executes fine in DBISQL but fails when executed in Java?

It should not. Note however that the database may have a "LoginProcedure" that automatically sets options or allows/disallows certain features based on the calling client application and/or user, so that (or different facilities) could lead to a different behaviour when the same user connects via a different application...

[Former Member]

Hi Volker,

It is possible that we do not have access to the system catalog, however, I was able to query in DBISQL the ones you asked for and the queries returned the right resultset -

select property('MachineName'), property('Name'), db_property('File'), db_property('Name')

returns - the server name, server alias, the file path to the database on the server, and the database name correctly.

select @@version returns - 11.0.1.2627

I will try to post the issue on the Forum as you suggested, the problem of "Error 141 - Table not found" only arises when trying to run a sybase query in java , when the connection in DBISQL/Interactive SQL has not been made, otherwise the query works fine in Java, which kind of makes me think, it could be due to a specific JDBC library driver or some kind of settings issue..

Let me know if you need any other information and thanks for all your help!

Warm regards,

-Rajarshi.

[Former Member]

Hi Volker,

I am sure, something similar has been going on since the query which gets executed in DBISQL returns a
"table not found" error in Java unless the DBISQL has a connection open to the particular database.

However, we have been using a previous version of the third party software (version 4) for many years successfully and we are planning to upgrade the software (version 6). The credentials for reading data from the tables has not changed with the version.

Do you suspect any feature might have changed in between the two versions which is causing the connection failure?

(I am simply trying to narrow down the problem as in if the issue lies with the vendor software or java )

Warm Regards,

-Rajarshi.

[chris_keating]

The warnings are chained to the object that threw the exception and accessed with the getWarning() method. My guess however is that the connection was not successfully established and has returned a NULL. Can you check that to be the case and, if so, are you sure that the credentials, IP,port and database name are correct?

[Former Member]

Hi Chris,

Thanks for your response!

About the error, I will explain in more details if it helps. The sybase database is wrapped around a third party software called Wincati developed by Sawtooth technologies( http://www.sawtooth.com/ ). This software has has a number of software/studies loaded and is primarily used in Survey Labs to interview and gather data.

Each study/survey has a backend Sybase Database attached to it.

The credentials, IP, port and database name are correct in the above example. C1603 is a sample study which has a sybase DB attached to it.

Also, I think i got past the connection issue, but with a different error this time.

When the sample code from above is run I get the below error -

Exception in thread "main" com.sybase.jdbc3.jdbc.SybSQLException: SQL Anywhere Error -141: Table 'attempt' not found at com.sybase.jdbc3.tds.Tds.a(Unknown Source) at com.sybase.jdbc3.tds.Tds.nextResult(Unknown Source) at com.sybase.jdbc3.jdbc.ResultGetter.nextResult(Unknown Source) at com.sybase.jdbc3.jdbc.SybStatement.nextResult(Unknown Source) at com.sybase.jdbc3.jdbc.SybStatement.nextResult(Unknown Source) at com.sybase.jdbc3.jdbc.SybStatement.queryLoop(Unknown Source) at com.sybase.jdbc3.jdbc.SybStatement.executeQuery(Unknown Source) at com.sybase.jdbc3.jdbc.SybStatement.executeQuery(Unknown Source) at example.SybaseConnectivity.makeConnection(SybaseConnectivity.java:30) at example.SybaseConnectivity.main(SybaseConnectivity.java:50)

Now, I know for a fact that the table does exist.

Furthermore, when the study (C1603) is open inside Wincati Software, the code runs just fine with the correct results. However, if the study is not open inside Wincati Software, the above exception is thrown.

Do you see any reason regarding this weird connection behavior? Please let me know, if you need further information.

Warm Regards,

-Rajarshi.