带有“partition by”的准备好的语句不适用于 Sybase IQ？

发布于 2024-07-26 09:56:17 字数 1416 浏览 11 评论 0原文

我在使用准备好的语句查询 Sybase IQ 时遇到问题。当我以文本形式输入整个查询，然后在不带参数的情况下对其调用PrepareStatement 时，查询工作正常。但是当我输入一个参数时，即使我的 sql 是正确的，我也会收到错误。知道为什么吗？

这段代码运行得很好，并且运行我的查询：

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();

但这是完全相同的事情，除了我不是直接在代码中键入“2001”，而是使用参数插入它：

    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();

这会产生以下错误： [Sybase][ODBC 驱动程序][Adaptive Server Anywhere]'(SUM(1) over(partition by foobar)) as myColumn' 附近的表达式无效

知道为什么如果第二个失败，第一个可以工作吗？是否不允许在插入的sql参数或类似的东西中使用“分区依据”？

原文

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?

分享到QQ

分享到微博