Обсуждение: SQLSetPos problem ?

Поиск

Список

Период

Сортировка

SQLSetPos problem ?

От

"lothar.behrens@lollisoft.de"

Дата:

10 декабря 2006 г., 10:58:54

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

Re: SQLSetPos problem ?

От

Hiroshi Inoue

Дата:

10 декабря 2006 г., 23:57:13

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

Re: SQLSetPos problem ?

От

"lothar.behrens@lollisoft.de"

Дата:

11 декабря 2006 г., 13:41:17

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

Re: SQLSetPos problem ?

От

Hiroshi Inoue

Дата:

11 декабря 2006 г., 23:04:03

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

Re: SQLSetPos problem ?

От

"lothar.behrens@lollisoft.de"

Дата:

12 декабря 2006 г., 19:26:13

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

Re: SQLSetPos problem ?

От

Hiroshi Inoue

Дата:

12 декабря 2006 г., 23:50:19

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

Re: SQLSetPos problem ?

От

"lothar.behrens@lollisoft.de"

Дата:

16 декабря 2006 г., 22:24:00

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 ?

От

"lothar.behrens@lollisoft.de"

Дата:

18 декабря 2006 г., 21:54:07

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

Re: Still SQLSetPos problem even with minimal sample, SQL_NULL_DATA

От

Hiroshi Inoue

Дата:

18 декабря 2006 г., 22:40:42

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

От

"lothar.behrens@lollisoft.de"

Дата:

19 декабря 2006 г., 06:38:57

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

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: SQLSetPos problem ?