Connection String Special char

[Former Member]

Hello,

I have a problem with databasenames with special characters in the name:

(My code works when there is not a special char in the name)

For example, when my connectionstring would be:  jdbc:sybase:Tds:localhost:2638?ServiceName=BANDENM&W

I get a java.sql.SQLException: JZ00L: Login failed.  Examine the SQLWarnings chained to this exception for the reason(s). at line 3 while my pasw and login are correct.

Class.forName("com.sybase.jdbc3.jdbc.SybDriver");
  String connectionString = "jdbc:sybase:Tds:" + config.getHost() + ":" + config.getPort() + "?ServiceName=" + databaseName;
  Connection connection2  = DriverManager.getConnection(connectionString, config.getUser(), config.getPassword());

  //get VC client customer info
  statement = connection2.createStatement();

  resultSet2 = statement.executeQuery("SELECT CNR_SUPP,CNR_CUST,TNR_ID,NM_ID,CNR_VAT_ID,NM_FILTER_ID,CNR_ZIP_ID,CNR_VATCNTRY_ID,NM_TOWN_ID,TOP_COUNTRY.CNR_INTRASTAT FROM TOP_IDENTITY JOIN TOP_COUNTRY ON (TOP_IDENTITY.COD_CNTRY_ID = TOP_COUNTRY.COD_CNTRY)");

When I add double quotes around the databaseName, I get to line 8 but then I get an error:

com.sybase.jdbc3.jdbc.SybSQLException: ASA Error -141: Table 'TOP_IDENTITY' not found

But the table is again, definitely there ...

I've tried escaping it by replacing & with &

But no luck.

[chris_keating · Accepted solution]

I suspect that you need to fully qualify the table name i.e., <owner>.<table> as in DBA.MyTable because the user that is logged in is not the owner of the table.

[Former Member · Accepted solution]

Thanks for the reply,

I'll give it a try, but why would it work for all my other databases and only be needed for databases with special char in the name ? I'll let you know if it helped, next week.

[chris_keating · Accepted solution]

I quickly tested this in SQL Anywhere v16.0.0  with this code and it is working without any issues. The table t is owned by dba;

  String connectionString = "jdbc:sybase:Tds:localhost:2638?ServiceName=\"BANDENM&W\"";

  System.out.println( connectionString );

  Connection connection = DriverManager.getConnection(connectionString, "dba", "sql");

  Statement statement = connection.createStatement();

  ResultSet resultSet = statement.executeQuery("select * from t");

  while( resultSet.next() ) {

   System.out.println( resultSet.getInt(1) );

  }

However, if I create an user "u" and change the getConnection to

  Connection connection = DriverManager.getConnection(connectionString, "u", "sql");

The following is reported:

jdbc:sybase:Tds:localhost:2638?ServiceName="BANDENM&W"

Unexpected exception : com.sybase.jdbc3.jdbc.SybSQLException: SQL Anywhere Error -141: Tab

le 't' not found, sqlstate = 42W33

If I change the query to fully qualify the table (and assuming that the user "u" has permissions to query that table) as in:

ResultSet resultSet = statement.executeQuery("select * from dba.t");

the query completes without error.

I suspect that there are schema differences between the working and non-working database case and it is unlikely that the database name would result in the -141 error.

[Former Member · Accepted solution]

Hi Chris,

I changed it to:

ResultSet2 = statement.executeQuery("SELECT CNR_SUPP,CNR_CUST,TNR_ID,NM_ID,CNR_VAT_ID,NM_FILTER_ID,CNR_ZIP_ID,CNR_VATCNTRY_ID,NM_TOWN_ID,TOP_COUNTRY.CNR_INTRASTAT FROM DBA.TOP_IDENTITY JOIN DBA.TOP_COUNTRY ON (DBA.TOP_IDENTITY.COD_CNTRY_ID = DBA.TOP_COUNTRY.COD_CNTRY)");

But the error stays the same ...

[chris_keating · Accepted solution]

1) What version and build are you running for jConnect and SQL Anywhere?

2) Can you post the output from dbinfo for the database that you testing?

3) Can you run the following queries within the application that you are seeing this behavior and provide the results:

  Q1:

     select  user_name,  table_name

          from  systable join sysuser

     where

           table_name in ('TOP_IDENTITY','TOP_COUNTRY')

Q2:

     select current user from dummy;

[Former Member · Accepted solution]

1) What version and build are you running for jConnect and SQL Anywhere?

I’ve used jconn2 and jconn3, for the moment I’m using 3

SQL Anywhere = 9.0.2.3198 on my developer system

2) Can you post the output from dbinfo for the database that you testing?

I'm assuming you mean this:

Adaptive Server Anywhere Database Engine Version 9.0.2.3198

Copyright © 1989-2004 Sybase, Inc.

Portions Copyright © 2002-2004, iAnywhere Solutions, Inc.

All rights reserved. All unpublished rights reserved.

This software contains confidential and trade secret information of

iAnywhere Solutions, Inc.

Use, duplication or disclosure of the software and documentation

by the U.S. Government is subject to restrictions set forth in a license

agreement between the Government and iAnywhere Solutions, Inc. or

other written agreement specifying the Government's rights to use the

software and any applicable FAR provisions, for example, FAR 52.227-19.

iAnywhere Solutions, Inc., One Sybase Drive, Dublin, CA 94568, USA

4 logical processor(s) on 1 physical processor(s) detected.

Running on Windows NT 6.1 Build 7601 Service Pack 1

21688K of memory used for caching

Minimum cache size: 2048K, maximum cache size: 262144K

Using a maximum page size of 8192 bytes

Database server started at Wed Aug 27 2014 14:18

Trying to start SharedMemory link ...

    SharedMemory link started successfully

Trying to start NamedPipes link ...

    NamedPipes link started successfully

Trying to start TDS (TCPIP) link ...

Starting on port 2638

    TDS (TCPIP) link started successfully

Now accepting requests

Starting database "TOPADMINSA9" (W:\TOP\DATA\TOPADMINSA9.db) at Wed Aug 27 2014 14:18

Transaction log: TOPADMINSA9.log

Starting checkpoint of "TOPADMINSA9" (TOPADMINSA9.db) at Wed Aug 27 2014 14:18

Finished checkpoint of "TOPADMINSA9" (TOPADMINSA9.db) at Wed Aug 27 2014 14:18

Database "TOPADMINSA9" (TOPADMINSA9.db) started at Wed Aug 27 2014 14:18

Starting database "3051" (w:\TOP\DATA\3051\3051.DB) at Wed Aug 27 2014 14:18

Starting checkpoint of "3051" (3051.DB) at Wed Aug 27 2014 14:18

Finished checkpoint of "3051" (3051.DB) at Wed Aug 27 2014 14:18

Database "3051" (3051.DB) started at Wed Aug 27 2014 14:18

Starting database "BANDENM&W" (w:\TOP\DATA\BANDENM&W\BANDENM&W.DB) at Wed Aug 27 2014 14:19

Database "3051" (3051.DB) stopped at Wed Aug 27 2014 14:19

Starting checkpoint of "BANDENM&W" (BANDENM&W.DB) at Wed Aug 27 2014 14:19

Finished checkpoint of "BANDENM&W" (BANDENM&W.DB) at Wed Aug 27 2014 14:19

Database "BANDENM&W" (BANDENM&W.DB) started at Wed Aug 27 2014 14:19

Starting database "BRAL" (w:\TOP\DATA\BRAL\BRAL.DB) at Wed Aug 27 2014 14:19

Transaction log: w:\TOP\DATA\BRAL\BRAL.log

Starting checkpoint of "BRAL" (BRAL.DB) at Wed Aug 27 2014 14:19

Finished checkpoint of "BRAL" (BRAL.DB) at Wed Aug 27 2014 14:19

Database "BRAL" (BRAL.DB) started at Wed Aug 27 2014 14:19

Starting checkpoint of "BRAL" (BRAL.DB) at Wed Aug 27 2014 14:19

Finished checkpoint of "BRAL" (BRAL.DB) at Wed Aug 27 2014 14:19

Database "BRAL" (BRAL.DB) stopped at Wed Aug 27 2014 14:19

Starting database "HARKELIJN" (w:\TOP\DATA\HARKELIJN\HARKELIJN.DB) at Wed Aug 27 2014 14:19

Transaction log: w:\TOP\DATA\HARKELIJN\HARKELIJN.log

Starting checkpoint of "HARKELIJN" (HARKELIJN.DB) at Wed Aug 27 2014 14:19

Finished checkpoint of "HARKELIJN" (HARKELIJN.DB) at Wed Aug 27 2014 14:19

Database "HARKELIJN" (HARKELIJN.DB) started at Wed Aug 27 2014 14:19

Starting checkpoint of "HARKELIJN" (HARKELIJN.DB) at Wed Aug 27 2014 14:19

Finished checkpoint of "HARKELIJN" (HARKELIJN.DB) at Wed Aug 27 2014 14:19

Starting database "BURO56" (w:\TOP\DATA\BURO56\BURO56.DB) at Wed Aug 27 2014 14:19

Database "HARKELIJN" (HARKELIJN.DB) stopped at Wed Aug 27 2014 14:19

Transaction log: w:\TOP\DATA\BURO56\BURO56.log

Starting checkpoint of "BURO56" (BURO56.DB) at Wed Aug 27 2014 14:19

Finished checkpoint of "BURO56" (BURO56.DB) at Wed Aug 27 2014 14:19

Database "BURO56" (BURO56.DB) started at Wed Aug 27 2014 14:19

Starting checkpoint of "BURO56" (BURO56.DB) at Wed Aug 27 2014 14:19

Finished checkpoint of "BURO56" (BURO56.DB) at Wed Aug 27 2014 14:19

Database "BURO56" (BURO56.DB) stopped at Wed Aug 27 2014 14:19

Starting database "ENOZEN" (w:\TOP\DATA\ENOZEN\ENOZEN.DB) at Wed Aug 27 2014 14:19

Transaction log: w:\TOP\DATA\ENOZEN\ENOZEN.log

Starting checkpoint of "ENOZEN" (ENOZEN.DB) at Wed Aug 27 2014 14:19

Finished checkpoint of "ENOZEN" (ENOZEN.DB) at Wed Aug 27 2014 14:19

Database "ENOZEN" (ENOZEN.DB) started at Wed Aug 27 2014 14:19

Starting checkpoint of "ENOZEN" (ENOZEN.DB) at Wed Aug 27 2014 14:19

Finished checkpoint of "ENOZEN" (ENOZEN.DB) at Wed Aug 27 2014 14:19

Database "ENOZEN" (ENOZEN.DB) stopped at Wed Aug 27 2014 14:19

Starting database "ENOCONBVBA" (W:\TOP\DATA\ENOCONBVBA\ENOCONBVBA.DB) at Wed Aug 27 2014 14:19

Transaction log: W:\TOP\DATA\ENOCONBVBA\ENOCONBVBA.LOG

Starting checkpoint of "ENOCONBVBA" (ENOCONBVBA.DB) at Wed Aug 27 2014 14:19

Finished checkpoint of "ENOCONBVBA" (ENOCONBVBA.DB) at Wed Aug 27 2014 14:19

Database "ENOCONBVBA" (ENOCONBVBA.DB) started at Wed Aug 27 2014 14:19

Starting checkpoint of "ENOCONBVBA" (ENOCONBVBA.DB) at Wed Aug 27 2014 14:19

Finished checkpoint of "ENOCONBVBA" (ENOCONBVBA.DB) at Wed Aug 27 2014 14:19

Database "ENOCONBVBA" (ENOCONBVBA.DB) stopped at Wed Aug 27 2014 14:19

3) Can you run the following queries within the application that you are seeing this behavior and provide the results:

  Q1:

     select  user_name,  table_name

          from  systable join sysuser

     where

           table_name in ('TOP_IDENTITY','TOP_COUNTRY')

27-08-14 14:23:30 [ERROR] TechLogger -  -> com.sybase.jdbc3.jdbc.SybSQLException: ASA Error -141: Table 'sysuser' not found

Q2:

     select current user from dummy;

resultSet2.getString("current user")" = DBA

[chris_keating · Accepted solution]

I tested this in current versions of SA - v12 and v16. SQL Anywhere 9 is relatively dated and the EBF that you are running is also relatively old.

As for dbinfo, it is a command line utility

     dbinfo -c <connection_string>

There were changes in the system tables in later versions. The query should be written for v9 as:

     select  name,  table_name

          from  systable join sysusers

     where

           table_name in ('TOP_IDENTITY','TOP_COUNTRY')

I have tested in the same 9.0.2.3198 build and continue to see things working as expected.

Guesses:

1) You are running a case sensitive database and the table names are not matching the case for you query. That is the purpose of getting the output from the dbinfo.exe utility and the query. If the query returns no results, remove the where clause and look at the results to verify that some case variant of those names exist in the results.

2) The table is owned not by DBA but by another user/group.  I asked for the CURRENT USER to map that value to the results from the system table query above.

If you continue to have problems and can provide a simple repro for this, you can send it to me directly at first_name.last_name@sap.com. I will only be able to advise workarounds if this issue is the result of a product defect as v9 is no longer actively supported.

[Former Member · Accepted solution]

Hi Chris,

First of all, thank you for keep trying to help me, it's much appreciated

I've started one of the problem database manually and I got this with dbinfo:

C:\Users\PeterDV.VARTEC-ADM\Desktop>"C:\Program Files (x86)\Sybase\SQL Anywhere

9\win32\dbinfo.exe" -c "userid=DBA;password=stef;eng=BANDENM&W"

Adaptive Server Anywhere Information Utility Version 9.0.2.3198

Database  : W:\TOP\DATA\BANDENM&W\BANDENM&W.DB

Log file  : none

Log mirror: none

Compressed: No

Page size : 8192

Encrypted : No

Strings padded with blanks for comparisons: No

Respect letter-case when comparing: No ('A' equal to 'a')

Collation sequence: 1252LATIN1 (Code Page 1252, Windows Latin 1, Western)

Java classes installed: No

Database checksums enabled: No

I don't know if this is the right way to check it? Since in my java code the database is started from within another database ...

As for the other query, I now get a result: an empty resultset

[chris_keating · Accepted solution]

This behavior is limited to jConnect. SQL Anywhere JDBC driver does not appear to encounter this issue.

My initial quick test worked because 1) my test had only one database running on the server and 2) connections with the 'special' name were falling back to the default database for the server. The default database on a server is the first database that gets started.

Executing "select current database" confirmed that I was not connecting to the database named BANDENM&W but rather another database.

In jConnect, the & is used to delimit connection properties. From the jConnect documentation:

"If the value for one of the connection properties contains &, precede the  "&" ... with a backslash (\)."

So, the ServiceName=\"BANDENM&W\""; should be written as

       ServiceName=BANDENM\\&W

I have tested this in SA16 with jConnect 3 and it appears to be working.

The other option is to provide a name that is not using that character. You can use the -n option to name the database at startup.

[Former Member · Accepted solution]

Thank you so much Chris, the backslash did the trick.