How do you set a blob value NULL in a sql::arguments object???

[TheSmitchell]

Hey all.

Does the SQL::Arguments object work with setting a blob field to NULL? I tried & tried & tried earlier to do something like this:

dim args as sql::arguments
args.setNull('SOME_BLOB_FILED','B')

BUUUUUT, I kept getting a data returned by database not recognized error --- I got the same error regardless of whether I'd set portableSqlEnabled to '.t.' or '.f.'. I was inserting a new record into a MySQL database. My DB defaults the field to NULL if you don't specify anything, so I was able to work around this by just not inserting that particular field if I had no data to send. HOWEVER, I would like to know how you would create an entry in a sql::arguments object that was a BLOB type and was set to NULL. I did a lot of digging around on the wiki & forums for the last 2 days on this topic with not much success.

Thanks-a-bunches!
---
Sarah

[frankbicknell]

Re: How do you set a blob value NULL in a sql::arguments object???

The way I do it is call a procedure and set it to null there.

[mvaughn]

Re: How do you set a blob value NULL in a sql::arguments object???

Hi Sarah,
Have you tried setting the table field to blob with a NULL default value?
That way you never need to set it to null.

One of my favorite most overlooked tricks.

[TheSmitchell]

Re: How do you set a blob value NULL in a sql::arguments object???

Hi Sarah,
Have you tried setting the table field to blob with a NULL default value?
That way you never need to set it to null.

One of my favorite most overlooked tricks.

The default for the blob field in the database is NULL.

No, I want to know if a sql::argument of type blob can be set to NULL --- and actually have it work. It currently appears that Alpha's sql::arguments cannot be used to set a blob to NULL.

[christappan]

Re: How do you set a blob value NULL in a sql::arguments object???

First of all, the example you gave should have had double quotes. I'm assuming you did that in XBasic and just put single quotes here. Try:

args.set("MyBlobField","",OutputArgument,.t.)

[Peter.Greulich]

Re: How do you set a blob value NULL in a sql::arguments object???

Alpha's arguments choke on nulls as well as large field content, etc. I use a tedious work-around which really slows things down if you are dealing with lots of records.

If typeof(vFIRST_NAME) = "Z"
    args.SetNull("FIRST_NAME","C")
Else
    args.set("FIRST_NAME",vFIRST_NAME)
End if

[TheSmitchell]

Re: How do you set a blob value NULL in a sql::arguments object???

First of all, the example you gave should have had double quotes. I'm assuming you did that in XBasic and just put single quotes here. Try:

args.set("MyBlobField","",OutputArgument,.t.)

Nah. My actual code had double quotes, otherwise it issues errors.

[Jim Coltz]

Re: How do you set a blob value NULL in a sql::arguments object???

Perhaps this release note from the bugs section of the current release of V12 is relevant:

AlphaDAO - MySQL - Longbinary Fields - Fixes an issue with inserting null blobs into longbinary fields.

Perhaps this bug was in V11 as well

[Peter.Greulich]

Re: How do you set a blob value NULL in a sql::arguments object???

Perhaps this release note from the bugs section of the current release of V12 is relevant:
Perhaps this bug was in V11 as well

I've encountered the null-argument bug in text, longtext, varchar & int fields in more recent builds than per the release notes.

[lvasic]

Re: How do you set a blob value NULL in a sql::arguments object???

Thanks Peter. This was exactly what I was looking for.