Re: [sqlite] Trouble importing hex encoded blob

[Black, Michael (IS)]

And do you want to do the blob_bind when the column type is blob?  Or when the 
field is X'' format?.



I can imagine an extension where you use the column type and then have URL 
qualifiers available too.





Added:unsigned char * blobBuffer = NULL;

 else if( nColType[i] == SQLITE_BLOB )
 {

if (strncasecmp(azCol[i],"'X",2)==0) {
 textLen = strlen(azCol[i]);
 // Convert from Hex to Binary.
 blobLen = HexToByte(,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);

 }

 else if (strncasecmp(azCol[i],"file:",5)==0) {

   // read file and insert as blob

 }

 else if (strncasecmp(azCol[i],"http:",5)==0) {

   // read web link and insert as blob (possible image or 
such?)

 }

 else {

   // unknown field type

 }
 }

Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Black, Michael (IS) [michael.bla...@ngc.com]
Sent: Monday, May 14, 2012 12:28 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Trouble importing hex encoded blob

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);
  }



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trouble importing hex encoded blob

[Black, Michael (IS)]

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: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Jim Morris [jmor...@bearriver.com]
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<jmor...@bearriver.com>  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(,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<d...@sqlite.org>wrote:
>>>> On Fri, May 11, 2012 at 10:13 PM, Joshua Shanks<jjsha...@gmail.com>
>>>>   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:
>>>

Re: [sqlite] Trouble importing hex encoded blob

[Jim Morris]

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  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(,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 Hippwrote:

On Fri, May 11, 2012 at 10:13 PM, Joshua Shanks
  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
wrote:

On 12 May 2012, at 2:01am, Joshua Shankswrote:


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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trouble importing hex encoded blob

[Joshua Shanks]

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  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( ,    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  wrote:
>>>
>>> On Fri, May 11, 2012 at 10:13 PM, Joshua Shanks
>>>  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
 wrote:
>
> On 12 May 2012, at 2:01am, Joshua Shanks  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
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

 ___
 sqlite-users mailing list
 sqlite-users@sqlite.org
 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

>>>
>>>
>>> --
>>> D. Richard Hipp
>>> d...@sqlite.org
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trouble importing hex encoded blob

[Joshua Shanks]

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  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( ,    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  wrote:
>>>
>>> On Fri, May 11, 2012 at 10:13 PM, Joshua Shanks
>>>  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
 wrote:
>
> On 12 May 2012, at 2:01am, Joshua Shanks  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
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

 ___
 sqlite-users mailing list
 sqlite-users@sqlite.org
 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

>>>
>>>
>>> --
>>> D. Richard Hipp
>>> d...@sqlite.org
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trouble importing hex encoded blob

[Black, Michael (IS)]

I updated my csvimport utility to allow hex fields.
So hex fields like X'01020304' will get imported as blobs if the option is 
enabled.

Sooo

test.csv:
X'0001063500',X'00'
X'0001063501',X'01'

csvimport test.csv test.db t
csvimport -x test.csv test.db t

Here's a dump of test.db after both command are run...you can see the first two 
are strings and the last two are blobs.
CREATE TABLE t(a blob,b blob);
INSERT INTO "t" VALUES('X''0001063500''','X''00''');
INSERT INTO "t" VALUES('X''0001063501''','X''01''');
INSERT INTO "t" VALUES(X'0001063500',X'00');
INSERT INTO "t" VALUES(X'0001063501',X'01');



// csvimport -- At least somewhat RFC4180 compliant
// quoted fields can span multiple lines
// quotes can be inserted by escaping with another quote
// will parse fields like X'01020304' as blobs
#include 
#include 
#include 
#include "sqlite3.h"

#define BUFSIZE 100
#define MOD 10
#define MAXTOKEN 10

int nline=0;
int ncol;
char buf[BUFSIZE];
int peek;
char delim=',';
char quote='"';
char eol='\n';
int hexFlag=0;

int nextChar(FILE *fp) {
  static char *ptr=buf;
  if (*ptr == 0) {
if(fgets(buf,BUFSIZE,fp)==NULL) {
  return EOF;
}
++nline;
ptr=buf;
  }
  peek = *(ptr+1);
  return *ptr++;
}

int countColumns(FILE *fp) {
  // 1st line(s) of the file determine how many columns we have
  int p;
  int ncol = 0;
  while((p=nextChar(fp))!=EOF) {
if (p == quote) { // start quote
  p=nextChar(fp);
  if (p==quote) continue; // escaped quote
  while((p=nextChar(fp)) && p!=quote); // skip to next quote
}
if (p == delim) {
  ++ncol;
}
if (p == eol) {
  break;
}
  }
  return ++ncol;
}

char *nextToken(FILE *fp) {
  // 1st line(s) of the file determine how many columns we have
  int p;
  int n=0;
  static char *token;
  int inquote=0;
  if (token == NULL) {
token=malloc(MAXTOKEN);
if (token==NULL) {
  fprintf(stderr,"malloc error: %%m\n");
  exit(1);
}
  }
  token[0]=0;
  while((p=nextChar(fp))!=EOF) {
if (p == quote) { // start quote
  p=nextChar(fp);
  if (p==quote) { // escaped quote
token[n++]=p;
token[n]=0;
continue;
  }
  token[n++]=p;
  token[n]=0;
  inquote = 1;
  while(inquote) {
p=nextChar(fp);
if (p==quote && peek==quote) {
  p=nextChar(fp); // skip on escaped quote
  token[n++]=p;
  token[n]=0;
  continue;
}
if (p==quote) {
  inquote=0;
} else {
  token[n++]=p;
  token[n]=0;
}
  }
  continue;
}
if (p == delim) {
  token[n]=0;
  return token;
}
if (p == eol) {
  break;
}
token[n++]=p;
token[n]=0;
  }
  if (feof(fp) && n==0) {
return NULL;
  }
  token[n]=0;
  return token;
}

void checkrc(sqlite3 *db,int rc,int checkrc,int flag,char *msg,char *str) {
  if (rc != checkrc) {
fprintf(stderr,"\nLine#%d: ",nline);
fprintf(stderr,msg,str);
fprintf(stderr,"%s\n",sqlite3_errmsg(db));
if (flag) { // then fatal
  exit(1);
}
  }
}

char escape(char *s) {
  if (*s != '\\') return *s;
  ++s;
  switch(*s) {
  case 't':
return '\t';
  case 'n':
return '\n';
  case '"':
return '"';
  }
  fprintf(stderr,"Unknown escape sequence=%s\n",--s);
  return '\0';
}

// Converts hex string X'' to blob value
unsigned char *makeblob(char *s, int *size) {
  static unsigned char *blob,*b;
  int i;
  if (blob) free(blob);
  *size=(strlen(s)-3)/2;
  blob=malloc(*size);
  b=blob;
  for(i=2; s[i]!='\''; i+=2) {
int value;
sscanf([i],"%2x",);
*b=value;
b++;
  }
  return blob;
}

// Add comma delimited file to exisiting database/table
// Quoted strings are accepted
int main(int argc, char *argv[]) {
  sqlite3 *db;
  sqlite3_stmt *stmt;
  int rc;
  int ninsert=0;
  int mycol;
  char sql[32768];
  FILE *fp;
  char *filename;
  char *databasename;
  char *tablename;

  while(argv[1] && argv[1][0]=='-') {
switch(argv[1][1]) {
case 'd':
  delim=escape([1][2]);
  break;
case 'e':
  eol=escape([1][2]);
  break;
case 'x':
  hexFlag=1;
  break;
default:
  fprintf(stderr,"Bad option: %s\n",argv[1]);
}
++argv;
--argc;
  }
  if (argc != 4) {
fprintf(stderr,"%s Version 1.1\n",argv[0]);
fprintf(stderr,"Usage: csvimport [-d] [-e] [-r] filename databasename 
tablename\n");
fprintf(stderr,"-d   Change field delimiter, default -d,\n");
fprintf(stderr,"-e   Change field enclosure char, default -e\\\"\n");
fprintf(stderr,"-r   Change record delimiter, default -r\\n\n");
fprintf(stderr,"-x   Parse hex fields as blobs (e.g. X'01020304'\n");
exit(1);
  }
  filename = argv[1];
  databasename = argv[2];
  tablename = argv[3];
  rc = sqlite3_open_v2(databasename,,SQLITE_OPEN_READWRITE,NULL);
  

Re: [sqlite] Trouble importing hex encoded blob

[Jim Morris]

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( ,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  wrote:

On Fri, May 11, 2012 at 10:13 PM, Joshua Shanks  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
wrote:

On 12 May 2012, at 2:01am, Joshua Shanks  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users




--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trouble importing hex encoded blob

[Joshua Shanks]

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  wrote:
> On Fri, May 11, 2012 at 10:13 PM, Joshua Shanks  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 
>> wrote:
>> >
>> > On 12 May 2012, at 2:01am, Joshua Shanks  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
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trouble importing hex encoded blob

[Richard Hipp]

On Fri, May 11, 2012 at 10:13 PM, Joshua Shanks  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 
> wrote:
> >
> > On 12 May 2012, at 2:01am, Joshua Shanks  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
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trouble importing hex encoded blob

[Joshua Shanks]

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

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  wrote:
>
> On 12 May 2012, at 2:01am, Joshua Shanks  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
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trouble importing hex encoded blob

[Simon Slavin]

On 12 May 2012, at 2:01am, Joshua Shanks  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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Trouble importing hex encoded blob

[Joshua Shanks]

I've tried searching for awhile and haven't had any luck. I'm able to
store and retrieve blob data using inserts and the hex encoded
literals like this

INSERT INTO rolled (roll1, roll2) VALUES
(X'0001063500',X'00');

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.

Do I need to use a different format for .import or do I just have to
use inserts for blobs?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users