SQL Anywhere Error -188: Not enough values for host variables

[Former Member]

Hi there

Running on SA 16.0.0.1915 using jConn4:

The Error caused by this simple thing (code is coldfusion, #...# will be replaced by it before SA sees it):

begin
   declare @now timestamp;
   set @now = current timestamp;
   update USERS
      set SESSION_KEY = hash (string(USER_GUID, '#cgi.REMOTE_ADDR#', @now), 'SHA1'),
          SESSION_KEY_IP = '#cgi.REMOTE_ADDR#',
          SESSION_KEY_ISSUED = @now,
          SESSION_KEY_TOUCHED = null
    where IS_SYSTEM_ = 0
      and USER_HASH = :uh ;

   select SESSION_KEY 
     from dba.USERS as u

       -- for next line to work it must not contain :uh again although it should!
       -- embedding the value literally does work but is not intended of course
    where USER_HASH = '#arguments.User_Hash#'  
      for read only;
end;


Note that this works where I have only ONE parameter with the same name. Set the commented WHERE clause to the same expression as above i.e. using the same parameter again, SA chokes.
Very annoying because embedding parameters literally bypasses parameter checks for all the web-related cheating like sql injection etc. etc. luckily again, having it at least for one parameter working, still prevents the script.
Hints to fix that are very welcome.
Tinu

[Former Member]

BTW: I have other scripts where I use the same sql parameters more than one time. SA also chokes on those. I cannot tell what part is to blame SA, jConn, Java, CF. CF's diagnostic data show that Java presents 2x Java.In values (of course, if I use :uh twice as intended).

[jack_schueler · Accepted solution]

Read this doc page: http://dcx.sybase.com/index.html#sa160/en/dbusage/ptib.html*d5e5480

Host variable references are permitted within batches with the following restrictions:

• only one statement in the batch can refer to host variables

• the statement which uses host variables cannot be preceded by a statement which returns a result set

[Former Member · Accepted solution]

There are massive behavior changes in this area from SA12x to SA16 ... I have productive applications out there which do lots of batch stuff in SA12 ...

I have to rewrite almost all of them to work in SA16 ... and this stuff is not mentioned in chapters "Behavior changes in SA16" in the docs AFAIK.

[jack_schueler · Accepted solution]

According to SA12 documentation, there is no change with regard to host variable restrictions. See http://dcx.sybase.com/index.html#1201/en/dbusage/ptib.html.

[former_SQLA_member1694886]

Is the statement wrapped between cfquery tags?

If yes, use

<cfqueryparam value="#arguments.User_Hash#" cfsqltype="cf_sql_varchar">


and not embedded sql host variables like USER_HASH = :uh

[Former Member]

your comment does not apply here. the whole statement is

q = new query(); the_sql = "..... all the above ...."; q.setSQL (the_sql); q.addParam (name="uh", value=arguments.User_Hash, cfsqltype="CF_SQL_VARCHAR"); res = q.execute().getResult().SESSION_KEY;

i have done such things a million times .. I assume a simple programming bug: some intermediate piece of software (SA, jConn) calculates: find parameter names and occurrences in sql, compare count with count of input parameters, if not equal ... and voila, it breaks

[former_SQLA_member1694886]

@tinu8805 So you're using cfscript. Did you try dumping the generated SQL and running it in ISQL?

[former_SQLA_member1694886]

@tinu8805 So you're using cfscript... Did you do a dump of the generated SQL and run it in ISQL? Also, I'm not sure you can run a compound statement of the sort in CF. You could break it down in 2 queries between transaction functions. Or use a stored proc.