VALUES expression, BLOB, and using ij

[Alan M. Feldstein]

Programmatically, I was able to provide the value for a BLOB column from a binary file with the following code sequence:

PreparedStatement pstmt = connection.prepareStatement( "INSERT INTO \"ADDTestCases\" ( \"pairID\", \"expectedSum\", \"executableFile\" ) VALUES( " + Long.toString( pairID ) + ", " + Long.toString( testCase.getExpectedSum() ) + ", ? )" );
File file = testCase.getExecutable().getFile();
FileInputStream stream = new FileInputStream( file );
pstmt.setBinaryStream( 1, stream, (int)( file.length() ) );
pstmt.executeUpdate();

Alternatively, I should be able to use ij to execute SQL statements interactively or via a script in order to add a couple of rows to the database. So far, I have:

INSERT INTO "ADDTestCases" ( "pairID", "expectedSum", "executableFile" ) VALUES( 1, 0, ? );
INSERT INTO "ADDTestCases" ( "pairID", "expectedSum", "executableFile" ) VALUES( 2, 1, ? );

but I don't think ij is going to like the dynamic parameters. Furthermore, I haven't thought of a way to set up the binary stream in that environment. Is something like this going to be possible using ij?

I want to put a couple of rows into the database on a clean machine using a minimalist approach, as proof of concept for the specification database specification I'm writing here. I'll be just about done writing that Web page once I can explain the requirements for populating the executableFile column.

[Alan M. Feldstein]

Alan M. Feldstein wrote:

I should be able to use ij to execute SQL statements
interactively ... in order to add a couple of rows to the
database.

I want to put a couple of rows into the database on a clean machine
using a minimalist approach, as proof of concept for the
database specification I'm writing here (http://www.alanfeldstein.com/products/software/fss/download/test_cases_database.html).
I'll be just about done writing that Web page once I can explain the
requirements for populating the executableFile column.
 

For a minimalist approach, I tried the following two SQL statements at the ij prompt. Both failed.

ij> INSERT INTO "ADDTestCases" ( "pairID", "expectedSum", "executableFile" ) VALUES( 1, 0,
X'7f' '45' '4c' '46' '02' '02' '01' '00' '00' '00' '00' '00' '00' '00' '00' '00'
'00' '02' '00' '2b' '00' '00' '00' '01' '00' '00' '00' '01' '00' '00' '00' 'b0'
'00' '00' '00' '00' '00' '00' '00' '40' '00' '00' '00' '00' '00' '00' '03' '78'
'00' '00' '00' '00' '00' '40' '00' '38' '00' '02' '00' '40' '00' '07' '00' '06'
'00' '00' '00' '01' '00' '00' '00' '05' '00' '00' '00' '00' '00' '00' '00' 'b0'
'00' '00' '00' '01' '00' '00' '00' 'b0' '00' '00' '00' '00' '00' '00' '00' '00'
'00' '00' '00' '00' '00' '00' '00' '58' '00' '00' '00' '00' '00' '00' '00' '58'
'00' '00' '00' '00' '00' '10' '00' '00' '00' '00' '00' '01' '00' '00' '00' '07'
'00' '00' '00' '00' '00' '00' '01' '08' '00' '00' '00' '01' '00' '10' '01' '08'
'00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '18'
'00' '00' '00' '00' '00' '00' '00' '18' '00' '00' '00' '00' '00' '10' '00' '00'
'0b' '00' '00' '00' '8a' '11' '60' '01' '8b' '29' '70' '20' '09' '00' '04' '00'
'88' '11' '21' '08' '88' '11' '00' '05' 'c2' '59' '00' '00' '0b' '00' '00' '00'
'8a' '11' '60' '01' '8b' '29' '70' '20' '09' '00' '04' '00' '88' '11' '21' '10'
'88' '11' '00' '05' 'c4' '59' '00' '00' '86' '00' '40' '02' '0b' '00' '00' '00'
'8a' '11' '60' '01' '8b' '29' '70' '20' '09' '00' '04' '00' '88' '11' '21' '18'
'88' '11' '00' '05' 'c6' '71' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00'
'00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00'
'00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00'
'00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '01' '04' '00' 'ff' 'f1'
'00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00'
'00' '00' '00' '00' '03' '00' '00' '01' '00' '00' '00' '01' '00' '00' '00' 'b0'
'00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '03' '00' '00' '02'
'00' '00' '00' '01' '00' '10' '01' '08' '00' '00' '00' '00' '00' '00' '00' '00'
'00' '00' '00' '00' '03' '00' '00' '03' '00' '00' '00' '01' '00' '10' '01' '20'
'00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '03' '00' '00' '04'
'00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00'
'00' '00' '00' '00' '03' '00' '00' '05' '00' '00' '00' '00' '00' '00' '00' '00'
'00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '03' '00' '00' '06'
'00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00'
'00' '00' '00' '25' '01' '00' '00' '03' '00' '00' '00' '01' '00' '10' '01' '20'
'00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '2b' '01' '00' '00' '01'
'00' '00' '00' '01' '00' '00' '00' 'b0' '00' '00' '00' '00' '00' '00' '00' '00'
'00' '00' '00' '07' '01' '00' '00' '02' '00' '00' '00' '01' '00' '10' '01' '18'
'00' '00' '00' '00' '00' '00' '00' '08' '00' '00' '00' '0b' '01' '00' '00' '02'
'00' '00' '00' '01' '00' '10' '01' '08' '00' '00' '00' '00' '00' '00' '00' '08'
'00' '00' '00' '13' '01' '00' '00' '02' '00' '00' '00' '01' '00' '10' '01' '10'
'00' '00' '00' '00' '00' '00' '00' '08' '00' '00' '00' '1b' '11' '00' '00' '03'
'00' '00' '00' '01' '00' '10' '01' '20' '00' '00' '00' '00' '00' '00' '00' '00'
'00' '00' '00' '20' '12' '00' '00' '01' '00' '00' '00' '01' '00' '00' '00' 'b0'
'00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '1d' '00' '00' '00'
'00' '00' '00' '00' '00' '00' '00' '02' '00' '00' '00' '00' '00' '00' '00' '00'
'00' '00' '00' '00' '1d' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '03'
'00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '33' '11' '00' '00' '02'
'00' '00' '00' '01' '00' '10' '01' '20' '00' '00' '00' '00' '00' '00' '00' '00'
'00' '00' '00' '3a' '11' '00' '00' '01' '00' '00' '00' '01' '00' '00' '01' '08'
'00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '41' '11' '00' 'ff' 'f1'
'00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00'
'00' '61' '2e' '6f' '75' '74' '00' '73' '75' '6d' '00' '61' '64' '64' '65' '6e'
'64' '31' '00' '61' '64' '64' '65' '6e' '64' '32' '00' '5f' '65' '6e' '64' '00'
'6d' '61' '69' '6e' '00' '5f' '45' '4e' '44' '5f' '00' '5f' '53' '54' '41' '52'
'54' '5f' '00' '5f' '65' '64' '61' '74' '61' '00' '5f' '65' '74' '65' '78' '74'
'00' '5f' '44' '59' '4e' '41' '4d' '49' '43' '00' '00' '2e' '74' '65' '78' '74'
'00' '2e' '64' '61' '74' '61' '00' '2e' '62' '73' '73' '00' '2e' '73' '79' '6d'
'74' '61' '62' '00' '2e' '73' '74' '72' '74' '61' '62' '00' '2e' '73' '68' '73'
'74' '72' '74' '61' '62' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00'
'00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00'
'00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00'
'00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00'
'00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '01' '00' '00' '00' '01'
'00' '00' '00' '00' '00' '00' '00' '06' '00' '00' '00' '01' '00' '00' '00' 'b0'
'00' '00' '00' '00' '00' '00' '00' 'b0' '00' '00' '00' '00' '00' '00' '00' '58'
'00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '04'
'00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '07' '00' '00' '00' '01'
'00' '00' '00' '00' '00' '00' '00' '03' '00' '00' '00' '01' '00' '10' '01' '08'
'00' '00' '00' '00' '00' '00' '01' '08' '00' '00' '00' '00' '00' '00' '00' '18'
'00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '08'
'00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '0d' '00' '00' '00' '08'
'00' '00' '00' '00' '00' '00' '00' '03' '00' '00' '00' '01' '00' '10' '01' '20'
'00' '00' '00' '00' '00' '00' '01' '20' '00' '00' '00' '00' '00' '00' '00' '00'
'00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '01'
'00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '12' '00' '00' '00' '02'
'00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00'
'00' '00' '00' '00' '00' '00' '01' '20' '00' '00' '00' '00' '00' '00' '01' 'e0'
'00' '00' '00' '05' '00' '00' '00' '0d' '00' '00' '00' '00' '00' '00' '00' '08'
'00' '00' '00' '00' '00' '00' '00' '18' '00' '00' '00' '1a' '00' '00' '00' '03'
'00' '00' '00' '00' '00' '00' '00' '20' '00' '00' '00' '00' '00' '00' '00' '00'
'00' '00' '00' '00' '00' '00' '03' '00' '00' '00' '00' '00' '00' '00' '00' '4a'
'00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '01'
'00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '22' '00' '00' '00' '03'
'00' '00' '00' '00' '00' '00' '00' '20' '00' '00' '00' '00' '00' '00' '00' '00'
'00' '00' '00' '00' '00' '00' '03' '4a' '00' '00' '00' '00' '00' '00' '00' '2c'
'00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '00' '01'
'00' '00' '00' '00' '00' '00' '00' '00' );
ERROR 42X01: Syntax error: Encountered "\'45\'" at line 2, column 7.
ij> INSERT INTO "ADDTestCases" ( "pairID", "expectedSum", "executableFile" ) VALUES( 1, 0,
X'7f454c460202010000000000000000000002002b0000000100000001000000b00000000000000040000000000000037800000000004000380002004000070006000000010000000500000000000000b000000001000000b0000000000000000000000000000000580000000000000058000000000010000000000001000000070000000000000108000000010010010800000000000000000000000000000018000000000000001800000000001000000b0000008a1160018b297020090004008811210888110005c25900000b0000008a1160018b297020090004008811211088110005c4590000860040020b0000008a1160018b297020090004008811211888110005c6710000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010400fff100000000000000000000000000000000000000000300000100000001000000b000000000000000000000000003000002000000010010010800000000000000000000000003000003000000010010012000000000000000000000000003000004000000000000000000000000000000000000000003000005000000000000000000000000000000000000000003000006000000000000000000000000000000000000002501000003000000010010012000000000000000000000002b0100000100000001000000b000000000000000000000000701000002000000010010011800000000000000080000000b01000002000000010010010800000000000000080000001301000002000000010010011000000000000000080000001b1100000300000001001001200000000000000000000000201200000100000001000000b00000000000000000000000001d00000000000000000000020000000000000000000000001d000000000000000000000300000000000000000000003311000002000000010010012000000000000000000000003a1100000100000001000001080000000000000000000000411100fff10000000000000000000000000000000000612e6f75740073756d00616464656e643100616464656e6432005f656e64006d61696e005f454e445f005f53544152545f005f6564617461005f6574657874005f44594e414d494300002e74657874002e64617461002e627373002e73796d746162002e737472746162002e7368737472746162000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100000001000000000000000600000001000000b000000000000000b00000000000000058000000000000000000000000000000040000000000000000000000070000000100000000000000030000000100100108000000000000010800000000000000180000000000000000000000000000000800000000000000000000000d000000080000000000000003000000010010012000000000000001200000000000000000000000000000000000000000000000010000000000000000000000120000000200000000000000000000000000000000000000000000012000000000000001e0000000050000000d000000000000000800000000000000180000001a00000003000000000000002000000000000000000000000000000300000000000000004a000000000000000000000000000000010000000000000000000000220000000300000000000000200000000000000000000000000000034a000000000000002c000000000000000000000000000000010000000000000000'
);
ERROR 42821: Columns of type 'BLOB' cannot hold values of type 'CHAR () FOR BIT DATA'.
ij>

[Kristian Waagan-4]

Alan M. Feldstein wrote:

> Alan M. Feldstein wrote:
>  
>> I should be able to use ij to execute SQL statements
>> interactively ... in order to add a couple of rows to the
>> database.
>>
>> I want to put a couple of rows into the database on a clean machine
>> using a minimalist approach, as proof of concept for the
>> database specification I'm writing here
>> (http://www.alanfeldstein.com/products/software/fss/download/test_cases_database.html).
>> I'll be just about done writing that Web page once I can explain the
>> requirements for populating the executableFile column.
>>  
>>    
>
> For a minimalist approach, I tried the following two SQL statements at the
> ij prompt. Both failed.
>  

[ snip - failed statement ]
> ij> INSERT INTO "ADDTestCases" ( "pairID", "expectedSum", "executableFile" )
> VALUES( 1, 0,
> X'7f454c460202010
>  
[ snip - rest of long line ]
> );
> ERROR 42821: Columns of type 'BLOB' cannot hold values of type 'CHAR () FOR
> BIT DATA'.
> ij>
>  
Hello Alan,

I think you have to add "CAST (X'many-hex-values' AS BLOB)" in your
statement, like this:

ij> create table blobtest (data blob);
0 rows inserted/updated/deleted
ij> insert into blobtest values CAST(X'7f454c4602' AS BLOB);
1 row inserted/updated/deleted
ij> select * from blobtest;
DATA                                                                                                                            

--------------------------------------------------------------------------------------------------------------------------------
7f454c4602                                                                                                                      


1 row selected
ij>


regards,
--
Kristian

[Alan M. Feldstein]

Kristian Waagan wrote:

I think you have to add "CAST (X'many-hex-values' AS BLOB)" in your
statement

Yes, that does work, even for my larger object. I think that, according to SQL:2003, the original value specification should have been recognized as a binary string literal (i.e. a value of type BLOB). In fact, I find no mention of type CHAR FOR BIT DATA in the standard. Nevertheless, the cast specification is standard SQL.