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?


Jun Gan

E-Mail: ganjun@gmail.com

asked 05 Sep '11, 16:37

Jun's gravatar image

accept rate: 0%

How are you testing these queries?

When using the ODBC function syntax with a homebrewn ODBC application program against a 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.

permanent link

answered 05 Sep '11, 17:06

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

edited 05 Sep '11, 17:16

Thanks for the quick response! I am using SQL Anywhere Network Server Version 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.

(06 Sep '11, 11:32) Jun
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 05 Sep '11, 16:37

question was seen: 1,796 times

last updated: 06 Sep '11, 11:52