Re: 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(dot)behrens(at)lollisoft(dot)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(dot)behrens(at)lollisoft(dot)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(dot)behrens(at)lollisoft(dot)de wrote:
> Hiroshi Inoue schrieb:
>
>> lothar(dot)behrens(at)lollisoft(dot)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(dot)behrens(at)lollisoft(dot)de wrote:
> > Hiroshi Inoue schrieb:
> >
> >> lothar(dot)behrens(at)lollisoft(dot)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: Einfgen oder Aktualisieren in Tabelle user_anwendungen
verletzt Fremdschlssel-Constraint cst_user_anwendungen_userid#
szSqlState = 'HY000',len=170, szError='Error while
executing the query;
FEHLER: Einfgen oder Aktualisieren in Tabelle user_anwendungen
verletzt Fremdschlssel-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(dot)behrens(at)lollisoft(dot)de wrote:
> Hiroshi Inoue schrieb:
>
>> lothar(dot)behrens(at)lollisoft(dot)de wrote:
>>> Hiroshi Inoue schrieb:
>>>
>>>> lothar(dot)behrens(at)lollisoft(dot)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(dot)behrens(at)lollisoft(dot)de wrote:
> > Hiroshi Inoue schrieb:
> >
> >> lothar(dot)behrens(at)lollisoft(dot)de wrote:
> >>> Hiroshi Inoue schrieb:
> >>>
> >>>> lothar(dot)behrens(at)lollisoft(dot)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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

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: Einfgen oder Aktualisieren in Tabelle user_anwendungen
verletzt Fremdschlssel-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(dot)behrens(at)lollisoft(dot)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

Hiroshi Inoue schrieb:

> lothar(dot)behrens(at)lollisoft(dot)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