Error when using ODBC function {fn right(,)} and {fn left(,)}

[Former Member]

I am using SQL Anywhere Network Server Version 11.0.1.2044 with Interactive SQL Version 11.0.1, build 2044. When I execute the following statement in this ISQL,

select {fn right(StringColumn, 5)} from ATable;

I got the following Error:

Could not execute statement. Syntax error near 'fn right(StringColumn, 5)' on line 1 SQLCODE=-131, ODBC 3 State="42000"

When I execute the same statement in the ISQL Session of the Database Painter of PowerBuilder Version 12.1 Build 6875, I got two different results: 1.When the DelimitIdentifier='Yes' in the SQLCA.DBParm of the database connection string, it executes correctly. 2.When the DelimitIdentifier='No' in the SQLCA.DBParm of the database connection string, it gives me the following error message:

SQLSTATE = 37000 [Sybase][ODBC Driver][SQL Anywhere]Syntx error near 'from' on line 1

Should it work without setting DelimitIdentifier='Yes' for the database connection in PowerBuilder? Is it a bug for PB or SQL Anywhere?

[VolkerBarth]

Given the facts

1. that your first question obviously has not given you the desired answer, and
2. that SQL Anywhere obviously does support the ODBC/JDBC escape sequences when using the according interfaces,

I would think the problem is specific to PowerBuilder and would suggest to ask this in a Powerbuilder forum.

[Former Member]

I don't think it's a PowerBuilder problem because there is no problem when I connect PowerBuilder to Oracle via the ODBC driver for Oracle. I think the problem is in the ODBC and JDBC driver of SQL Anywhere.

[VolkerBarth]

As stated in my answer on the other question, when using an ordinary ODBC application, the SQL Anywhere ODBC driver definitely does support the ODBC escape syntax.

I haven't tested with a JDBC application. Note that DBSIQL (which uses JDBC) does support the JDBC escape syntax, too, but requires the doubled braces as documented (for its own parsing purposes, I would think).

Therefore I would conclude it's a problem "between PowerBuilder and the SQL ODBC driver"...

[Former Member]

Please do an actual test using PowerBuilder with ODBC connection to SQL Anywhere. Set the DelimitIdentifier to 'No' in the SQLCA.DBParm. You will see the following 3 statements do not work:

select {fn CHAR(40)} from ATable;

select {fn LEFT(StringColumn,5)} from ATable;

select {fn RIGHT(StringColumn,5)} from ATable;

while all other ODBC string functions work just fine, such as:

select {fn RTRIM(StringColumn)} from ATable;

select {fn SUBSTRING(StringColumn,1,1)} from ATable;

I think this inconsistency is a bug in the ODBC driver for SQL Anywhere. The ODBC driver for Oracle does not cause any problem with PowerBuilder.

By the way, how to report a bug for Sybase to fix?

[VolkerBarth]

FWIW, I do not use PowerBuilder at all, so I can't test this. However, I have tested with an ordinary ODBC application, and have used ODBC escapes with SQL Anywhere in production for years.

But I'm sure several users of this forum are PB-savvy. They just haven't answered yet...

[jeff_albion]

>> By the way, how to report a bug for Sybase to fix?

If you have a technical support plan, it is suggested that you log a case via that service, as this ensures priority service and resolution times for your issue. A technical support representative will work with you for your specific development environment (e.g. PowerBuilder technical support).

If this is a non-priority issue that you simply wish to have examined and do not care about the resolution times for, or notification about when the EBF patch is released for, you can also log a free bug submission case.

You should make it clear when logging your case that your problem is related to a 'PowerBuilder' application.

[jack_schueler]

Regarding select {{fn POSITION(StringColumn in 'abc')}} from ATable;

The ODBC driver escape syntax parser is looking for uppercase keywords like IN or FROM in scalar function syntax involving functions POSITION and EXTRACT. The string comparison should be done in a case-insensitive manner. This problem will be corrected.

[jack_schueler]

The problem with functions like RIGHT and the Powerbuilder DelimitIdentifier='No' option is that the ODBC driver places quotation marks around functions that are keywords when it rewrites the escape syntax.

So this ...

select {{fn right(DepartmentName,4)}} from Departments;

is rewritten as this ...

select "RIGHT"(DepartmentName,4) from Departments;

The PB DelimitIdentifier='No' option results in the server option "quoted_identifier" being set "off".

Of course, when it is "off", then "RIGHT" is interpreted as the literal value 'RIGHT' and this leads to the syntax error. It's as if you had tried to execute this ...

    select 'RIGHT'(DepartmentName,4) from Departments

[VolkerBarth]

Just to understand: So this is considered desired behaviour?

I would think that "{fn right(...)}" would clearly indicate that a builtin function is called (instead of an arbitrary identifier which might have to be quoted).

[jack_schueler]

I think what you are asking is "is it necessary to use the quotation marks?" For example create table foo (col1 char(65) default {{fn char(65)}}) becomes create table foo (col1 char(65) default "CHAR"(65)) but does there exist a case where something like create table foo (col1 char(65) default CHAR(65)) could result in a syntax error because CHAR (or whatever) is interpreted as a keyword rather than a function. I need a SQL syntax expert to answer this.

[VolkerBarth]

Yes, I guess this is what I'm asking...

As usual, I have thought less carefully: I was just thinking of the "SELECT {fn ..." syntax where one would not expect DDL statements. But given your sample it's more complicated, I agree.

[jack_schueler]

The problem has been corrected by using square brackets instead of quotation marks.

select [ RIGHT ] (DepartmentName,4) from Departments;

[Former Member]

I don't believe iSQL is using ODBC to access the database engine. I'm not certain that ODBC escapes would be handled properly, being as they get "fixed up" by the driver before being sent to the database.

Possibly related google searches: SQL_ATTR_NOSCAN, SQLNativeSql

Not sure I can comment about DelimitIdentifier

[VolkerBarth]

AFAIK, DBISQL uses JDBC and requires doubled braces around the escape sequences whereas dbisqlc is an ESQL application and does not support ODBC escapes.

[Former Member]

Doubled braces does work in DBISQL, though it removed the first non-space character so you have to do like:

*select {{fn right(StringColumn, 5)}} from ATable;

instead of just:

select {{fn right(StringColumn, 5)}} from ATable;

That's a bug, probably in the SQL Anywhere's JDBC driver.

When I test the problem in PowerBuilder with ODBC connection, the following statements cause errors when the DelimitIdentifier is set to 'No' in the SQLCA.DBParm of the database connection string:

select {fn CHAR(40)} from ATable;

select {fn LEFT(StringColumn,5)} from ATable;

select {fn RIGHT(StringColumn,5)} from ATable;

while all other ODBC string functions work just fine, such as:

select {fn RTRIM(StringColumn)} from ATable;

select {fn SUBSTRING(StringColumn,1,1)} from ATable;

select {fn UCASE(StringColumn)} from ATable;

This inconsistency might be a bug in the ODBC driver for SQL Anywhere.

Here is a common problem for both JDBC and ODBC driver of SQL Anywhere:

The following statement works

select {{fn POSITION(StringColumn IN 'abc')}} from ATable;

while this does not work

select {{fn POSITION(StringColumn in 'abc')}} from ATable;

The only difference: "IN" is upper case while "in" is lower case. Should case matter?

[VolkerBarth]

Given that I would think that those function names that are reserved words in SQL Anywhere are problematic.

Note that I'm in the position to draw any conclusions from that.

[jeff_albion]

No, the case should not matter, but in this specific case it did: http://search.sybase.com/kbx/changerequests?bug_id=684608. This has been fixed in 11.0.1.2678.

[jack_schueler]

Re: Doubled braces does work in DBISQL, though it removed the first non-space character so you have to do like:

*select {{fn right(StringColumn, 5)}} from ATable;

There was a problem like this back in 2009 but it was corrected. I suspect your software may be out-of-date.