Problem inserting nil into DB2 blob column using vw7.8 DB2EXDI

Hi,
I have a DB2 table with some blob columns that are allowed to have null values.  The VW Database documentation says "When setting a NULL value, use nil, which is treated as a NULL value of type VARCHAR."  When I use nil, I get a db2 cli error "SQL0301N The value of the input host variable or parameter number '10' cannot be used because of its data type".  I suspect it's a blob vs varchar issue.
I would have thought the DB2EXDI parcel would have dealt with this for me but evidently not.  Should DB2EXDI be handling this or should I try putting nil in a ByteArray, which might work but I want to be sure I wind up with the expected null in the database?  I can play around with it but thought I'd post here for suggestions first.

Thanks!
Mark

Sounds like your expectations are reasonable. It may be a glitch in the
blob machinery. I'll try to look at this later today.

Thanks,
- Dave

> -----Original Message-----
> From: [hidden email] [mailto:[hidden email]] On
Behalf
> Of MarkPetersen
> Sent: Tuesday, November 02, 2010 7:12 AM
> To: [hidden email]
> Subject: [vwnc] Problem inserting nil into DB2 blob column using
> vw7.8DB2EXDI
>
>
> Hi,
> I have a DB2 table with some blob columns that are allowed to have
null
> values.  The VW Database documentation says "When setting a NULL
value,
> use
> nil, which is treated as a NULL value of type VARCHAR."  When I use
nil, I
> get a db2 cli error "SQL0301N The value of the input host variable or
> parameter number '10' cannot be used because of its data type".  I
suspect
> it's a blob vs varchar issue.
> I would have thought the DB2EXDI parcel would have dealt with this for
me
> but evidently not.  Should DB2EXDI be handling this or should I try
> putting
> nil in a ByteArray, which might work but I want to be sure I wind up
with
> the expected null in the database?  I can play around with it but
thought
> I'd post here for suggestions first.
>
> Thanks!
> Mark
> --
> View this message in context:
http://forum.world.st/Problem-inserting-nil-
> into-DB2-blob-column-using-vw7-8-DB2EXDI-tp3023755p3023755.html
> Sent from the VisualWorks mailing list archive at Nabble.com.
> _______________________________________________
> vwnc mailing list
> [hidden email]
> http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

Thanks Dave.
Let me know if you need anything from me.  BTW, we're using the 7.8 DB2EXDI parcel in VW 7.7.1 so that we can pick up the additional platform support (e.g. aix) from the 7.8 parcel.

Mark
mpeterse@us.ibm.com

Hi Mark,

I was able to reproduce the problem using the code snippet below, and
have filed AR 64197 Error binding nil to DB2Buffer for BLOB column.
There is a workaround, at least. If you un-comment the bindTemplate
line, the row will be inserted (at least on my machine). The problem
revolves around the fact that we currently guess the target column type
based on the bound input. For nil, we punt and claim #String.

Thanks,
- Dave

conn :=DB2Connection new username: ' ';
                                password: '';
                                environment: 'SAMPLE'.
[conn connect.
sess := conn getSession.

"CREATE TABLE hasblob (b BLOB, i integer)"

sess prepare: 'INSERT INTO hasblob VALUES( ?, ? )'.
"sess bindTemplate: #( #LargeByteArray #Integer)."
sess bindInput: (Array with: nil with: 1).
sess execute.
[(ans := sess answer) = #noMoreAnswers]
        whileFalse: [ans = #noAnswerStream
                                ifFalse: [data := [ans upToEnd] on:
Error do: [:ex| ex exit].
                                Transcript show: data printString; cr]].
] ensure: [sess disconnect.
                conn disconnect].

> -----Original Message-----
> From: [hidden email] [mailto:[hidden email]] On
Behalf support
> (e.g. aix) from the 7.8 parcel.
>
> Mark
> [hidden email]
> --
> View this message in context:
http://forum.world.st/Problem-inserting-nil-
> into-DB2-blob-column-using-vw7-8-DB2EXDI-tp3023755p3023851.html
> Sent from the VisualWorks mailing list archive at Nabble.com.
> _______________________________________________
> vwnc mailing list
> [hidden email]
> http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

_______________________________________________
vwnc mailing list
[hidden email]
http://lists.cs.uiuc.edu/mailman/listinfo/vwnc

Thanks Dave.  
Yes that works if I build the template.  Since we use the same insert method for all tables, I added the following code fragment to build the template:

        self select: 'select colname, typename, length from syscat.columns where tabname=', aTable asUppercase printString , ' order by colno'.
        typeArray := (self resultColumns at: 2) data.
        bindTemplateArray := List new.
        typeArray do:
                        [:each |
                        each = 'BLOB' ifTrue: [bindTemplateArray add: #LargeByteArray].
                        each = 'CLOB' ifTrue: [bindTemplateArray add: #String].
                        (each findString: 'CHAR' startingAt: 1) > 0
                                ifTrue: [bindTemplateArray add: #String].
                        each = 'DATE' ifTrue: [bindTemplateArray add: #Date].
                        each = 'TIME' ifTrue: [bindTemplateArray add: #Time].
                        (each = 'INTEGER' or: [each = 'SMALLINT'])
                                ifTrue: [bindTemplateArray add: #Integer].
                        (each = 'DOUBLE' or: [each = 'FLOAT'])
                                ifTrue: [bindTemplateArray add: #Double].
                        each = 'REAL' ifTrue: [bindTemplateArray add: #Float].
                        each = 'TIMESTAMP' ifTrue: [bindTemplateArray add: #Timestamp].
                        each = 'DECIMAL' ifTrue: [bindTemplateArray add: #FixedPoint]].

This covers all the data types we use at least, but I check size of bindTemplateArray to make sure we don't get anything unexpected.

Thanks again.
Mark
mpeterse@us.ibm.com