CODEWITHSUNDEEP
c++sql-serverstored-proceduresodbc
geostocker
geostocker

Reputation: 1200

SQLSTATE 24000 - Invalid cursor state ODBC VS c++

I've got a fairly simple c++ application that uses ODBC to connect to a SQL Server instance that uses a stored procedure to populate.

I use the lines of a wstring object to build a query that is then passed through to the stored procedure. Everything works fine if I run it a single time - however, I want to be able to loop through a quite extensive amount of code (3000+ excel rows) and as I do it the cursor error mentioned in the title occurs.

This is the stored procedure:

GO
CREATE PROCEDURE s_addHistorical
    @Symbol nchar(10),@Date datetime,
    @Open decimal(8,2),@Close decimal(8,2),@MinPrice decimal(8,2),
    @MaxPrice decimal(8,2),@Volume int
AS 
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    BEGIN TRANSACTION
    MERGE HistoricalStock WITH (UPDLOCK) AS myTarget
        USING (SELECT @Symbol AS Symbol,
        @Date AS Date, @Open AS [Open], @Close AS [Close],
        @MinPrice AS MinPrice, @MaxPrice AS MaxPrice,@Volume AS Volume) AS mySource
        ON mySource.Symbol = myTarget.Symbol AND mySource.Date = myTarget.Date
        WHEN MATCHED 
            THEN UPDATE 
                SET [Open] = mySource.[Open], [Close] = mySource.[Close],
                MinPrice = mySource.MinPrice, MaxPrice = mySource.MaxPrice, Volume = mySource.Volume            
        WHEN NOT MATCHED
            THEN
                INSERT(Symbol,Date,[Open],[Close],MinPrice,MaxPrice,Volume)
                VALUES(@Symbol,@Date,@Open,@Close,@MinPrice,@MaxPrice,@Volume);
    COMMIT 
GO

And this is the connector:

#include "stdafx.h"
#include "database_con.h"

////////////////////////////////////////////////////////////////////////
// Show errors from the SQLHANDLE

void database_con::show_error(unsigned int handletype, const SQLHANDLE& handle)
{
    SQLWCHAR sqlstate[1024];
    SQLWCHAR message[1024];
    if (SQL_SUCCESS == SQLGetDiagRec(handletype, handle, 1, sqlstate, NULL, message, 1024, NULL))
        wcout << "Message: " << message << "\nSQLSTATE: " << sqlstate << endl;
}


////////////////////////////////////////////////////////////////////////
// Builds the stored procedure query.

std::wstring database_con::buildQuery(vector<std::wstring> input)
{
    std::cout << "Building the query" << std::endl;
    std::wstringstream builder;
    builder << L"EXEC sp_addHistorical " << "@Symbol='" << L"" << input.at(0) << "'," <<
        "@Date=" << (wstring)L"" << input.at(1) << "," <<
        "@Open=" << (wstring)L"" << input.at(2) << "," <<
        "@Close=" << (wstring)L"" << input.at(3) << "," <<
        "@MaxPrice=" << (wstring)L"" << input.at(4) << "," <<
        "@MinPrice=" << (wstring)L"" << input.at(5) << "," <<
        "@Volume=" << (wstring)L"" << input.at(6) << ";";
    return builder.str();
}

////////////////////////////////////////////////////////////////////////
// Adds a substring of the string before the delimiter to a vector<wstring> that is returned.

std::vector<wstring> database_con::parseData(wstring line, char delim) {
    size_t pos = 0;
    std::vector<std::wstring> vOut;
    while ((pos = line.find(delim)) != std::string::npos) {
        vOut.push_back(line.substr(0, pos));
        line.erase(0, pos + 2);
    }
    vOut.push_back(line.substr(0, pos));
    return vOut;
}

std::wstring database_con::StringToWString(const std::string& s)
{
    std::wstring temp(s.length(), L' ');
    std::copy(s.begin(), s.end(), temp.begin());
    return temp;
}

database_con::database_con(std::string historical){
    /*
    Set up the handlers
    */

    /* Allocate an environment handle */
    SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
    /* We want ODBC 3 support */
    SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
    /* Allocate a connection handle */
    SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);

    /* Connect to the DSN */
    SQLDriverConnectW(dbc, NULL, L"DRIVER={SQL Server};SERVER=ERA-PC-STUART\\JBK_DB;DATABASE=master;UID=geo;PWD=kalle123;", SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
    /* Check for success */
    if (SQL_SUCCESS != SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt))
    {
        show_error(SQL_HANDLE_DBC, dbc);
        std::cout << "Failed to connect";
    }
    std::wstringstream stream(StringToWString(historical));
    std::wstring line;
    while (std::getline(stream, line)) {
        vector<wstring> vHistorical = parseData(L"" + line, ',');
        std::wstring SQL = buildQuery(vHistorical);

        if (SQL_SUCCESS != SQLExecDirectW(stmt, const_cast<SQLWCHAR*>(SQL.c_str()), SQL_NTS)) {
            show_error(SQL_HANDLE_STMT, stmt);
        }

    }

}

database_con::~database_con() {

}

I've been looking around at google and on SO, but I can't seem to find any questions that are usable in my current question. Most of them seem to be revolving around stored procedures sending back some sort of extracts, whereas my SP is simply inserting / updating.

Any sort of help would be greatly appriciated. :)

Anyone?

Upvotes: 0

Views: 5665

Answers (1)

erg
erg

Reputation: 1652

You need to call SQLCloseCursor to release the Cursor.

Change your code to:

while (std::getline(stream, line)) {
    vector<wstring> vHistorical = parseData(L"" + line, ',');
    std::wstring SQL = buildQuery(vHistorical);

    if (SQL_SUCCESS != SQLExecDirectW(stmt, const_cast<SQLWCHAR*>(SQL.c_str()), SQL_NTS)) {
        show_error(SQL_HANDLE_STMT, stmt);
    }
    // Close Cursor before next iteration starts:
    SQLRETURN closeCursRet = SQLCLoseCursor(stmt);
    if(!SQL_SUCCEEDED(closeCursRet))
    {
        // maybe add some handling for the case that closing failed.
    }
}

See: https://msdn.microsoft.com/en-us/library/ms709301%28v=vs.85%29.aspx

Upvotes: 2

Related Questions