Re: UPDATE with data at exec and CURRENT OF question - Mailing list pgsql-odbc

On 9 Aug., 10:43, z-sa...@guitar.ocn.ne.jp ("Hiroshi Saito") wrote:
> Hi.
>
> Sorry, very late reaction.
> > I am still using 07.03.0200 on Mac OS X. I didn't get compiled the
> > 07.03.0260 version due to missing
> > pg_config.
>
> Can't you use the series of 08.02.xx, either?
> I think It should be cleared if there are some problems.
>

My application and my ODBC wrapper class relates to full cursor
functionality (first,previous,next,last). So I only know about 7.3.2xx
drivers.

> > What's wrong ?
>
> However, I can't immediately check the following code...
> Slight time is needed. I appreciate your perseverance.
>

Now I have a better working code. It is able to put data in 1000 byte
pieces, but the driver
(SQLParamData(...)) does not indicate, that it needs more data. Also
the getBinaryData
function returns no more data, that the size of the buffer (here
5000).

I could increase the buffer to the buffer in my value object, but I
probably could not go
ahead the maximum of available memory.

So I tried all to put the data in pieces of, say, 1000 or 5000 bytes,
both functions do not
behave as expected from Microsoft documentation from here:

http://msdn2.microsoft.com/en-us/library/ms713824.aspx

I have tried to ignore SQL_NEED_DATA and have calculated a remaining
size to be handled
until it decreases to the piece size to put the rest. But then the
result behaves like storing rubbish.

The lb_I_BinaryData class is a container where I could append pieces
and I think it is unnesesary
to copy that code too.

I have tried all I can get from documentation. I have reached the end
of my brain :-)

Hope you will see any wrong API usage or parameters. But I assume,
there is a bug in
the 07.03.0200 driver.

Actually I do no more use CURRENT OF <cursor name>, because PostgreSQL
doesn't support
that syntax (at least in my old 7.4 database).

I do a trick by first bind the binary column and put a unique marker
to that column for later update
filtering. I hope the cursorname is unique. I probably have to make
this function thread save !

Sorry for the big code.

Thanks, Lothar

lb_I_BinaryData* LB_STDCALL lbQuery::getBinaryData(int column) {
    // Declare a binary buffer to retrieve 5000 bytes of data at a time.
    SQLCHAR       BinaryPtr[5000];
    SQLUINTEGER   PartID;
    SQLINTEGER    PartIDInd, BinaryLenOrInd, NumBytes;
    SQLRETURN     rc, retcode;
    SQLHSTMT      hstmt_blob;

       // Make an instance of my binarydata container class.
    UAP_REQUEST(getModuleInstance(), lb_I_BinaryData, binarydata)

    if (boundColumns != NULL) {
        if (boundColumns->isBound(column)) {

   // My log mechanism
            _LOG << "Error: binary column " << column << "shouldn't be bound!"
LOG_
            binarydata->append((void*) "", 1);

   // Referencecounting. Avoid cleanup at this scope.
            binarydata++;
            return binarydata.getPtr();
        } else {
            while ((rc = SQLGetData(hstmt, column, SQL_C_BINARY, BinaryPtr,
sizeof(BinaryPtr), &BinaryLenOrInd)) != SQL_NO_DATA) {
                NumBytes = (BinaryLenOrInd > 5000) || (BinaryLenOrInd ==
SQL_NO_TOTAL) ? 5000 : BinaryLenOrInd;
                if (BinaryLenOrInd == SQL_NULL_DATA) {
                    binarydata->append("", 1);
                    binarydata++;
                    return binarydata.getPtr();
                }
                if (BinaryLenOrInd == 0) {
                    binarydata->append("", 1);
                    binarydata++;
                    return binarydata.getPtr();
                }
                binarydata->append(BinaryPtr, NumBytes-1);
            }
        }
    }

    binarydata->append((void*) "", 1);

    binarydata++;
    return binarydata.getPtr();
}

lb_I_BinaryData* LB_STDCALL lbQuery::getBinaryData(const char* column)
{
    if (boundColumns != NULL) {
        int i = boundColumns->getColumnIndex(column);
        return getBinaryData(i);
    }
    _LOG << "lbQuery::getBinaryData('" << column << "') Error: No bound
columns!" LOG_
    UAP_REQUEST(getModuleInstance(), lb_I_BinaryData, binary)
    binary++;
    binary->append("", 1);
    return binary.getPtr();
}

lbErrCodes LB_STDCALL lbQuery::setBinaryData(int column,
lb_I_BinaryData* value) {
#define LB_BLOCKSIZE 1000
#define USE_CURRENT_OF

#ifdef USE_CURRENT_OF
#undef USE_CURRENT_OF
    UAP_REQUEST(getModuleInstance(), lb_I_String, update_query)

    SQLRETURN     rc, retcode;
    SQLCHAR*       BinaryPtr;
    SQLCHAR       BinaryPtrCur[100];
    long          realBufferSize;
    void*          tempBuffer;
    long          remainingsize;
    SQLINTEGER    BinaryLenOrIndCurrentOf;
    SQLINTEGER    BinaryLenOrInd;
    SQLINTEGER    PutDataSize;

    retcode = SQLAllocStmt(hdbc, &hupdatestmt); /* Statement handle */

    if (retcode != SQL_SUCCESS)
    {
            _LOG << "lbDatabase::getQuery() failed due to statement
allocation." LOG_
            return ERR_DB_ALLOCSTATEMENT;
    }

    retcode = SQLSetStmtOption(hupdatestmt, SQL_ATTR_CONCURRENCY,
SQL_CONCUR_ROWVER);

    retcode = SQLSetStmtOption(hupdatestmt, SQL_CURSOR_TYPE,
SQL_CURSOR_KEYSET_DRIVEN);

    if (retcode == SQL_SUCCESS_WITH_INFO) {
        _LOG << "lbDatabase::getQuery() failed due to setting cursor type."
LOG_
    } else
        if (retcode != SQL_SUCCESS)
        {
                _LOG << "lbDatabase::getQuery() failed due to setting
cursor type." LOG_
                return ERR_DB_ALLOCSTATEMENT;
        }

    SQLINTEGER size = 1;

    if (retcode != SQL_SUCCESS) {
        _LOG << "lbDatabase::getQuery() failed due to set statement
attributes." LOG_
        return ERR_DB_ALLOCSTATEMENT;
    }

    remainingsize = value->getSize();
    BinaryLenOrInd = value->getSize();

    BinaryLenOrIndCurrentOf = strlen(cursorname)+1;

    rc = SQLBindCol(hstmt, column, SQL_C_BINARY, (void *)BinaryPtrCur,
BinaryLenOrIndCurrentOf, &BinaryLenOrIndCurrentOf);
    memcpy(BinaryPtrCur, cursorname, BinaryLenOrIndCurrentOf);

    update();

    retcode = SQLBindCol(hstmt, column, SQL_C_BINARY, NULL, 0, 0);

    tempBuffer = value->getData();
    BinaryLenOrInd = value->getSize();
    remainingsize = value->getSize();

    *update_query = "UPDATE \"";
    *update_query += getTableName(getColumnName(column));
    *update_query += "\" SET \"";
    *update_query += getColumnName(column);
    *update_query += "\" = ? WHERE \"";
    *update_query += getColumnName(column);
    *update_query += "\" LIKE '";
    *update_query += cursorname;
    *update_query += "%'";

    _LOG << "Prepare positioned BLOB update: '" << update_query-
>charrep() << "' with length of data = " << BinaryLenOrInd LOG_

    retcode = SQLPrepare(hupdatestmt, update_query->charrep(), SQL_NTS);

    if (retcode != SQL_SUCCESS) {
        _LOG << "Preparing update statement failed." LOG_
    }

    if (remainingsize > LB_BLOCKSIZE) {
        //BinaryLenOrInd = SQL_LEN_DATA_AT_EXEC(value->getSize());
        BinaryLenOrInd = SQL_LEN_DATA_AT_EXEC(value->getSize());

        realBufferSize = LB_BLOCKSIZE;
        BinaryPtr = malloc(realBufferSize);


        _LOG << "Call SQLBindParameter with a length indicator value of " <<
BinaryLenOrInd << "." LOG_

        retcode = SQLBindParameter(hupdatestmt, 1, SQL_PARAM_INPUT,
                  SQL_C_BINARY, SQL_LONGVARBINARY,
                  value->getSize(), 0, (SQLPOINTER) 1, LB_BLOCKSIZE,
&BinaryLenOrInd);

    } else {
        realBufferSize = remainingsize;
        BinaryLenOrInd = remainingsize;
        BinaryPtr = malloc(remainingsize);
        retcode = SQLBindParameter(hupdatestmt, 1, SQL_PARAM_INPUT,
                  SQL_C_BINARY, SQL_LONGVARBINARY,
                  0, 0, (SQLPOINTER) &BinaryPtr, BinaryLenOrInd,
&BinaryLenOrInd);
    }


    if (retcode != SQL_SUCCESS) {
        _LOG << "Binding update parameter failed." LOG_
    }

    _LOG << "Executing positioned BLOB update: '" << update_query-
>charrep() << "' with length of data = " << BinaryLenOrInd LOG_

    retcode = SQLSetPos(hstmt, 1, SQL_REFRESH, SQL_LOCK_NO_CHANGE);
    //retcode = SQLSetPos(hupdatestmt, 1, SQL_REFRESH,
SQL_LOCK_NO_CHANGE);
    retcode = SQLExecute(hupdatestmt);

    long iteration = 0;

    if ((retcode != SQL_SUCCESS) && (retcode != SQL_NEED_DATA)) {
        _LOG << "Execute query failed." LOG_
        //_dbError_STMT("Executing positioned BLOB update failed.",
hupdatestmt);
    }

    if (retcode == SQL_NEED_DATA)
    {
        SQLPOINTER putDataBuffer;
        retcode = SQLParamData(hupdatestmt, (void **)  &putDataBuffer);
        while(retcode == SQL_NEED_DATA)
        {
            _LOG << "lbQuery::setBinaryData() Needs more data ..." <<
remainingsize    LOG_
            if (remainingsize <= realBufferSize) {
                _LOG << "Copy lesser memory piece of " << remainingsize << "
bytes." LOG_
                memcpy(BinaryPtr, tempBuffer, remainingsize);
                PutDataSize = remainingsize;
                retcode = SQLPutData(hupdatestmt, BinaryPtr, PutDataSize);
                retcode = SQLParamData(hupdatestmt, (void **)  &putDataBuffer);
                tempBuffer += realBufferSize;
                remainingsize -= realBufferSize;
            } else {

                            //
********************************************************************************
                            // Force multible calls to SQLPutData and
SQLParamData doesn't help
                            //
********************************************************************************

//                while (remainingsize > realBufferSize) {
                    _LOG << "Copy maximum memory piece of " << realBufferSize << "
bytes." LOG_
                    memcpy(BinaryPtr, tempBuffer, realBufferSize);
                    PutDataSize = realBufferSize;
                    retcode = SQLPutData(hupdatestmt, BinaryPtr, PutDataSize);
                    retcode = SQLParamData(hupdatestmt, (void **)  &putDataBuffer);
                    tempBuffer += realBufferSize;
                    remainingsize -= realBufferSize;
//                }
            }
        }

    }
    retcode = SQLSetPos(hstmt, 1, SQL_REFRESH, SQL_LOCK_NO_CHANGE);

    SQLFreeStmt(hupdatestmt, SQL_DROP);
#endif

#ifdef USE_SETPOS
    // Declare a binary buffer to retrieve 5000 bytes of data at a time.
    SQLCHAR       BinaryPtr[LB_BLOCKSIZE];
    SQLUINTEGER   PartID;
    SQLINTEGER    PartIDInd, BinaryLenOrInd, NumBytes;
    SQLINTEGER      longDataLen;
    SQLRETURN     rc, retcode;
    SQLHSTMT      hstmt_blob;

    void*          tempBuffer;
    long          remainingsize;

    tempBuffer = value->getData();

    if (value->getSize() <= LB_BLOCKSIZE) {
        memcpy(BinaryPtr, tempBuffer, value->getSize());
    } else {
        memcpy(BinaryPtr, tempBuffer, LB_BLOCKSIZE);
    }

    remainingsize = value->getSize();

    longDataLen = SQL_LEN_DATA_AT_EXEC(value->getSize());

    rc = SQLBindCol(hstmt, column, SQL_C_BINARY, (void *)BinaryPtr, 0,
&longDataLen);

    retcode = SQLSetPos(hstmt, 1, SQL_UPDATE, SQL_LOCK_NO_CHANGE);

    long iteration = 0;

    if (retcode == SQL_NEED_DATA)
    {
        retcode = SQLParamData(hstmt, (void **)  &BinaryPtr);
        while(retcode == SQL_NEED_DATA)
        {
            tempBuffer += LB_BLOCKSIZE;
            remainingsize -= LB_BLOCKSIZE;

            if (remainingsize <= LB_BLOCKSIZE) {
                memcpy(BinaryPtr, tempBuffer, remainingsize);
            } else {
                memcpy(BinaryPtr, tempBuffer, LB_BLOCKSIZE);
            }


            retcode = SQLPutData(hstmt, BinaryPtr, SQL_NTS);
            /* check for error here */
            retcode = SQLParamData(hstmt, (void **)  &BinaryPtr);
        }

    }

    rc = SQLBindCol(hstmt, column, SQL_C_BINARY, NULL, 0, 0);
#endif

    return ERR_NONE;
}

lbErrCodes LB_STDCALL lbQuery::setBinaryData(const char* column,
lb_I_BinaryData* value) {
    if (boundColumns != NULL) {
        int i = boundColumns->getColumnIndex(column);
        return setBinaryData(i, value);
    }

    return ERR_NONE;
}