How to write NULL to a Sql table

[nageswaragunupudi]

Rao

YES ..

#xtranslate NULL => VTWrapper( 1, nil )

Was the answer !!

Thanks
Rick Lipkin

I have checked with the latest win32ole.prg. There is no change. If we try to assign nil, where NULL is to be assigned to a parameter, Oracle complains as wrong data type. When NULL is read it is returned as NIL but when NIL is written, it is not passed on as NULL, the way acceptable by Oracle/MSSql.

Interstingly Empty dates ( CToD('') ) are passed on as NULLs successfully, but not NIL as NULL.
relevant portions of source from win32ole.prg

Code: Select all  Expand view

        case HB_IT_NIL:
          //pVariant->n1.n2.vt = VT_EMPTY;
          break;
         .......
        case HB_IT_DATE:
          if( pItem->item.asDate.value == 0 )
          {
             pVariant->n1.n2.vt = VT_NULL;
          }
 

Probably replacing "//pVariant->n1.n2.vt = VT_EMPTY;" with "pVariant->n1.n2.vt = VT_NULL;" may give the desired results.
Instead of tinkering with the source code of win32ole.prg, I found out the above work around of using VTWrapper( 1, nil ).

Wish xHarbour changes the win32ole.prg suitably.

I have not tried with Harbour.

[nageswaragunupudi]

Ideally we would like to read NULL as NIL and write NIL as NULL. If win32ole.prg can do this, our code becomes far simpler, neat and uncluttered with many if(... ) statements. For example, instead of writing
oCmd:Parameters( n ):Value := If( nVal == nil, NULL, nVal )
we can write
oCmd:Parameters( n ):Value := nVal
If we test the above modification I suggested and if it works fine, without any side-effects, probably fixing the win32ole.prg and maintaining the fix over revisions may be a better solution. I may test this sometime soon and confirm. If Mr Rick likes to do some experimentation with this, that would be greatly welcome.

[Rick Lipkin]

Rao

Some time ago when I started with ADO and Sql Server .. I noticed that you could not write ctod("") to Sql .. if I recall .. it just gave an ugly run-time ..

I have checked with the latest win32ole.prg. There is no change. If we try to assign nil, where NULL is to be assigned to a parameter, Oracle complains as wrong data type. When NULL is read it is returned as NIL but when NIL is written, it is not passed on as NULL, the way acceptable by Oracle/MSSql.

Interstingly Empty dates ( CToD('') ) are passed on as NULLs successfully, but not NIL as NULL.
relevant portions of source from win32ole.prg

Code: Select all  Expand view

case HB_IT_NIL:
          //pVariant->n1.n2.vt = VT_EMPTY;
          break;
         .......
        case HB_IT_DATE:
          if( pItem->item.asDate.value == 0 )
          {
             pVariant->n1.n2.vt = VT_NULL;
          }
 

I started a thread in the xHarb NG and one of the developers of Win32Ole.prg ... did indeed fix the blank date .. however, up until this time I have not tried to write NULL to any other field.

I took on a conversion project to move data from an Oracle production application and take that subset of data and append it to MS Sql for a web application that I did not design. In order for the Web interface to look and function properly .. I could not use 'blanks' but had to pass NULL to those fields that were truly un-defined.

Hopefully one of the xHarbour developers may be following this thread and make some suggestions on a permanent fix..

Thanks
Rick Lipkin

[nageswaragunupudi]

Mr Rick

>
I started a thread in the xHarb NG and one of the developers of Win32Ole.prg ... did indeed fix the blank date .. however, up until this time I have not tried to write NULL to any other field.
>

So you were the cause of the fix for blank dates.

Let us await the right fix from the xHarbour team. Meanwhile my workaround is working for me in all cases quite well.

[Rick Lipkin]

Rao

YUP .. I won't take credit for the fix .. but I will take credit for my frustration that lead to the fix .. and happy the xHarb people were listning

Rick Lipkin

[Rick Lipkin]

Rao

FYI .. I did get a responce from xHarb ..

On Apr 24, 5:34 pm, "Ron Pinkas" <Ron.Pinkas_remove_th...@xHarbour.com> wrote:
> Rick,
>
> > xHarb Developers .. here is an extract thread from the FWH NG with a
> > suggested fix to Win32ole.prg ..
>
> As far as I remember VT_EMPTY is also commonly desired (and natural)
> conversion of NIL (IIRC f.e. a skipped argument). I don't have time to check
> so I'd urge you to check how is NULL value supported in vbScript - this may
> provide some ideas.
>
> Otherwise I added VTWrapper() support specifically to allow explicit control
> over passed arguments.
>
> Ron

Ron

Thank you !!!

[nageswaragunupudi]

>
> Otherwise I added VTWrapper() support specifically to allow explicit control
> over passed arguments.
>
That means we are in the right direction by using VTWrapper( ... )
Still I would like to test my proposed modification extensively and post here. Waiting for some free time.

[Enrico Maria Giordano]

All of this still doesn't explain why I don't get the problem...

EMG

[Rick Lipkin]

Enrico

Try your test using nvchar fields .. I inherrited this database and all I got were nvchar and datetime fields ..

Let me know if the datatype is perhaps the common denominator..

Thanks
Rick

[Enrico Maria Giordano]

If you mean nvarchar then I never used this datatype. I usually use varchar. So yes, this is a difference.

EMG

[nageswaragunupudi]

It is not only with nchar types. The problem is even with numeric fields.

I have just now tested with Oracle.

I try to assign NULL value to a column with NUMBER ( oracle ) data type.

When I write oRs:Fields( n ):Value := nil, the column is not updated with NULL as we would want. If there is already a number there ( say 99 ) it remains the same without change. If the previous value is NULL, it is updated as 0. Mr Rick may confirm MSSql's behaviour.

If we assign oRs:Fields( n ):Value := VTWrapper( 1, nil ) then a proper NULL is assigned and the column is replaced with proper NULL value.

I tried with assigning NULLs to parameters of functions and procedures.

When I write oCmd:Parameters( n ):Value := nil, the Oracle raises an error that it is wrong datatype.
But when I write oCmd:Parameters( n ):Value := VTWrapper( 1, nil ), then the oracle procedures accepts it as proper NULL and deals wth the parameter accordingly.

I have just modified the Win32ole.Prg as I suggested earlier and linked and tested.
With this change assigning NIL to field value or paramter value is working perfectly and proper NULL is being passed on.

I earnestly request my colleagues to test and suggest if they face any problems.

[Enrico Maria Giordano]

I try to assign NULL value to a column with NUMBER ( oracle ) data type.

When I write oRs:Fields( n ):Value := nil, the column is not updated with NULL as we would want. If there is already a number there ( say 99 ) it remains the same without change. If the previous value is NULL, it is updated as 0.

I just tried it using MSSQL and sorry, I don't confirm the problem. I wrote 999 and found 999. Then I wrote NIL and found NULL.

EMG

[Enrico Maria Giordano]

This is my test:

Code: Select all  Expand view

#define adOpenForwardOnly 0
#define adOpenKeyset      1
#define adOpenDynamic     2
#define adOpenStatic      3

#define adLockReadOnly        1
#define adLockPessimistic     2
#define adLockOptimistic      3
#define adLockBatchOptimistic 4


#define adUseNone   1
#define adUseServer 2
#define adUseClient 3


FUNCTION MAIN()

    LOCAL oRs

    oRs = CREATEOBJECT( "ADODB.Recordset" )

    oRs:Open( "SELECT * FROM Contatti", "Provider=SQLOLEDB;Integrated Security=SSPI;Data Source=EMAG\Emag;Initial Catalog=Ecmp", adOpenForwardOnly, adLockOptimistic )

//    oRs:Fields( "Test" ):Value = 999
    oRs:Fields( "Test" ):Value = NIL

    oRs:Update()

    oRs:Close()

    RETURN NIL

[nageswaragunupudi]

Mr Enrico

>
oRs:Fields( "Test" ):Value = NIL
>
Is the column getting updated to NULL ? or any numeric value like 0 ? What is the column type please ?
Can you clarify ? I have not tested on MSSql serve

[nageswaragunupudi]

oh you already answered in your earlier post