Prepared statement with "partition by" doesn't work against Sybase IQ?

Question

I'm seeing a problem when querying Sybase IQ with a prepared statement. The query works fine when I type the entire query as text and then call PrepareStatement on it with no parameters. But when I stick in one parameter, then I get back errors, even though my sql is correct. Any idea why?

This code works perfectly fine and runs my query:

errorquery<<"SELECT   1   as foobar \
  ,       (SUM(1) over (partition by foobar) )      as myColumn  \
  FROM spgxCube.LPCache lpcache   \
                WHERE    lpcache.CIG_OrigYear = 2001    "; 


odbc::Connection* connQuery= SpgxDBConnectionPool::getInstance().getConnection("MyServer");
PreparedStatementPtr pPrepStatement(connQuery->prepareStatement(errorquery.str()));
    pPrepStatement->executeQuery();

But this is the exact same thing except instead of typing "2001" directly in the code, I insert it with a parameter:

    errorquery<<"SELECT   1   as foobar \
  ,       (SUM(1) over (partition by foobar) )      as myColumn  \
  FROM spgxCube.LPCache lpcache   \
                WHERE    lpcache.CIG_OrigYear = ?    "; 

 odbc::Connection* connQuery = SpgxDBConnectionPool::getInstance().getConnection("MyServer");
 PreparedStatementPtr pPrepStatement(connQuery->prepareStatement(errorquery.str()));

 int intVal = 2001;
 pPrepStatement->setInt(1, intVal);

 pPrepStatement->executeQuery();

That yields this error: [Sybase][ODBC Driver][Adaptive Server Anywhere]Invalid expression near '(SUM(1) over(partition by foobar)) as myColumn'

Any idea why the first one works if the second one fails? Are you not allowed to use "partition by" with inserted sql parameters or something like that?

Answer 1

I know very little about Adaptive Server Anywhere, but you're using the Adaptive Server Anywhere driver to query Sybase IQ.

Is that really what you want?

Answer 2

The Sybase (ASA) Adaptive Server Anywhere error is fine, there is a Sybase ASA instance included in the IQ DB, used for the SYSTEM space.

I do not know if partition by is supported / fully supported in versions prior to Sybase IQ v12.7. I recall having problems with it under v12.6. Under v12.7 or better it should be fine and otherwise you command looks good to me.