ORA-01002:按顺序获取C ++ [英] ORA-01002: fetch out of sequence C++

问题描述

所以我无法弄清楚为什么它会不断抛出此ORA-01002:

So I can not figure out what is wrong why it keeps throwing this ORA-01002:

/**

//  compile: oracleCC fileName
//  Execute  a hardCoded sql query


/**  Program run snapshot

Connected to ORACLE as user: sp201511@cs11g

Query is  select supplierName from Supplier  where supplierNumber = 1

supplier name = Ray

*/


#include <iostream.h>
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sys/types.h>

// Parse=partial by default when code=cpp so, preprocessor directives are
// recognized and parsed fully.
#define     UNAME_LEN      20
#define     PWD_LEN        40

// Declare section is required when CODE=CPP and/or PARSE={PARTIAL|NONE}
exec sql begin declare section;
  VARCHAR username[UNAME_LEN];  // VARCHAR is an ORACLE supplied struct
                                // Has actual data & length
  varchar password[PWD_LEN];    // varchar can be in lower case also
  varchar sql_statement[1000];  //holds the sql stat being constructed..
  int supplierNo;
  char    inputSupplierName[50];        //entered using cin
  varchar outputSupplierName[50];       //retrieved output from database

exec sql end declare section;

// Declare error handling function
void sql_error(char *msg);
void connectToDatabase();
void commitAndExit() ;
void buildAhardCodedSQL();
void prepareAndExecuteIt ();
exec sql include sqlca;

main()
{

//  Call sql_error() function on any error in an embedded SQL statement
    exec sql WHENEVER SQLERROR DO sql_error("Oracle error");


    exec sql
        WHENEVER SQLERROR
        DO sql_error("ORACLE error:");
    connectToDatabase();

    buildAhardCodedSQL();
    prepareAndExecuteIt ();
    commitAndExit();

}

void connectToDatabase() {

/*  Hardcoded username and password  */
    strcpy((char *)username.arr,"myusername");
    strcpy((char *)password.arr,"password.");
    username.len = strlen((char *) username.arr);
    password.len = strlen((char *) password.arr);

//  CONNECTS TO DATABASE
    exec sql CONNECT :username IDENTIFIED BY :password;

    cout << "\nConnected to ORACLE as user: "
                << (char *)username.arr << endl << endl;

}


void commitAndExit() {

    exec sql commit work release;
    exit(0);
}
void sql_error(char *msg)
{
    exec sql WHENEVER SQLERROR CONTINUE;
    cout << endl << msg << endl;
    cout << sqlca.sqlerrm.sqlerrmc << endl;
    exec sql rollback release;
    exit(1);
}

void buildAhardCodedSQL() {
    strcpy((char *)sql_statement.arr,"select supplierNumber, supplierName ");
    strcat((char *)sql_statement.arr,"from supplier ");
    strcat((char *)sql_statement.arr,"where supplierNumber < > 1;");
    // Display the SQL statement and its current input host variable.
    cout << "Query is  " << (char *) sql_statement.arr << endl;
}
void  prepareAndExecuteIt() {

//  Prepare the query; define a cursor, execute it...

    sql_statement.len = strlen((char *) sql_statement.arr);
    exec sql PREPARE S1 FROM :sql_statement;

/* The declare statement associates a cursor with a
 * PREPAREd statement.  The cursor name, like the statement
 * name, does not appear in the Declare Section.
 * A single cursor name can not be declared more than once.
*/

    exec sql declare C1 cursor FOR S1;

    exec sql open C1;

    exec sql FETCH C1 INTO :supplierNo, :outputSupplierName;

     if ( sqlca.sqlcode !=  0) {
        cout << "past fetch..." <<endl;

        cout << sqlca.sqlerrm.sqlerrmc << endl;
        exec sql rollback release;
        exit(1);
     }
     cout << "supplier name = " << (char *)outputSupplierName.arr << endl;
     cout << "supplier number = " << supplierNo << endl;
   exec sql close C1;
}

它符合要求，但输出为

过去抓取......

past fetch....

ORA-01002:未按顺序提取

ORA-01002: fetch out of sequence

我尝试了很多事情让他们迷失了自己.

ive tried so many things im losing it guys.

推荐答案

您的提取失败，因为在此之前打开，声明和准备所有失败，但是您没有检查任何错误.如果这样做，则会出现ORA-00911: invalid character错误，原因是:

Your fetch is failing because the open, declare and prepare all failed before that point, but you didn't check for any errors from those. If you did you'd get an ORA-00911: invalid character error, because of this:

    strcpy((char *)sql_statement.arr,"select supplierNumber, supplierName ");
    strcat((char *)sql_statement.arr,"from supplier ");
    strcat((char *)sql_statement.arr,"where supplierNumber < > 1;");

构建的语句是

select supplierNumber, supplierName from supplier where supplierNumber < > 1;

但是您这样做:

exec sql PREPARE S1 FROM :sql_statement;

与动态SQL和JDBC调用一样，此prepare只能采用一条语句.构造的字符串末尾的分号是语句分隔符，在此上下文中没有任何意义，就解析器而言，它是无效的.

As with dynamic SQL and JDBC calls, this prepare can only take a single statement. The semicolon at the end of your constructed string is a statement separator which doesn't have any meaning in this context, and it is invalid as far as the parser is concerned.

因此，只需删除该分号即可:

So just remove that semicolon:

    strcpy((char *)sql_statement.arr,"select supplierNumber, supplierName ");
    strcat((char *)sql_statement.arr,"from supplier ");
    strcat((char *)sql_statement.arr,"where supplierNumber < > 1");

，并在每个exec sql之后添加错误检查.

and add an error check after each exec sql.

这篇关于ORA-01002:按顺序获取C ++的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！