Thread: select error with null string -- error code -209

I am using following codes to get the accountname, and account from the
Table Account;
accountname, and account are varchar(30) and it can be null.
The code has no problem when both accountname, and account are not
null.  It gives me an errror code of -209 when the account is null.
Anyone has any idea of fixing it.

the output looks like :
error in fetch -209

Thanks,


  EXEC SQL BEGIN DECLARE SECTION;
  char query_string[256];
  char name[30];
  char account[30];
   EXEC SQL END DECLARE SECTION;



  EXEC SQL CONNECT TO :dbname user :userid/:passwd;
  if(sqlca.sqlcode) {
    printf("error in connect\n");
    return FALSE;
  }
  sprintf(query_string, "SELECT accountname, account FROM Account where
AccountID = %d", customerId);

  EXEC SQL PREPARE sql_command FROM :query_string;
  EXEC SQL DECLARE c_cursor2 CURSOR FOR sql_command;/* DECLARE a cursor
*/
  EXEC SQL OPEN c_cursor2;
  if(sqlca.sqlcode) {
    printf("error in cursor\n");
    return FALSE;
  }
  EXEC SQL FETCH c_cursor2 INTO :name, :account;
  if(sqlca.sqlcode) {
    printf("error in fetch %d\n", sqlca.sqlcode);
    return FALSE;
  }

Have a look at the COALESCE() function. You may want to wirte your
query as:

SELECT accountname, COALESCE(account, '') FROM
Account WHERE AccountID = %d

Hope that helps,

Mike Mascari
mascarm@mascari.com

-----Original Message-----
From:    HYip [SMTP:hualinyip@earthlink.net]
Sent:    Friday, May 04, 2001 11:48 AM
To:    pgsql-general@postgresql.org
Subject:    [GENERAL] select error with null string  -- error code -209

I am using following codes to get the accountname, and account from
the
Table Account;
accountname, and account are varchar(30) and it can be null.
The code has no problem when both accountname, and account are not
null.  It gives me an errror code of -209 when the account is null.
Anyone has any idea of fixing it.

the output looks like :
error in fetch -209

Thanks,


  EXEC SQL BEGIN DECLARE SECTION;
  char query_string[256];
  char name[30];
  char account[30];
   EXEC SQL END DECLARE SECTION;



  EXEC SQL CONNECT TO :dbname user :userid/:passwd;
  if(sqlca.sqlcode) {
    printf("error in connect\n");
    return FALSE;
  }
  sprintf(query_string, "SELECT accountname, account FROM Account
where
AccountID = %d", customerId);

  EXEC SQL PREPARE sql_command FROM :query_string;
  EXEC SQL DECLARE c_cursor2 CURSOR FOR sql_command;/* DECLARE a
cursor
*/
  EXEC SQL OPEN c_cursor2;
  if(sqlca.sqlcode) {
    printf("error in cursor\n");
    return FALSE;
  }
  EXEC SQL FETCH c_cursor2 INTO :name, :account;
  if(sqlca.sqlcode) {
    printf("error in fetch %d\n", sqlca.sqlcode);
    return FALSE;
  }



---------------------------(end of
broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

HYip <hualinyip@earthlink.net> writes:
> The code has no problem when both accountname, and account are not
> null.  It gives me an errror code of -209 when the account is null.
> Anyone has any idea of fixing it.

(1) Try printing sqlca.sqlerrm.sqlerrmc, not sqlca.sqlcode, if you
don't want to look up error codes.

(2) You need an indicator variable if you want to fetch NULLs.
Else how will you know that the value is NULL?

            regards, tom lane