Writing empty string to SQL Server table using Db-Library

From: Uday Eyunni (post2_at_newsgroup.com)
Date: 06/21/04 

Date: Mon, 21 Jun 2004 11:48:47 -0700

Hello,

I am experiencing problems in writing empty strings (initialized to "") to
SQL Server (2000) using Db-Library. The following example illustrates what I
am trying to do.

Let's say there are three varchar fields in a table. When the data is
populated to the table only one of the columns will have the data.

Binding of the variables -

DBCHAR val1[11];
DBCHAR val2[15];
DBCHAR val3[13];

   if(bcp_bind(dbproc, (LPCBYTE) val1, 0, -1, (LPCBYTE) "", 1, 0, 1) ==
FAIL)
    iDbBindCols = false;

   if(bcp_bind(dbproc, (LPCBYTE) val2, 0, -1, (LPCBYTE) "", 1, 0, 2) ==
FAIL)
    iDbBindCols = false;

   if(bcp_bind(dbproc, (LPCBYTE) val3, 0, -1, (LPCBYTE) "", 1, 0, 3) ==
FAIL)
    iDbBindCols = false;

    // Fill the variables
    ...

   if (bcp_sendrow(dbproc) == FAIL)
      sendError = true;

   In the above example, if val1 and val2 are initialized to "" and val3
contains the text "John", after calling bcp_sendrow the database table
contains NULL for val1, val2 fields and John for val3 field when you execute
the query from the Query Analyzer.

The table schema doesn't allow NULLs for the columns. So, I am guessing that
columns that hold val1 and val2 might contain '\0'. Also, I can't open the
table using the Enterprise Manager (Open Table -> Return all rows) after
populating the table. The error message "Runtime Error: Data provider or
other service returned an E_FAIL status." is displayed from the enterprise
manager.

Any clues on how to bind the variables to write empty strings to the
database table?

Thanks

Relevant Pages

  • Re: Microsoft SQL-DMO (ODBC SQLState: HY000) error when opening any ob
    ... I didn't encounter any difficulty using Query Analyzer. ... But this error message does pop up everytime I open an object in the ... database on the hosting company's server, when using SQL Enterprise Manager. ... Otherwise try asking the web host about it. ...
    (microsoft.public.sqlserver.server)
  • Re: SA can not create users
    ... If it works correctly from Query Analyzer, ... likely Enterprise Manager, but you don't say exactly what happens - is ... there an error message? ... client tools completely. ...
    (comp.databases.ms-sqlserver)
  • Writing an empty string to SQL Server using Db-Library
    ... I am experiencing problems in writing empty strings to ... the query from the Query Analyzer. ... table using the Enterprise Manager after ... The error message "Runtime Error: ...
    (microsoft.public.sqlserver.clients)
  • Re: adp connection to msde and exclusive rights
    ... I'd recommend not using Access as a development tool for SQL Server ... Use the Query Analyzer or Enterprise Manager instead. ... error message you're getting is an Access error, ...
    (microsoft.public.sqlserver.msde)
  • Re: Array Question
    ... It is a warning message, not an error message. ... How do I know which format is throwing ... Then ones that correspond to the empty strings. ...
    (comp.lang.perl.misc)