C++ SQLCLI routine(s) won't run more than once - IBM AS400

This is a discussion on C++ SQLCLI routine(s) won't run more than once - IBM AS400 ; I'm not sure what I'm doing wrong here. I thought I was doing everything according to the patterns I'd found in the online stuff for doing SQLCLI w/ DB2. I have a routine that I can call once, but not ...

+ Reply to Thread
Results 1 to 3 of 3

Thread: C++ SQLCLI routine(s) won't run more than once

  1. C++ SQLCLI routine(s) won't run more than once

    I'm not sure what I'm doing wrong here. I thought I was doing
    everything according to the patterns I'd found in the online stuff for
    doing SQLCLI w/ DB2. I have a routine that I can call once, but not a
    second time. My basic pattern is to create a statement handle, do
    some stuff, call it, get the results, then free the handle. When the
    routine gets called a second time it can't get the statement handle
    again. There's a little bit of supporting code that I'll post in a
    reply to this message if you really want to see it but this is it.
    I'm compiling and testing this on two machines: one has V6R1, the
    other V5R2 and the same result. I don't get any meaningful error
    codes or messages on the second iteration.
    /* MAIN TEST PROGRAM */
    #include
    #include
    #include
    #include
    #include
    #include
    #include "FTPDatabase.h"
    #include "ftp_status.h"
    #include "FTPCompanyMaster.h"
    #include "utilcli.h"

    using namespace std;


    bool GetStartCode(FTPDatabase db, const string fileLibrary, string&
    startCode);
    /*
    CRTCPPMOD MODULE(*CURLIB/GET_TEST) SRCSTMF('/home/kbeard/
    get_test.cpp') OUTPUT(*print) CSOPT('-qnamemangling=v5') INCDIR('/qibm/
    proddata/xmltoolkit2/xml5_6_0/include' '/home/kbeard/ftp')
    CRTPGM PGM(GETFTPSTUP) BNDSRVPGM((QXMLLIB/QXML4C560)) BNDDIR(*LIBL/
    TRANS_ENG) OPTION(*DUPVAR *DUPPROC *NOWARN) DETAIL(*BASIC)
    */
    int main(int argc, char *argv[])
    {
    string startCode;
    FTPDatabase db("*LOCAL", "ICCTEST01P", "", "");

    cerr << "first run" << endl;
    cerr << GetStartCode(db, "ICCTEST01P", startCode) << endl;
    cerr << startCode << endl;
    cerr << "second run" << endl;
    cerr << GetStartCode(db, "ICCTEST01P", startCode) << endl;
    cerr << startCode << endl;
    }

    /* ACTUAL PROBLEMATIC ROUTINE HERE */
    #include
    #include
    #include
    #include
    #include
    #include "FTPDatabase.h"
    #include "utilcli.h"

    using namespace std;

    /*
    CRTCPPMOD
    MODULE(*CURLIB/GETSTARTCD)
    SRCSTMF('/home/kbeard/ftp/GetStartCode.cpp')
    OUTPUT(*print)
    INCDIR('/home/kbeard/ftp')

    CRTPGM
    PGM(GETSTARTCD)
    BNDDIR(KBEARD/TRANS_ENG)
    TGTRLS(*CURRENT)
    */

    bool SQLResultPrint(SQLHANDLE, SQLRETURN&, vector&);
    void trim2(string&);

    /*
    Module:
    GetStartCode()
    Synopsis:
    Returns:
    true if no SQL were encountered; false otherwise.
    Written by:
    Kelly Beard
    */
    bool GetStartCode(FTPDatabase db, const string fileLibrary, string&
    startCode)
    {
    bool ret;
    SQLRETURN sqlRet;
    SQLHANDLE h_SqlStmt;
    SQLCHAR SQLState[10], MessageText[200];
    SQLINTEGER NativeError;
    SQLSMALLINT TextLength;
    string statementString;
    vector stringArray;


    /*
    First step is to fetch the value...
    */
    statementString = "SELECT DFSCODE FROM " + db.getDefaultLibrary()
    +
    ".DFMASTERP WHERE DFDLIB = '" + fileLibrary + "'" +
    " FETCH FIRST 1 ROWS ONLY";

    char *TempString = new char[statementString.size() + 1];
    strcpy(TempString, statementString.c_str());

    sqlRet = db.CreateStatementHandle(h_SqlStmt);
    if (sqlRet != SQL_SUCCESS && sqlRet != SQL_SUCCESS_WITH_INFO) {

    /*
    It's safe to assume here that we didn't find our record so
    build
    some sort of message to return to the caller.
    */
    SQLGetDiagRec(SQL_HANDLE_STMT, h_SqlStmt, 1, SQLState,
    &NativeError, MessageText, 200, &TextLength);

    cerr << __FILE__ << "(" << __LINE__ <<
    "): sqlRet=" << sqlRet <<
    " NativeError=" << NativeError <<
    " SqlState=" << SQLState <<
    " Message=" << MessageText << endl;

    ret = false;
    }

    sqlRet = SQLExecDirect(h_SqlStmt, TempString, SQL_NTS);

    delete[] TempString;

    if (sqlRet != SQL_SUCCESS && sqlRet != SQL_SUCCESS_WITH_INFO) {

    SQLGetDiagRec(SQL_HANDLE_STMT, h_SqlStmt, 1, SQLState,
    &NativeError, MessageText, 200, &TextLength);

    cerr << __FILE__ << "(" << __LINE__ <<
    "): sqlRet=" << sqlRet <<
    " NativeError=" << NativeError <<
    " SqlState=" << SQLState <<
    " Message=" << MessageText << endl;

    ret = false;
    }
    else {
    /*
    Any code in this block isn't causing any weird problems. If I
    comment this
    out I still get errors the second time this routine is run.
    */
    ret = false;
    SQLResultPrint(h_SqlStmt, sqlRet, stringArray);
    if (stringArray.size() > 0) {
    trim2(stringArray[0]);
    startCode = stringArray[0];
    ret = true;
    }
    }

    /*
    Final cleanup.
    */
    db.FreeStatementHandle(h_SqlStmt);
    return ret;
    }


  2. Re: C++ SQLCLI routine(s) won't run more than once

    This is a C++ class I ****ed up to try and encapsulate all of the CLI
    startup stuff you have to go through.

    /* HEADER FILE */

    #include
    #include
    #include

    using namespace std;

    class FTPDatabase {
    public :
    SQLHENV cv_hSqlEnv;
    SQLHDBC cv_hSqlDbc;
    bool cv_databaseIsOpen;
    string cv_defaultLibrary;

    FTPDatabase(char *databaseName, char *library, char *user, char
    *password);
    ~FTPDatabase();
    bool IsConstructorOK();
    SQLRETURN CreateStatementHandle(SQLHSTMT& handle);
    bool FreeStatementHandle(SQLHSTMT handle);
    string getDefaultLibrary();
    };


    /* EXECUTABLE FILE */

    #include
    #include
    #include
    #include
    #include "FTPDatabase.h"


    /*
    CRTCPPMOD MODULE(*CURLIB/FTPDATABAS) SRCSTMF('/home/kbeard/ftp/
    FTPDatabase.cpp') OUTPUT(*print) SYSIFCOPT(*IFSIO)
    */

    using namespace std;

    FTPDatabase::FTPDatabase(char *databaseName, char *library, char
    *user, char *password)
    {
    SQLRETURN sqlRet;
    SQLHSTMT h_SqlStmt;
    SQLCHAR SQLState[10], messageText[200];
    SQLINTEGER nativeError;
    SQLSMALLINT textLength;



    cv_defaultLibrary = library;

    /*
    To begin, you have to get an Environment handle.
    */
    sqlRet = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE,
    &cv_hSqlEnv);
    if (sqlRet != SQL_SUCCESS && sqlRet != SQL_SUCCESS_WITH_INFO) {

    /*
    SQL_INVALID_HANDLE
    SQL_ERROR
    */
    cv_databaseIsOpen = false;
    SQLGetDiagRec(SQL_HANDLE_STMT, cv_hSqlEnv, 1, SQLState,
    &nativeError, messageText, 200, &textLength);
    cerr << __FILE__ << "(" << __LINE__ <<
    "): sqlRet=" << sqlRet <<
    " NativeError=" << nativeError <<
    " SqlState=" << SQLState <<
    " Message=" << messageText << endl;
    }
    else {

    /*
    Now you have to get a database connection handle. You're
    not actually connected at this point.
    */
    sqlRet = SQLAllocHandle(SQL_HANDLE_DBC, cv_hSqlEnv,
    &cv_hSqlDbc);
    if (sqlRet != SQL_SUCCESS && sqlRet != SQL_SUCCESS_WITH_INFO)
    {
    /*
    SQL_INVALID_HANDLE
    SQL_ERROR
    */
    SQLGetDiagRec(SQL_HANDLE_STMT, cv_hSqlEnv, 1, SQLState,
    &nativeError, messageText, 200, &textLength);
    cerr << __FILE__ << "(" << __LINE__ <<
    "): sqlRet=" << sqlRet <<
    " NativeError=" << nativeError <<
    " SqlState=" << SQLState <<
    " Message=" << messageText << endl;
    }

    /*
    Setting this prevents UPDATES from failing because of
    commitment control.
    */
    int commitmentValue = SQL_TXN_NO_COMMIT;
    sqlRet = SQLSetConnectAttr(cv_hSqlDbc, SQL_ATTR_COMMIT,
    &commitmentValue, 0);
    if (sqlRet != SQL_SUCCESS && sqlRet != SQL_SUCCESS_WITH_INFO)
    {
    SQLGetDiagRec(SQL_HANDLE_DBC, cv_hSqlDbc, 1, SQLState,
    &nativeError, messageText, 200, &textLength);
    cerr << __FILE__ << "(" << __LINE__ <<
    "): sqlRet=" << sqlRet <<
    " NativeError=" << nativeError <<
    " SqlState=" << SQLState <<
    " Message=" << messageText << endl;
    }

    /*
    Now do the actual connection
    */
    sqlRet = SQLConnect(cv_hSqlDbc, databaseName, SQL_NTS, user,
    SQL_NTS, password, SQL_NTS);
    if (sqlRet != SQL_SUCCESS && sqlRet != SQL_SUCCESS_WITH_INFO)
    {
    SQLGetDiagRec(SQL_HANDLE_DBC, cv_hSqlDbc, 1, SQLState,
    &nativeError, messageText, 200, &textLength);
    cerr << __FILE__ << "(" << __LINE__ <<
    "): sqlRet=" << sqlRet <<
    " NativeError=" << nativeError <<
    " SqlState=" << SQLState <<
    " Message=" << messageText << endl;
    }
    else {
    }
    }
    }


    /*
    */
    FTPDatabase::~FTPDatabase()
    {
    SQLDisconnect(cv_hSqlDbc);

    SQLFreeHandle(SQL_HANDLE_DBC, cv_hSqlDbc);
    SQLFreeHandle(SQL_HANDLE_ENV, cv_hSqlEnv);
    }


    /*
    */
    SQLRETURN FTPDatabase::CreateStatementHandle(SQLHSTMT& handle)
    {
    SQLRETURN sqlRet;
    SQLHSTMT h_SqlStmt;


    sqlRet = SQLAllocHandle(SQL_HANDLE_STMT, cv_hSqlDbc, &h_SqlStmt);
    handle = h_SqlStmt;
    return sqlRet;
    }


    /*
    */
    bool FTPDatabase::FreeStatementHandle(SQLHSTMT handle)
    {
    SQLRETURN sqlRet;


    sqlRet = SQLFreeHandle(SQL_HANDLE_STMT, handle);
    return (sqlRet == SQL_SUCCESS || sqlRet == SQL_SUCCESS_WITH_INFO);
    }


    bool FTPDatabase::IsConstructorOK()
    {
    return cv_databaseIsOpen;
    }


    string FTPDatabase::getDefaultLibrary()
    {
    return cv_defaultLibrary;
    }

  3. Re: C++ SQLCLI routine(s) won't run more than once

    Dammit, this always happens to me. I'm glad I don't have much of an
    ego for brusing otherwise I'd probably be dead by now. Anyway, I was
    goofing around with the code and found that if I change the function
    definition of GetStartCode() to be :

    bool GetStartCode(FTPDatabase& db, const string fileLibrary, string&
    startCode);

    instead of:

    bool GetStartCode(FTPDatabase db, const string fileLibrary, string&
    startCode);

    It'll run as many times as I want it to now.



+ Reply to Thread