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

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

odbc right-truncation-of-strin

asked 05 Sep '11, 16:37

Jun
21●2●2●4
accept rate: 0%

flat view

One Answer:

active answers oldest answers newest answers popular answers

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.

permanent link

answered 05 Sep '11, 17:06

Volker Barth
40.2k●361●550●822
accept rate: 34%

edited 05 Sep '11, 17:16

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:

When the DelimitIdentifier='Yes' in the SQLCA.DBParm of the database connection string, it executes correctly.
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

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

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

By RSS:

Answers

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

learn more about Markdown

Question tags:

odbc ×145
right-truncation-of-strin ×8

question asked: 05 Sep '11, 16:37

question was seen: 3,934 times

last updated: 06 Sep '11, 11:52

SAP SQL Anywhere

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

Follow this question

Related questions