Syntax error at ";" that does not exist

Post Reply
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Syntax error at ";" that does not exist

Post by barry.marcus »

In our database we have a certain set of "base" tables, each of which has an associated "log" table. For instance, there are the tables project and logproject, filter and logfilter, etc. We also have a simple utility that creates backup copies of the base tables. Here is a simplified version of the code:

<a name=backupDatabase PUBLIC source timestamp>
<$BACKUP_PREFIX="BU">
<apicp allinear 1>
<sql db=$source ROW "select NAME from SYSTABLES where NAME like 'log*'">
<substr $NAME 3 -1>
<$baseTable=$ret>
<strfmt "create table %s%s%s as select * from %s" $BACKUP_PREFIX $timestamp $baseTable $baseTable>
<sql db=$source $ret></sql>
</sql>
</a>

The select query retrieves the name of each log table, and the base table is extracted from that. Then a create table statement is built, executed and voila! This works fine when called with code such as:

<$theDb="D:\TheDBPath">
<sql "select counter whenBackedUp"></sql>
<backupDatabase source=$theDb timestamp=$whenBackedUp>

The problem is when I submit a URL to run the same utility on a remote server. In the same vortex script file is the following function:

<a name=launchBackupDatabase PUBLIC>
<backupDatabase source=$theDb timestamp=$whenBackedUp>
</a>

When the code issues a URL such as:

http://our.remote.server/texiscgi/texis ... edUp=12345

we get the following errors in the vortex log (one for each create table statement that it attempted to run):

015 [synchronizer]:575: Line 1: syntax error at ";"
000 [synchronizer]:575: SQLPrepare() failed with -1 in the function prepntexis

Line 575 is the line that reads "<sql db=$source $ret></sql>" in the function backupDatabase. I don't get why it's saying there is a syntax error at ";" since there is no semicolon in the create table statement that the function produces. I'm logging the sql statements in a separate text file on the remote server, and this is what's in that log (one line like this for each create table statement):

Thu Jul 28 19:59:48 Eastern Daylight Time 2011 [create table BU12345project as select * from project]

All of the statements are syntactically correct. Perhaps I'm missing something obvious. Thanks for your help.
User avatar
John
Site Admin
Posts: 2597
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

Syntax error at ";" that does not exist

Post by John »

The semicolon is added automatically at the end of the statement, and generally indicates incomplete SQL.

Does the SQL statement you logged work in tsql?


You could also enable sql tracing to verify the SQL statement being passed in is what you expect.
John Turnbull
Thunderstone Software
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Syntax error at ";" that does not exist

Post by barry.marcus »

The sql statement I log does indeed work exactly right at the TSQL prompt.

I'll enable sql tracing and let you know what I find out.
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

Syntax error at ";" that does not exist

Post by barry.marcus »

I put in some sqlcp tracesql statements this morning and lo and behold... It's working fine now! Full disclosure: There was a server restart overnight which apparently "fixed" whatever was gumming up the works! This turns out to be one of those anomalous things that will forever be unexplained.

MICROSOFT!
Post Reply