Thread: SQLSetPos problem ?
Hi, I am using unixodbc in the latest version and psqlodbc 07.03.0200. Having many queries working good in my application one gives me this error, when updating a row. Any hints ? Thanks, Lothar [SQLSetPos]PGAPI_SetPos fOption=2 irow=1 lock=0 currt=-1 STATEMENT ERROR: func=PGAPI_SetPos, desc='', errnum=21, errmsg='Row value out of range' CONN ERROR: func=PGAPI_SetPos, desc='', errnum=0, errmsg='(NULL)' [SQLError]**** PGAPI_Error: henv=0, hdbc=0 hstmt=170626352 **** PGAPI_StmtError: hstmt=170626352 <512> SC_get_error: status = 21, msg = #Row value out of range# szSqlState = 'HY107',len=22, szError='Row value out of range' **** PGAPI_Error exit code=0 [SQLError]**** PGAPI_Error: henv=0, hdbc=0 hstmt=170626352 **** PGAPI_StmtError: hstmt=170626352 <512> SC_Get_error returned nothing. **** PGAPI_Error exit code=100
lothar.behrens@lollisoft.de wrote: > Hi, > > I am using unixodbc in the latest version and psqlodbc 07.03.0200. > Having many queries working > good in my application one gives me this error, when updating a row. The version 7.3.0200 is pretty old. Please try the 8.2.xxxx version. regards, Hiroshi Inoue
Hiroshi Inoue schrieb: > lothar.behrens@lollisoft.de wrote: > > Hi, > > > > I am using unixodbc in the latest version and psqlodbc 07.03.0200. > > Having many queries working > > good in my application one gives me this error, when updating a row. > > The version 7.3.0200 is pretty old. > Please try the 8.2.xxxx version. > I need cursor functionality like first,back,next,last. Does the latest driver support it ? Regards, Lothar > regards, > Hiroshi Inoue > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org
lothar.behrens@lollisoft.de wrote: > Hiroshi Inoue schrieb: > >> lothar.behrens@lollisoft.de wrote: >>> Hi, >>> >>> I am using unixodbc in the latest version and psqlodbc 07.03.0200. >>> Having many queries working >>> good in my application one gives me this error, when updating a row. >> The version 7.3.0200 is pretty old. >> Please try the 8.2.xxxx version. >> > > I need cursor functionality like first,back,next,last. Does the latest > driver support it ? Yes at least on Windows. regards, Hiroshi Inoue
Hiroshi Inoue schrieb: > lothar.behrens@lollisoft.de wrote: > > Hiroshi Inoue schrieb: > > > >> lothar.behrens@lollisoft.de wrote: > >>> Hi, > >>> > >>> I am using unixodbc in the latest version and psqlodbc 07.03.0200. > >>> Having many queries working > >>> good in my application one gives me this error, when updating a row. > >> The version 7.3.0200 is pretty old. > >> Please try the 8.2.xxxx version. > >> > > > > I need cursor functionality like first,back,next,last. Does the latest > > driver support it ? > > Yes at least on Windows. Currently I have figured out, that my SQL_ADD statement fails (SQLSetPos). It seems, that I cannot insert new rows, because of foreign key constraints. Removing the NOT NULL clauses of that columns didn't helped. With PGAdmin I was able to test inserting such a row. That worked. Any ideas ? If the bound columns having the wrong value - such as a non exsisting foreign key - how must it be filled to indicate a NULL value ? I bind such a column that way: buffer = malloc((ColumnSize+1)*rows); memset(buffer, 0, (ColumnSize+1)*rows); ret = SQLBindCol(hstmt, column, SQL_C_CHAR, buffer, (ColumnSize+1), &cbBufferLength); Is that wrong ? Regardless, I will also try the newest psqlODBC driver and also create a sample console app to insert into the same table. The GUI application is too complex. Lothar The logfile reports this error: [SQLError]**** PGAPI_Error: henv=0, hdbc=0 hstmt=170046048 **** PGAPI_StmtError: hstmt=170046048 <512> SC_get_error: status = 7, msg = #Error while executing the query; FEHLER: Einf�gen oder Aktualisieren in Tabelle �user_anwendungen� verletzt Fremdschl�ssel-Constraint �cst_user_anwendungen_userid�# szSqlState = 'HY000',len=170, szError='Error while executing the query; FEHLER: Einf�gen oder Aktualisieren in Tabelle �user_anwendungen� verletzt Fremdschl�ssel-Constraint �cst_user_anwendungen_userid�' **** PGAPI_Error exit code=0 [SQLError]**** PGAPI_Error: henv=0, hdbc=0 hstmt=170046048 **** PGAPI_StmtError: hstmt=170046048 <512> SC_Get_error returned nothing. Thanks, Lothar > > regards, > Hiroshi Inoue > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings
lothar.behrens@lollisoft.de wrote: > Hiroshi Inoue schrieb: > >> lothar.behrens@lollisoft.de wrote: >>> Hiroshi Inoue schrieb: >>> >>>> lothar.behrens@lollisoft.de wrote: >>>>> Hi, >>>>> >>>>> I am using unixodbc in the latest version and psqlodbc 07.03.0200. >>>>> Having many queries working >>>>> good in my application one gives me this error, when updating a row. >>>> The version 7.3.0200 is pretty old. >>>> Please try the 8.2.xxxx version. >>>> >>> I need cursor functionality like first,back,next,last. Does the latest >>> driver support it ? >> Yes at least on Windows. > > Currently I have figured out, that my SQL_ADD statement fails > (SQLSetPos). > It seems, that I cannot insert new rows, because of foreign key > constraints. > > Removing the NOT NULL clauses of that columns didn't helped. > > With PGAdmin I was able to test inserting such a row. That worked. > > Any ideas ? How do you setting the foreign key column using PGadmin ? > If the bound columns having the wrong value - such as a non exsisting > foreign key - > how must it be filled to indicate a NULL value ? > > I bind such a column that way: > > buffer = malloc((ColumnSize+1)*rows); > memset(buffer, 0, (ColumnSize+1)*rows); > ret = SQLBindCol(hstmt, column, SQL_C_CHAR, buffer, (ColumnSize+1), > &cbBufferLength); > > Is that wrong ? I'm afraid I'm misunderstanding your point. Please set SQL_NULL_DATA to cbBufferLength to indicate the column is NULL. Also set SQL_COLUMN_IGNORE to cbBufferLength to ignore the column. regards, Hiroshi Inoue
Hiroshi Inoue schrieb: > lothar.behrens@lollisoft.de wrote: > > Hiroshi Inoue schrieb: > > > >> lothar.behrens@lollisoft.de wrote: > >>> Hiroshi Inoue schrieb: > >>> > >>>> lothar.behrens@lollisoft.de wrote: > >>>>> Hi, > >>>>> > >>>>> I am using unixodbc in the latest version and psqlodbc 07.03.0200. > >>>>> Having many queries working > >>>>> good in my application one gives me this error, when updating a row. > >>>> The version 7.3.0200 is pretty old. > >>>> Please try the 8.2.xxxx version. > >>>> > >>> I need cursor functionality like first,back,next,last. Does the latest > >>> driver support it ? > >> Yes at least on Windows. > > > > Currently I have figured out, that my SQL_ADD statement fails > > (SQLSetPos). > > It seems, that I cannot insert new rows, because of foreign key > > constraints. > > > > Removing the NOT NULL clauses of that columns didn't helped. > > > > With PGAdmin I was able to test inserting such a row. That worked. > > > > Any ideas ? > > How do you setting the foreign key column using PGadmin ? > > > If the bound columns having the wrong value - such as a non exsisting > > foreign key - > > how must it be filled to indicate a NULL value ? > > > > I bind such a column that way: > > > > buffer = malloc((ColumnSize+1)*rows); > > memset(buffer, 0, (ColumnSize+1)*rows); > > ret = SQLBindCol(hstmt, column, SQL_C_CHAR, buffer, (ColumnSize+1), > > &cbBufferLength); > > > > Is that wrong ? > > I'm afraid I'm misunderstanding your point. > Please set SQL_NULL_DATA to cbBufferLength to indicate the column is NULL. > Also set SQL_COLUMN_IGNORE to cbBufferLength to ignore the column. > Hi, I have added functions to set a column to be NULL and therefore, if I add a new row, my update function rebinds the relevant column as follows, before the update: cbBufferLength = SQL_NULL_DATA+SQL_COLUMN_IGNORE; ret = SQLBindCol(hstmt, _column, DataType, buffer, 0, &cbBufferLength); The information I got before this (SQLDescribeCol) returns Nullable = 1 and Datatype = 4. So it should work. But it haven't helped until I change the SQLSetPos line as follows: from retcode = SQLSetPos(hstmt, 2, SQL_ADD, SQL_LOCK_NO_CHANGE); to retcode = SQLSetPos(hstmt, 1, SQL_ADD, SQL_LOCK_NO_CHANGE); It seems that I haven't correctly bound my columns. The second column (2) came from the documentation, that uses a special adding row. Thus, if I only have one row for my data to be shown, the second row was for adding. But I only have provided one cbBufferSize variable, not an array of two. This was the cause, why my first try to only set SQL_NULL_DATA and SQL_COLUMN_IGNORE has been failed. Is that correct ? For the issue of adding a new row, the bug may be found. One bug is remaining and it belongs to the same table and it may be clear to me, what the cause is. But I am still wondering why my error handling code crashes :-( Do you see any mistake ? Here it is: void lbQuery::dbError(char* lp, HSTMT hstmt) { SQLCHAR SqlState[6], SQLStmt[100], Msg[SQL_MAX_MESSAGE_LENGTH]; SQLINTEGER NativeError; SQLSMALLINT i, MsgLen; SQLRETURN rc; i = 1; while ((rc = SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, i, SqlState, &NativeError, Msg, sizeof(Msg), &MsgLen)) != SQL_NO_DATA) { cout << "Error in lbQuery: (" << lp << ") " << SqlState << ": " << (int) NativeError << " - " << Msg << endl; i++; } } Thanks, Lothar > regards, > Hiroshi Inoue > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
Still SQLSetPos problem even with minimal sample, SQL_NULL_DATA and SQL_COLUMN_IGNORE ?
From
"lothar.behrens@lollisoft.de"
Date:
Hi, I have implemented setting SQL_COLUMN_IGNORE, SQL_NULL_DATA and have added functions to indicate this state. I have made a 'small' sample application to demonstrate this, but I still use my ODBC wrapper classes in that sample application. You may get access to the full code. Ask for it! The first insert is with valid foreign keys and no column set to NULL. This insert works. My next insert has set the columns to NULL, but it inserts the values of the last insert. SetNull("column XYZ"); does not work, even I check before and after the update command, that the columns are still set to NULL. This is as expected. If I set illegal foreign keys and also have set NULL column, I get the following error: lbDB.cpp, 3417: Error in lbQuery: (SQLSetPos()) HY000: 7 - [unixODBC]Error while executing the query; FEHLER: Einf�gen oder Aktualisieren in Tabelle �user_anwendungen� verletzt Fremdschl�ssel-Constraint �cst_user_anwendungen_anwendungenid� Currently I have made an array of long (cbBufferLength[2]) and set this explicitely short before the call to SQLSetPos: if (mode == 1) // 1 means adding cbBufferLength[1] = SQL_NULL_DATA+SQL_COLUMN_IGNORE; else cbBufferLength[0] = SQL_NULL_DATA+SQL_COLUMN_IGNORE; SQLRETURN ret; ret = SQLDescribeCol( hstmt, _column, ColumnName, BufferLength, &NameLength, &DataType, &ColumnSize, &DecimalDigits, &Nullable); _isNullable = Nullable == 1; if (ret != SQL_SUCCESS) { printf("Error: Failed to get column description for column %d.\n", _column); query->dbError("SQLDescribeCol()", hstmt); } ret = SQLBindCol(hstmt, _column, DataType, buffer, 0, cbBufferLength); // ... lbErrCodes LB_STDCALL lbQuery::update() { lbErrCodes err = ERR_NONE; if (boundColumns != NULL) { boundColumns->indicateNullValues(); // calls the above code per NULL column. boundColumns->unbindReadonlyColumns(); // Unbinds readonly columns if ((mode == 1) && (!boundColumns->hasValidData())) { // either set valid with setNull() or setString("...") per column. _CL_LOG << "Error: Query has not got valid data to be added." LOG_ mode = 0; return ERR_DB_UPDATEFAILED; } } if (mode == 1) { retcode = SQLSetPos(hstmt, 2, SQL_ADD, SQL_LOCK_NO_CHANGE); if (retcode != SQL_SUCCESS) { dbError("SQLSetPos()", hstmt); _LOG << "lbQuery::update(...) adding failed." LOG_ // ... The functions are definitely called and cbBufferLength[1] is set to -7 (SQL_NULL_DATA+SQL_COLUMN_IGNORE) I have no idea what could be wrong. :-( Hope to get a light :-) Thanks, Lothar
lothar.behrens@lollisoft.de wrote: > Hi, > > I have implemented setting SQL_COLUMN_IGNORE, SQL_NULL_DATA and have > added functions > to indicate this state. > > I have made a 'small' sample application to demonstrate this, but I > still use my ODBC wrapper > classes in that sample application. You may get access to the full > code. Ask for it! > > The first insert is with valid foreign keys and no column set to NULL. > This insert works. > My next insert has set the columns to NULL, but it inserts the values > of the last insert. <snip> > Currently I have made an array of long (cbBufferLength[2]) and set this > explicitely short before > the call to SQLSetPos: > > if (mode == 1) // 1 means adding > cbBufferLength[1] = SQL_NULL_DATA+SQL_COLUMN_IGNORE; > else > cbBufferLength[0] = SQL_NULL_DATA+SQL_COLUMN_IGNORE; At first glance, adding SQL_NULL_DATA(=-1) and SQL_COLUMN_IGNORE(=-6) seems to have no meaning. If you really want to set to NULL on SQL_ADD, please set SQL_NULL_DATA. If you want to set DEFAULT on SQL_ADD, please set SQL_COLUMN_IGNORE. regards, Hiroshi Inoue
Re: Still SQLSetPos problem even with minimal sample, SQL_NULL_DATA
From
"lothar.behrens@lollisoft.de"
Date:
Hiroshi Inoue schrieb: > lothar.behrens@lollisoft.de wrote: > > Hi, > > > > I have implemented setting SQL_COLUMN_IGNORE, SQL_NULL_DATA and have > > added functions > > to indicate this state. > > > > I have made a 'small' sample application to demonstrate this, but I > > still use my ODBC wrapper > > classes in that sample application. You may get access to the full > > code. Ask for it! > > > > The first insert is with valid foreign keys and no column set to NULL. > > This insert works. > > My next insert has set the columns to NULL, but it inserts the values > > of the last insert. > > <snip> > > > Currently I have made an array of long (cbBufferLength[2]) and set this > > explicitely short before > > the call to SQLSetPos: > > > > if (mode == 1) // 1 means adding > > cbBufferLength[1] = SQL_NULL_DATA+SQL_COLUMN_IGNORE; > > else > > cbBufferLength[0] = SQL_NULL_DATA+SQL_COLUMN_IGNORE; > > At first glance, adding SQL_NULL_DATA(=-1) and SQL_COLUMN_IGNORE(=-6) > seems to have no meaning. > If you really want to set to NULL on SQL_ADD, please set SQL_NULL_DATA. > If you want to set DEFAULT on SQL_ADD, please set SQL_COLUMN_IGNORE. > Ahh, I misunderstood you. I will try that today. regards, Lothar > regards, > Hiroshi Inoue > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq