Re: Bound variables produce different result to fetching field by fiel

On Jun 23, 5:45 pm, Pak-Ming Cheung [MSFT]
<PakMingCheungM...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
HiPeter,

Could you come up with a simple program to reproduce the problem you
encountered? Please also gives us your sample database (it is better to give
us a small database, say 1 or 2 columns with 1 or 2 rows, if the problem can
be reproduced). Also, what is your OS version (Vista 64-bit?)?

We are interested in whether this is a bug inside our ODBC driver.

Thanks a lot,
Ming.



"PeterNolan" wrote:
Hi All,
we have come across the strangest problem and wondered if anyone else
has seen this.

We have written an ETL Product and when reading tables we generally
fetch the row and then fetch each field from the row. However, in
tables with a large number of rows this slows down a bit.

So we wrote a piece of code to prepare the select and then bind the
output variables for the select. We called it 'turboread' because
straight unloading goes about 25% faster.

However, when we have a zero length character string that is NOT null
in SQL Server the fetch field by field reports the field correctly as
a zero length character string that is not null....but the bound
version reports that the field is NULL. We can't fix this problem
because by the time the field gets to us it is a zero length character
string which is the representation a field has if it is NULL.

We also see a strange occurrence where sometimes the bound version
reports a field that contains data as being NULL. We can take care of
this merely by testing the string length and if the string is longer
than 0 bytes we turn off the null indicator.

We are using SQL Server 2005 with the latest ODBC drivers on 64 bit
opterons...

We were just wondering if anyone else has seen this kind of behavior
before. We think it is really strange and we felt that ODBC is way
mature enough not to be doing anything flaky like this....

All advice and guidance very, very welcome!!

Best Regards

Peter
www.peternolan.com- Hide quoted text -

- Show quoted text -

Hi Ming,
I can give you the load module as well as cut/paste of the source code
that it is using.....it happens on any SQL Server table we have tried
on here.....my personal email is peter@xxxxxxxxxxxxxxx If you email me
an address to mail to I will mail you all that you will need.

The source code for the calling program is here...it is CTLU001.

http://www.instantbi.com/SeETL/LinkClick.aspx?link=SeETLUtilitiesSourceCode.zip&tabid=59&mid=443

The executables for all the code including CTLU001 is here.

http://www.instantbi.com/SeETL/LinkClick.aspx?link=SeETL+RT+3.0.00+Beta+C.zip&tabid=59&mid=444

There are many files to set up a database here...but any database will
do.

http://www.instantbi.com/SeETL/LinkClick.aspx?link=SeETLDemoFiles.zip&tabid=59&mid=443

The manual on the product is here:

http://www.instantbi.com/SeETL/LinkClick.aspx?link=SeETL+User+Guide.pdf&tabid=59&mid=442

A sample command to run CTLU001 is as follows:
CTLU001.exe DBConnectionInParameter=DSN=XXX_DWH003_DEV
InCatalogName=XXX_DWH003_DEV InSchemaName=XXX InTableName=%1
DataMovementOption=UnLoad WorkFileName=D:\XXX\data\%2.dat
ErrorMessageOutput=cerr DebugLevel=0 TurboRead=Yes
UseOrderByClause=Yes OrderByClause=1

You just need CTLU001.exe visible in the path and the DSN defined via
ODBC DSN Manager. I suggest you use windows authentication on the DSN
rather than supply userid/password.

The TurboRead option set to Yes will execute the code to perform the
prepared select. When set to No it will fetch each individual field.

We also have a process to compare files CTLU007 and it was this
process that caught the null indicator because CTLU007 is sensitive to
fields changing to and from NULL.

Again, if you send me an email to peter@xxxxxxxxxxxxxx I can reply
with a pretty good slab of source code.....but not all of it.....I am
sure you understand...

If needs be I can write a separate program to do this but it takes a
while......

Thanks for taking an interest....we see the turbo read running about
25% faster so we'd like to use it if we can get all the null flags set
correctly.

Best Regards

Peter


.

Relevant Pages

  • Re: Native exception 0xc0000005 when using SQLCE and .NET CF 2.0?!
    ... performance and reliability of SQLCE on Windows CE 5.0 (source code ... doesn't matter whether the database is empty or not when the test is ... // Database connection string ... // Create SQL engine object ...
    (microsoft.public.sqlserver.ce)
  • Re: numbers with regional settings
    ... > i fetch a value from database which is a number field in the database ... > After fetching the values fromt he data base and once the format is ... Are you saving it as a string or as a number? ...
    (microsoft.public.vb.general.discussion)
  • Re: read string from database and divide result into cells?
    ... that will work if my string is in a cell... ... I would like i VBA to fetch a string result from Database and split the ...
    (microsoft.public.excel.programming)
  • Re: A little Rolodex [revised]
    ... including alpha sort and searching for any embedded string, ... In this application, a database is a directory, ... you may optionally provide any alternate UCASE program, ... NN -> first store NN as key length ...
    (comp.sys.hp48)
  • Re: return multiple rows from sql statement
    ... strings from input values is almost certainly a safe path to SQL ... Also, being a MySQL function, it knows what MySQL needs or uses. ... All characters that are entered in the fields make their way into the database unaltered. ... The insert of what surprisinlgly was NOT a syntax error, but a string called "mysql_insert_id" into an integer field resulted in the value zero being put in. ...
    (comp.lang.php)