Hi

I have a blind spot here!

In this SQL, ConfigValue is a BLOB and I'm trying to put the string
value 'OFF' into it.

INSERT INTO Config (ConfigKey,ComputerName,UserName,ConfigValue)
VALUES('Maintenance Mode', '', 'Jeff Cook', CAST('OFF' AS BLOB))

.... and I get this error:-

ElevateDB Error #1011 An error occurred with the value OFF (A conversion
error occurred with the value OFF)

I've tried it without the CAST:-

INSERT INTO Config (ConfigKey,ComputerName,UserName,ConfigValue)
VALUES('Maintenance Mode', '', 'Jeff Cook', 'OFF')

.... with a different error:-

ElevateDB Error #700 An error was found in the statement at line 2 and
column 45 (Expected NULL expression but instead found 'OFF')

This must be simple but I can't see it!

Cheers

Jeff

--------------
CREATE TABLE "Config"
(
"ConfigKey" VARCHAR(60) COLLATE "UNI_CI",
"ComputerName" VARCHAR(40) COLLATE "UNI_CI",
"UserName" VARCHAR(40) COLLATE "UNI_CI",
"ConfigValue" BLOB,
CONSTRAINT "PrimaryKey" PRIMARY KEY ("ConfigKey", "ComputerName",
"UserName")
)

On 2/11/2015 5:42 PM, Jeff Cook wrote:
> I have a blind spot here!
>
> In this SQL, ConfigValue is a BLOB and I'm trying to put the string
> value 'OFF' into it.
>
> INSERT INTO Config (ConfigKey,ComputerName,UserName,ConfigValue)
> VALUES('Maintenance Mode', '', 'Jeff Cook', CAST('OFF' AS BLOB))
>
> ... and I get this error:-
>
> ElevateDB Error #1011 An error occurred with the value OFF (A conversion
> error occurred with the value OFF)
>

You're not the only one - looks ok to me but i'm seeing the same error.

I did get it to work by double-casting but that should not be needed

INSERT INTO Config (ConfigKey,ComputerName,UserName,ConfigValue)
VALUES('Maintenance Mode', '', 'Jeff Cook', CAST( CAST('OFF' AS CLOB) AS
BLOB) )

Raul

On 12/02/2015 3:17 p.m., Raul wrote:
> On 2/11/2015 5:42 PM, Jeff Cook wrote:
>> I have a blind spot here!
>
> You're not the only one - looks ok to me but i'm seeing the same error.

That's a bit of a relief - I don't feel as silly
>
> I did get it to work by double-casting but that should not be needed
>
> INSERT INTO Config (ConfigKey,ComputerName,UserName,ConfigValue)
> VALUES('Maintenance Mode', '', 'Jeff Cook', CAST( CAST('OFF' AS CLOB) AS
> BLOB) )

OK that's an acceptable solution and works for me.  It seem to be rather
odd to have to do it that way, but it is not like it is common thing to
want to do.

Thanks, Raul

Cheers

Jeff

Jeff

I know Raul's given you a solution but I became a bit curious about this and started browsing the newsgroups. I think the basic problem you have is that BLOBs expect binary data and you're trying to feed it straight ascii. If you wanted to convert OFF into a binary string then you could store it by using the X syntax (X'binarystring').

If you want to store straight text you'll be far better off using a CLOB rather than a BLOB or using binary strings to replace your text.

Back in the DBISAM days the two types weren't really differentiated, now they are. I used to store everything in a MEMO field - great no need to think.

I like Raul's fix - its interesting that you can CAST a CLOB to a BLOB but not a VARCHAR which (to my simple brain) is just a type of CLOB anyway

Roy

On 13/02/2015 12:41 a.m., Roy Lambert wrote:

<snip>

>
> I know Raul's given you a solution but I became a bit curious about this and started browsing the newsgroups. I think the basic problem you have is that BLOBs expect binary data and you're trying to feed it straight ascii. If you wanted to convert OFF into a binary string then you could store it by using the X syntax (X'binarystring').
>
> If you want to store straight text you'll be far better off using a CLOB rather than a BLOB or using binary strings to replace your text.
>
> Back in the DBISAM days the two types weren't really differentiated, now they are. I used to store everything in a MEMO field - great no need to think.

Yes, the application is the result of 15+ years of labour using Paradox,
DBISAM and now EDB.

The table concerned probably originated in DBISAM days.

It is a replacement for individual and system preferences that formerly
lived in the registry.

The BLOB field is convenient because it allows me to store "anything"
and allows me to be lazy and not create special tables for stuff.

e.g. I might want to store a users JPEG email signature - just chuck it
in my Config table with a ConfigKey of "EmailSignature" and the username
as the key.

Cheers

Jeff

On 2/12/2015 6:41 AM, Roy Lambert wrote:
> I think the basic problem you have is that BLOBs expect binary data and you're trying to feed it straight ascii. If you wanted to convert OFF into a binary string then you could store it by using the X syntax (X'binarystring').

Kicker is that he is not trying to store the ascii though - isn't the
whole point of the CAST to do the proper conversions from one type to
another ?

It actually looks like a bug to me - just not sure where the bug is
(CAST or somewhere else).


> I like Raul's fix - its interesting that you can CAST a CLOB to a BLOB but not a VARCHAR which (to my simple brain) is just a type of CLOB anyway

Agreed - one more reason to think of this as a bug.

if Jeff reads them i wonder whether he reported this to Tim and if not
we should

Raul

Jeff

>The BLOB field is convenient because it allows me to store "anything"
>and allows me to be lazy and not create special tables for stuff.
>
>e.g. I might want to store a users JPEG email signature - just chuck it
>in my Config table with a ConfigKey of "EmailSignature" and the username
>as the key.

Since I like to be able to "get" at the data I'd probably go for encoding (say nice simple uuencode) binary data which allows me to read all the other non-binary records easily (SELECT binarycolumn in EDBManager and you get the size only). Having said that I do have a few BLOBs where I know the content is always going to be binary and I can't read it anyway and about all I could do with it in EDBManager is set it to null.

My personal opinion is that differentiating the two types really adds nothing. But then I'm not a true believer.

Roy Lambert

Raul

>if Jeff reads them i wonder whether he reported this to Tim and if not
>we should

He does seem to be reading them so lets give him the pleasure

Roy Lambert

On 13/02/2015 8:45 p.m., Roy Lambert wrote:
> Raul
>
>> if Jeff reads them i wonder whether he reported this to Tim and if not
>> we should
>
> He does seem to be reading them so lets give him the pleasure
>
> Roy Lambert
>
Dunnit

Below:  Tim's response and mine to his:-

On 14/02/2015 4:34 a.m., Tim Young wrote:
> Jeff,

<snip>

> No, it's not a bug in CAST.  You can only cast binary strings to
BLOB/BYTE/VARBYTES types, and by binary strings I mean hex-encoded strings.
>
> If you want to store strings in a BLOB, then you should use a CLOB,
not a BLOB.
>
> Tim Young
> Elevate Software
> www.elevatesoft.com

Hi Tim

Thanks for the advice.

The only reason this came up is that I'm making more and more use of SQL
rather than navigational methods.  (In the hope that I'm unloading more
work onto the server and reducing network traffic in a c/s only
environment.)

In this case SQL and Delphi seem to be at odds.  Your implementation of
CAST, which I'm sure is by the book, precludes casting a string to a
BLOB whereas in Delphi I can happily say:-

MyTableMyBLOBField.AsString := 'OFF';

Nevertheless, Raul's double CAST seems to solve this rare problem
without the upheaval of a database change in a table referenced in
hundreds of modules.

Thanks again, Tim

Cheers

Jeff