Looks like it goes inside this loop in 3.7.12 at line 1883 of shell.c. Could
we get maybe a pragma ".mode csvblob" or such and have this made a permanet
part of the shell?
for(i=0; i<nCol; i++){
if( azCol[i][0]=='"' ){
int k;
for(z=azCol[i], j=1, k=0; z[j]; j++){
if( z[j]=='"' ){ j++; if( z[j]==0 ) break; }
z[k++] = z[j];
}
z[k] = 0;
}
sqlite3_bind_text(pStmt, i+1, azCol[i], -1, SQLITE_STATIC);
}
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
________________________________
From: [email protected] [[email protected]] on
behalf of Jim Morris [[email protected]]
Sent: Monday, May 14, 2012 12:13 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Trouble importing hex encoded blob
Joshua,
It doesn't exist in the standard version. We added it in-house to aid
development and testing. The code I posted was the changes we made to
the 3.5.9 shell.c in addition to adding an existing hex to byte function
from our libraries.
If you can compile a new shell the existing shell, haven't checked the
shell code recently, is probably still similar enough to use a similar
style, if desired.
The provided code is just a hint as to what might work for you. We
never had time to attempt to push the code back to the standard shell.
The sqlite environment suggested was for Linux and since I'm using
Windows didn't have time to set up an environment.
Jim
On 5/14/2012 10:06 AM, Joshua Shanks wrote:
> Hey Jim,
>
> I downloaded the source or 3.7.12 from sqlite.org and can't find that code.
>
> $ ls
> shell.c sqlite3.c sqlite3ext.h sqlite3.h
> $ head -n3 sqlite3.c
> /******************************************************************************
> ** This file is an amalgamation of many separate C source files from SQLite
> ** version 3.7.12. By combining all the individual C code files into this
> $ grep blobBuffer *
> $
>
>
> 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
>
_______________________________________________
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
