Seems like with or without the X it doesn't do the same thing as an import
$ cat sample.tsv
import X'1234' X'1234'
import 1234 1234
CREATE TABLE samples (
method varchar(64),
value blob
);
insert INTO samples (method, value) VALUES ("insert - null", null);
insert INTO samples (method, value) VALUES ("insert - 1234", x'1234');
.separator "\t"
.import sample.tsv samples
select method, length(value), hex(value) from samples;
sqlite> select method, length(value), hex(value) from samples;
insert - null
insert - 1234 2 1234
import X'1234' 7 58273132333427
import 1234 4 31323334
On Mon, May 14, 2012 at 8:24 AM, Jim Morris <[email protected]> wrote:
> We added blob import on an old version of the shell, 3.5.9. Using a simple
> HexToByte function.
>
> To function: static int do_meta_command(char *zLine, struct callback_data
> *p){
>
> Added: unsigned char * blobBuffer = NULL;
>
> In the loop // ***** Bind cached values to prepared statement. *****
> we added
> else if( nColType[i] == SQLITE_BLOB )
> {
> textLen = strlen(azCol[i]);
> // Convert from Hex to Binary.
> blobLen = HexToByte( &blobBuffer, azCol[i], textLen );
>
> // Have sqlite make an internal copy since we may have
> multiple blobs...
> rc = sqlite3_bind_blob(pStmt, i+1, blobBuffer, blobLen,
> SQLITE_TRANSIENT);
> }
>
> with a free(blobBuffer); done for each row after the reset.
>
>
>
> On 5/11/2012 10:22 PM, Joshua Shanks wrote:
>>
>> I peeked at the source code real quick and it looks like it just
>> converts the contents of the file into a bunch of SQL that is
>> essentially opening a transaction and doing an insert for each row
>> followed by a commit. This suggest I just need to format it
>> differently so I'll play around with that tomorrow and report back on
>> if I make an progress. It looks to be using prepared statements
>> instead of straight inserts which is what i tried to convert out of.
>>
>> On Fri, May 11, 2012 at 7:22 PM, Richard Hipp<[email protected]> wrote:
>>>
>>> On Fri, May 11, 2012 at 10:13 PM, Joshua Shanks<[email protected]>
>>> wrote:
>>>
>>>> I set the separator to tab and then in the file it is
>>>>
>>>> X'somevalue'\tX'someothervalue'\n
>>>> X'morestuff'\tX'evenmore'\n
>>>>
>>>> but with real hex values
>>>>
>>>> According to the documentation
>>>>
>>> That document you quote is describing the SQL langauge, not CSV. There
>>> is
>>> no way to enter BLOBs using CSV, that I know of.
>>>
>>> The ".import" command operatos on CSV, not SQL.
>>>
>>>
>>>> BLOB literals are string literals containing hexadecimal data and
>>>> preceded by a single "x" or "X" character. For example:
>>>>
>>>> X'53514C697465'
>>>>
>>>> On Fri, May 11, 2012 at 6:16 PM, Simon Slavin<[email protected]>
>>>> wrote:
>>>>>
>>>>> On 12 May 2012, at 2:01am, Joshua Shanks<[email protected]> wrote:
>>>>>
>>>>>> But when I try to use the .import method the values get imported as
>>>>>> the string "X'...." instead of the hex blob value and don't get pulled
>>>>>> out correctly.
>>>>>
>>>>> .import is for .csv files. What are you putting in the .csv file to
>>>>
>>>> express a value in hex ? I don't think there's a way to do it.
>>>>>
>>>>> Simon.
>>>>> _______________________________________________
>>>>> sqlite-users mailing list
>>>>> [email protected]
>>>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>>
>>>> _______________________________________________
>>>> sqlite-users mailing list
>>>> [email protected]
>>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>>
>>>
>>>
>>> --
>>> D. Richard Hipp
>>> [email protected]
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [email protected]
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [email protected]
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
