I'm trying to deploy a simple PowerBuilder app using a SQL Anywhere 11 database and the SQLA run-time engine. I have all of the SQLA DLLs deployed that are documented and I can start the database engine when I create an ODBC entry. However, I get a syntax error on a simple select.

---------------------------
System Options Error
---------------------------

SELECT "options"."optionid", "options"."optionname", "options"."optionvalue" FROM "options"

-----

Select Error: SQLSTATE = 37000

[Sybase][ODBC Driver][SQL Anywhere]Syntax error near 'optionid' on line 1
---------------------------
OK   
---------------------------

Of course, running on my development machine does not product this error. Any ideas what's going on? Am I missing a DLL? I've tried the syntax both with quotes and without quotes.

Deployed Files:

  • rteng11.exe
  • dbcon11.dll
  • dbctrs11.dll
  • dbicu11.dll
  • dbicudt11.dll
  • dblgen11.dll
  • dblib11.dll
  • dbserv11.dll
  • dbtool11.dll
  • rteng11.lic

asked 16 Feb '10, 19:05

Jason%20%27Bug%27%20Fenter's gravatar image

Jason 'Bug' ...
158669
accept rate: 50%

edited 04 May '10, 10:06

Volker%20Barth's gravatar image

Volker Barth
30.0k294448654

Comment Text Removed

There is nothing wrong with the syntax that I can see... and I did test it using dbisql. It is complaining about "optionid" and if it was a reserved word problem it would complain about "options"... and you've double-quoted it anyway. When you take off the double quotes, what message do you get? What produces the error message? the format is new to me, as is the SQLSTATE value it's displaying for that message text. You may be right looking for a configuration problem, but I am very doubtful that such a basic flaw would be present in any copy of the engine... could be wrong there, of course.

(17 Feb '10, 07:54) Breck Carter

Is the deployed *.db identical to the one that works OK on your development machine? Are there any views and/or triggers involved? Have you waved a dead chicken over the deployment machine?

(17 Feb '10, 08:06) Breck Carter
1

oops, I didn't scroll down to read the comments on Jose's answer. If you want, you can send me a deployment package to test. Could it be something to do with the pbodb ini thing?

(17 Feb '10, 10:29) Breck Carter

Okay, this is all my fault. I failed to deploy PowerBuilder's PBODB115.ini file. Once I put that in place, everything started working. Ugh. I'm sorry. I wish the error messages, though, were a little more helpful.

permanent link

answered 17 Feb '10, 15:48

Jason%20%27Bug%27%20Fenter's gravatar image

Jason 'Bug' ...
158669
accept rate: 50%

Don't be sorry... it may help future readers. The PBODB thing has a long history of messing with folk's minds.

(17 Feb '10, 16:51) Breck Carter

Jason,

OPTIONS is a reserved word, so when you use it in a SQL statement, it must be double-quoted. For example, issuing this statement:

SELECT * FROM OPTIONS;

will return the syntax error you saw. But if you use this one:

SELECT * FROM "options";

then you get the desired result set.

As an aside, I'd recommend that you do not use reserved keywords as identifiers. You can get a full list of these keywords here: http://dcx.sybase.com/1101en/dbreference_en11/alhakeywords.html.

José

permanent link

answered 16 Feb '10, 19:27

Jos%C3%A9%20Ramos's gravatar image

José Ramos
1.0k51524
accept rate: 30%

edited 16 Feb '10, 19:35

Comment Text Removed

I forgot to mention, I think you'll need the PowerScript escape sequence for double-quotes: ~"

(16 Feb '10, 19:32) José Ramos

I'll try changing the table and column names. However, you can see above that I did use double quotes. And this syntax came from a DataWindow, which should take care of escaping quotes as needed. I dunno... we'll see if changing the table name fixes it.

(16 Feb '10, 20:12) Jason 'Bug' ...


System Options Error

SELECT "appoptions"."appoptionid", "appoptions"."appoptionname", "appoptions"."appoptionvalue" FROM "appoptions"


Select Error: SQLSTATE = 37000

[Sybase][ODBC Driver][SQL Anywhere]Syntax error near 'appoptionid' on line 1

OK

(16 Feb '10, 20:21) Jason 'Bug' ...

copying the database back to my dev and running the exact same statement (quotes and all) through isql works without an error.

(16 Feb '10, 20:31) Jason 'Bug' ...
Your answer
toggle preview

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

Question tags:

×26
×4

question asked: 16 Feb '10, 19:05

question was seen: 2,297 times

last updated: 04 May '10, 10:06