ODBC function {fn right(,)} and {fn left(,)} not supported?

[Former Member]

I do not want to use the SQL Anywhere build-in fuction as follow:

select right(StringColumn, 5) from ATable;

Instead, I want to use the standard ODBC fuction as:

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

This way, I can run my application on different ODBC compliant DBMS without changing too much of my codes.

However, SQL Anywhere does not like the ODBC fuction {fn right(,)}. Any idea why, or how to resolve this problem?

Thanks,

Jun Gan

E-Mail: ganjun@gmail.com

[VolkerBarth · Accepted solution]

How are you testing these queries?

When using the ODBC function syntax with a homebrewn ODBC application program against a 12.0.1.3389 SQL Anywhere database, {fn LEFT()} does work.

Note that DBISQL is a JDBC client and dbisqlc is an Embedded SQL client, so these common utilities will not accept ODBC escape syntax AFAIK.

---

EDIT: However, DBSIQL does support JDBC escape syntax as shown in the docs. - You simply have to double the braces in DBISQL, i.e. "{{fn LEFT(column, 10)}}" will work. I'm not sure however whether JDBC and ODBC escape syntax are fully compatible.

[Former Member · Accepted solution]

Thanks for the quick response! 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"

However, 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

I don't really like setting DelimitIdentifier='Yes' because it puts double quotes around all the table and column names when I create datawindows in PowerBuilder.