date:20120514

Re: [sqlite] SQLite 3.7.12 64-bit Binary

2012-05-14 Thread Rafael Trevisan

Unfortunately I need a specific version for 64-bit.

[]s,

Rafael Trevisan
raf...@trevis.com.br



On Mon, May 14, 2012 at 9:43 PM, Joshua Shanks  wrote:

> The 32 bit version should run fine on Windows 64 bit. Do you
> specifically need the 64 bit compiled version?
>
> On Mon, May 14, 2012 at 5:39 PM, Rafael Trevisan 
> wrote:
> > Someone tell me where I get the dll SQLite 3.7.12 for Windows 64-bit? I
> do
> > not develop in C + + and I can not compile.
> >
> > []s,
> >
> > Rafael Trevisan
> > raf...@trevis.com.br
> > ___
> > 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] SQLite 3.7.12 64-bit Binary

2012-05-14 Thread Joshua Shanks

The 32 bit version should run fine on Windows 64 bit. Do you
specifically need the 64 bit compiled version?

On Mon, May 14, 2012 at 5:39 PM, Rafael Trevisan  wrote:
> Someone tell me where I get the dll SQLite 3.7.12 for Windows 64-bit? I do
> not develop in C + + and I can not compile.
>
> []s,
>
> Rafael Trevisan
> raf...@trevis.com.br
> ___
> 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] SQLite 3.7.12 64-bit Binary

2012-05-14 Thread Rafael Trevisan

Someone tell me where I get the dll SQLite 3.7.12 for Windows 64-bit? I do
not develop in C + + and I can not compile.

[]s,

Rafael Trevisan
raf...@trevis.com.br
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to insert UTF-8 strings in SQLITE3.EXE?

2012-05-14 Thread Yuriy Kaminskiy

Kit wrote:
> 2012/5/13, Frank Chang :
>> Here is another way I found out how insert UTF-8 strings in SQLITE3.EXE.
>>
>> F:\sqlite3_6_16>sqlite3.exe mdName.dat
>> SQLite version 3.6.16
>> Enter ".help" for instructions
>> Enter SQL statements terminated with a ";"
>> sqlite> INSERT INTO PREFIX SELECT CAST(x'52C3B373' AS TEXT),'M','Ros','10';
>> sqlite> .quit
> 
> sqlite> CREATE TABLE PREFIX (a TEXT, b TEXT, c TEXT, d INT);
> sqlite> INSERT INTO PREFIX SELECT CAST(x'52C3B373' AS TEXT),'M','Ros','10';
> sqlite> INSERT INTO PREFIX SELECT x'52C3B373','M','Ros','10';
> sqlite> INSERT INTO PREFIX VALUES (x'52C3B373','M','Ros','10');
> sqlite> SELECT * FROM PREFIX;
> Rós|M|Ros|10
> Rós|M|Ros|10
> Rós|M|Ros|10

While they look similar at first, they are actually different:
sqlite> SELECT TYPEOF(a), LENGTH(a) FROM prefix;
text|3
blob|4
blob|4

And in sqlite CAST (and other expressions) can be used with INSERT VALUES too:
sqlite> INSERT INTO PREFIX VALUES (CAST(x'52C3B373' AS TEXT),'M','Ros','10');
(resulting in correct 'text|3' row).

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

Re: [sqlite] proposal for api

2012-05-14 Thread Jay A. Kreibich

On Mon, May 14, 2012 at 10:55:35PM +0300, Baruch Burstein scratched on the wall:
> Assuming that sqlite knows how many rows are in a result set without having
> to sqlite3_step over each row,

  It doesn't.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] proposal for api

2012-05-14 Thread Baruch Burstein

Assuming that sqlite knows how many rows are in a result set without having
to sqlite3_step over each row, can there be an api like:

sqlite3_in64 sqlite3_rows(sqlite3_stmt *stmt)

which would run the query (if sqlite3_step has not been called on this stmt
object yet), and return the number of rows in the result set?

Or is my assumption that sqlite has this information wrong?

-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to write line feed "\n\r" when output a txt file

2012-05-14 Thread Black, Michael (IS)

Under windows:

SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t(a,b);
sqlite> insert into t values(1,2);
sqlite> insert into t values(3,4);
sqlite> .output akk.txt
sqlite> select * from t;
sqlite> .output stdout
sqlite> .quit

od -x akk.txt
000 7c31 0a32 7c33 0a34
010

LF only -- no CR.

So the output won't be happy under notepad.  Wordpad would work fine though on 
the file.  After which you save it from Wordpad and you'll have the CR/LF.
od -x akk.txt
000 7c31 0d32 330a 347c 0a0d

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 Kees Nuyt [k.n...@zonnet.nl]
Sent: Monday, May 14, 2012 2:03 PM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] how to write line feed "\n\r" when output a txt file

On Mon, 14 May 2012 05:41:08 +, YAN HONG YE 
wrote:

>when I use :
>
>.output akk.txt
>select * from dhq where qph>0;
>.output stdout
>
> command to write a txt file,I found no "\n\r" in the each line,

Are you sure?

By the way, common lineendings are platform dependent

MS Windows: \r\n  = 0x0D 0x0A = cr lf
Unix/Linux: \n= 0x0A = lf
Apple  Mac: \r= 0x0D = cr

As far as i know, using
\n\r  = 0x0A 0x0D = lf cr
is very very rare. In fact I only saw it on the console interface of a
DATUS portselector, back in the 1980s.

> how to write "\n\r" each line in the output txt file?

Pipe it through awk, for example (Unix/Linux):

printf "select * from dhq where qph>0;\n" \
| sqlite3 your.db \
| awk '{printf "%s\r\n",$0}' \
>akk.txt

In awk on MS Windows, "\n" will render "\r\n", except when BINMODE is
used.

Many programs are able to cope with several different line endings, but
indeed notepad insists on \r\n.

Hope this helps.

--
Regards,

Kees Nuyt

___
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] how to write line feed "\n\r" when output a txt file

2012-05-14 Thread Kees Nuyt

On Mon, 14 May 2012 05:41:08 +, YAN HONG YE 
wrote:

>when I use :
>
>.output akk.txt
>select * from dhq where qph>0;
>.output stdout
>
> command to write a txt file,I found no "\n\r" in the each line,

Are you sure?

By the way, common lineendings are platform dependent

MS Windows: \r\n  = 0x0D 0x0A = cr lf
Unix/Linux: \n= 0x0A = lf
Apple  Mac: \r= 0x0D = cr

As far as i know, using 
\n\r  = 0x0A 0x0D = lf cr
is very very rare. In fact I only saw it on the console interface of a
DATUS portselector, back in the 1980s.

> how to write "\n\r" each line in the output txt file? 

Pipe it through awk, for example (Unix/Linux):

printf "select * from dhq where qph>0;\n" \
| sqlite3 your.db \
| awk '{printf "%s\r\n",$0}' \
>akk.txt

In awk on MS Windows, "\n" will render "\r\n", except when BINMODE is
used.

Many programs are able to cope with several different line endings, but
indeed notepad insists on \r\n.

Hope this helps.

-- 
Regards,

Kees Nuyt

___
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

2012-05-14 Thread 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

Re: [sqlite] Trouble importing hex encoded blob

2012-05-14 Thread 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 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
>>

Re: [sqlite] Trouble importing hex encoded blob

2012-05-14 Thread 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

2012-05-14 Thread 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

2012-05-14 Thread 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

2012-05-14 Thread 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] a couple of questions

2012-05-14 Thread Baruch Burstein

I rebind them on every iteration.

On Mon, May 14, 2012 at 6:46 PM, Pavel Ivanov  wrote:

> > One the data is commited surely it doesn't need to be retained, does it?
>
> If you called sqlite3_reset() on a statement and then didn't call
> sqlite3_clear_bindings() then all bindings will surely be needed on
> the next statement execution in some subsequent transaction.
>
>
> Pavel
>
>
> On Mon, May 14, 2012 at 11:35 AM, Black, Michael (IS)
>  wrote:
> > If you do a periodic commit and use SQLITE_TRANSIENT wouldn't that work?
> >
> >
> >
> > One the data is commited surely it doesn't need to be retained, does it?
> >
> >
> >
> > 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 Baruch Burstein [bmburst...@gmail.com]
> > Sent: Monday, May 14, 2012 10:20 AM
> > To: General Discussion of SQLite Database
> > Subject: EXT :Re: [sqlite] a couple of questions
> >
> > On Mon, May 14, 2012 at 5:45 PM, Richard Hipp  wrote:
> >
> >> On Mon, May 14, 2012 at 10:35 AM, Baruch Burstein  >> >wrote:
> >>
> >> >
> >> > 2. How "static" does data have to be to be bound with SQLITE_STATIC?
> If
> >> it
> >> > won't change until the call to sqlite3_step, is that enough? How about
> >> > until sqlite3_reset or sqlite3_clear_bindings? If all of those are not
> >> > enough, I assume that if it doesn't change before the
> sqlite3_finalize,
> >> it
> >> > must be safe to use SQLITE_STATIC?
> >> >
> >>
> >> SQLITE_STATIC means that the content must not change until after SQLite
> has
> >> read it for the last time.  You are guaranteed to be safe if you hold
> the
> >> content unchanged until sqlite3_finalize().  You can probably get away
> with
> >> changing the content sooner than that, but it depends on your
> >> circumstances.  Why push your luck?
> >>
> >
> > For speed. I will be binding - stepping (it is an update statement) -
> > resetting in a tight loop, possibly up to about a million times.
> >
> > --
> > Programming today is a race between software engineers striving to build
> > bigger and better idiot-proof programs, and the Universe trying to
> produce
> > bigger and better idiots. So far, the Universe is winning.  - Rich Cook
> > ___
> > 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
>



-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] a couple of questions

2012-05-14 Thread Pavel Ivanov

> One the data is commited surely it doesn't need to be retained, does it?

If you called sqlite3_reset() on a statement and then didn't call
sqlite3_clear_bindings() then all bindings will surely be needed on
the next statement execution in some subsequent transaction.


Pavel


On Mon, May 14, 2012 at 11:35 AM, Black, Michael (IS)
 wrote:
> If you do a periodic commit and use SQLITE_TRANSIENT wouldn't that work?
>
>
>
> One the data is commited surely it doesn't need to be retained, does it?
>
>
>
> 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 Baruch Burstein [bmburst...@gmail.com]
> Sent: Monday, May 14, 2012 10:20 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] a couple of questions
>
> On Mon, May 14, 2012 at 5:45 PM, Richard Hipp  wrote:
>
>> On Mon, May 14, 2012 at 10:35 AM, Baruch Burstein > >wrote:
>>
>> >
>> > 2. How "static" does data have to be to be bound with SQLITE_STATIC? If
>> it
>> > won't change until the call to sqlite3_step, is that enough? How about
>> > until sqlite3_reset or sqlite3_clear_bindings? If all of those are not
>> > enough, I assume that if it doesn't change before the sqlite3_finalize,
>> it
>> > must be safe to use SQLITE_STATIC?
>> >
>>
>> SQLITE_STATIC means that the content must not change until after SQLite has
>> read it for the last time.  You are guaranteed to be safe if you hold the
>> content unchanged until sqlite3_finalize().  You can probably get away with
>> changing the content sooner than that, but it depends on your
>> circumstances.  Why push your luck?
>>
>
> For speed. I will be binding - stepping (it is an update statement) -
> resetting in a tight loop, possibly up to about a million times.
>
> --
> Programming today is a race between software engineers striving to build
> bigger and better idiot-proof programs, and the Universe trying to produce
> bigger and better idiots. So far, the Universe is winning.  - Rich Cook
> ___
> 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] a couple of questions

2012-05-14 Thread Black, Michael (IS)

If you do a periodic commit and use SQLITE_TRANSIENT wouldn't that work?



One the data is commited surely it doesn't need to be retained, does it?



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 Baruch Burstein [bmburst...@gmail.com]
Sent: Monday, May 14, 2012 10:20 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] a couple of questions

On Mon, May 14, 2012 at 5:45 PM, Richard Hipp  wrote:

> On Mon, May 14, 2012 at 10:35 AM, Baruch Burstein  >wrote:
>
> >
> > 2. How "static" does data have to be to be bound with SQLITE_STATIC? If
> it
> > won't change until the call to sqlite3_step, is that enough? How about
> > until sqlite3_reset or sqlite3_clear_bindings? If all of those are not
> > enough, I assume that if it doesn't change before the sqlite3_finalize,
> it
> > must be safe to use SQLITE_STATIC?
> >
>
> SQLITE_STATIC means that the content must not change until after SQLite has
> read it for the last time.  You are guaranteed to be safe if you hold the
> content unchanged until sqlite3_finalize().  You can probably get away with
> changing the content sooner than that, but it depends on your
> circumstances.  Why push your luck?
>

For speed. I will be binding - stepping (it is an update statement) -
resetting in a tight loop, possibly up to about a million times.

--
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
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

2012-05-14 Thread 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] a couple of questions

2012-05-14 Thread Baruch Burstein

On Mon, May 14, 2012 at 5:45 PM, Richard Hipp  wrote:

> On Mon, May 14, 2012 at 10:35 AM, Baruch Burstein  >wrote:
>
> >
> > 2. How "static" does data have to be to be bound with SQLITE_STATIC? If
> it
> > won't change until the call to sqlite3_step, is that enough? How about
> > until sqlite3_reset or sqlite3_clear_bindings? If all of those are not
> > enough, I assume that if it doesn't change before the sqlite3_finalize,
> it
> > must be safe to use SQLITE_STATIC?
> >
>
> SQLITE_STATIC means that the content must not change until after SQLite has
> read it for the last time.  You are guaranteed to be safe if you hold the
> content unchanged until sqlite3_finalize().  You can probably get away with
> changing the content sooner than that, but it depends on your
> circumstances.  Why push your luck?
>

For speed. I will be binding - stepping (it is an update statement) -
resetting in a tight loop, possibly up to about a million times.

-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] a couple of questions

2012-05-14 Thread Richard Hipp

On Mon, May 14, 2012 at 10:35 AM, Baruch Burstein wrote:

> 1. Can a use the expression 'WHERE b IN (SELECT ...)' if b is a blob
> column? Does the 'IN' comparison work with blobs?
>

IN works with blobs.


>
> 2. How "static" does data have to be to be bound with SQLITE_STATIC? If it
> won't change until the call to sqlite3_step, is that enough? How about
> until sqlite3_reset or sqlite3_clear_bindings? If all of those are not
> enough, I assume that if it doesn't change before the sqlite3_finalize, it
> must be safe to use SQLITE_STATIC?
>

SQLITE_STATIC means that the content must not change until after SQLite has
read it for the last time.  You are guaranteed to be safe if you hold the
content unchanged until sqlite3_finalize().  You can probably get away with
changing the content sooner than that, but it depends on your
circumstances.  Why push your luck?


>
> --
> Programming today is a race between software engineers striving to build
> bigger and better idiot-proof programs, and the Universe trying to produce
> bigger and better idiots. So far, the Universe is winning.  - Rich Cook
> ___
> 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] a couple of questions

2012-05-14 Thread Baruch Burstein

1. Can a use the expression 'WHERE b IN (SELECT ...)' if b is a blob
column? Does the 'IN' comparison work with blobs?

2. How "static" does data have to be to be bound with SQLITE_STATIC? If it
won't change until the call to sqlite3_step, is that enough? How about
until sqlite3_reset or sqlite3_clear_bindings? If all of those are not
enough, I assume that if it doesn't change before the sqlite3_finalize, it
must be safe to use SQLITE_STATIC?

-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] parameters

2012-05-14 Thread Baruch Burstein

Thank you for the quick answer.

On Mon, May 14, 2012 at 4:55 PM, Richard Hipp  wrote:

> On Mon, May 14, 2012 at 9:49 AM, Baruch Burstein  >wrote:
>
> > Are text parameters bound with sqlite3_bind_text automatically escaped
> and
> > quoted, just escaped, just quoted, or neither?
> >
>
>
> Both.  And neither.
>
> The content of the parameter is not modified in any way.  It is copied
> directly into the database file, byte for byte.  This has the same effect
> as if the parameter had been both escaped and quoted and inserted into the
> SQL and then parsed, but is much faster since no conversions take place.
>
>
> >
> > --
> > Programming today is a race between software engineers striving to build
> > bigger and better idiot-proof programs, and the Universe trying to
> produce
> > bigger and better idiots. So far, the Universe is winning.  - Rich Cook
> > ___
> > 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
>



-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to accurately retrieve UTF-8 String stored in SQLite 3.7.11 databases using sqlite3_column_text?

2012-05-14 Thread Richard Hipp

On Mon, May 14, 2012 at 9:48 AM, Frank Chang wrote:

>
>  Good morning, Is it possible to accurately retrieve UTF-8 String stored
> in SQLite 3.7.11 databases using sqlite3_column_text?
>

Yes.  That is the default behavior.  In fact, I do not know of a way to get
it to work otherwise.



> If not, what sqlite3 C/C++ api should we use? Thank you.
> ___
> 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] parameters

2012-05-14 Thread Richard Hipp

On Mon, May 14, 2012 at 9:49 AM, Baruch Burstein wrote:

> Are text parameters bound with sqlite3_bind_text automatically escaped and
> quoted, just escaped, just quoted, or neither?
>

Both.  And neither.

The content of the parameter is not modified in any way.  It is copied
directly into the database file, byte for byte.  This has the same effect
as if the parameter had been both escaped and quoted and inserted into the
SQL and then parsed, but is much faster since no conversions take place.

>
> --
> Programming today is a race between software engineers striving to build
> bigger and better idiot-proof programs, and the Universe trying to produce
> bigger and better idiots. So far, the Universe is winning.  - Rich Cook
> ___
> 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] Is it possible to accurately retrieve UTF-8 String stored in SQLite 3.7.11 databases using sqlite3_column_text?

2012-05-14 Thread Frank Chang


  Good morning, Is it possible to accurately retrieve UTF-8 String stored in 
SQLite 3.7.11 databases using sqlite3_column_text? 
 If not, what sqlite3 C/C++ api should we use? Thank you.   
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] parameters

2012-05-14 Thread Baruch Burstein

Are text parameters bound with sqlite3_bind_text automatically escaped and
quoted, just escaped, just quoted, or neither?

-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite version 3.7.12

2012-05-14 Thread D . Richard Hipp

SQLite version 3.7.12 is now available on the SQLite website:

http://www.sqlite.org/

An overview of the enhancements in this release can be seen here:

http://www.sqlite.org/releaselog/3_7_12.html

Please send email to the sqlite-users@sqlite.org mailing list if you encounter 
any problems with this release.

--
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] sqlite3_finalize

2012-05-14 Thread Richard Hipp

On Mon, May 14, 2012 at 7:55 AM, Baruch Burstein wrote:

> If sqlite3_prepare_v2 didn't return SQLITE_OK, do I need to call
> sqlite3_finalize on the statement pointer?
>

No.  If sqlite3_prepare_v2() returns anything other than SQLITE_OK then the
statement pointer will be NULL.  A call to sqlite3_finalize() in that case
will always be a harmless no-op and can be omitted.



>
>
> --
> Programming today is a race between software engineers striving to build
> bigger and better idiot-proof programs, and the Universe trying to produce
> bigger and better idiots. So far, the Universe is winning.  - Rich Cook
> ___
> 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] sqlite3_finalize

2012-05-14 Thread Baruch Burstein

If sqlite3_prepare_v2 didn't return SQLITE_OK, do I need to call
sqlite3_finalize on the statement pointer?


-- 
Programming today is a race between software engineers striving to build
bigger and better idiot-proof programs, and the Universe trying to produce
bigger and better idiots. So far, the Universe is winning.  - Rich Cook
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to insert UTF-8 strings in SQLITE3.EXE?

2012-05-14 Thread Kit

2012/5/13, Frank Chang :
> Here is another way I found out how insert UTF-8 strings in SQLITE3.EXE.
>
> F:\sqlite3_6_16>sqlite3.exe mdName.dat
> SQLite version 3.6.16
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> INSERT INTO PREFIX SELECT CAST(x'52C3B373' AS TEXT),'M','Ros','10';
> sqlite> .quit 

sqlite> CREATE TABLE PREFIX (a TEXT, b TEXT, c TEXT, d INT);
sqlite> INSERT INTO PREFIX SELECT CAST(x'52C3B373' AS TEXT),'M','Ros','10';
sqlite> INSERT INTO PREFIX SELECT x'52C3B373','M','Ros','10';
sqlite> INSERT INTO PREFIX VALUES (x'52C3B373','M','Ros','10');
sqlite> SELECT * FROM PREFIX;
Rós|M|Ros|10
Rós|M|Ros|10
Rós|M|Ros|10
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [sqlite-dev] Bad performance with foreign key contraint

2012-05-14 Thread Dan Kennedy


On 05/14/2012 10:39 AM, Jonas Malaco Filho wrote:

I found this on the docs :
If this SELECT returns any rows at all, then SQLite concludes that
deleting the row from the parent table would violate the foreign key
constraint and returns an error. Similar queries may be run if the
content of the parent key is modified *or a new row is inserted into the
parent table*. If these queries cannot use an index, they are forced to
do a linear scan of the entire child table. In a non-trivial database,
this may be prohibitively expensive.

I have to ask: _why would similar queries on the child table be run upon
insertions into the parent table_?


Fair question. Note the "may". They're not always run. Here's an
example:

  CREATE TABLE p(x PRIMARY KEY);
  CREATE TABLE c(y REFERENCES p(x) DEFERRABLE INITIALLY DEFERRED);
  BEGIN;
INSERT INTO c VALUES('abc');
INSERT INTO p VALUES('abc');
  COMMIT;

When the first insert is run, SQLite records the fact that there is
a foreign key violation in the database (since there is no parent
key for 'abc'). When the second insert is run, SQLite checks if it
resolves the existing foreign key violation by checking for rows
in "c" that match the new parent key. i.e. it internally does:

   SELECT count(*) FROM c WHERE y='abc';

However, if you were to do this:

  BEGIN;
INSERT INTO p VALUES('def');
  COMMIT;

Then SQLite would not have to scan table "c" - as it knows that there
are no FK violations in the database and so no reason to check if
any have been resolved.

Quite probably this thread should be on sqlite-users@sqlite.org
instead of this list. Since it is of interest to users, not just
those working on the development of SQLite.

Dan.

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

Re: [sqlite] SQLite 3.7.12 64-bit Binary

Re: [sqlite] SQLite 3.7.12 64-bit Binary

[sqlite] SQLite 3.7.12 64-bit Binary

Re: [sqlite] Is it possible to insert UTF-8 strings in SQLITE3.EXE?

Re: [sqlite] proposal for api

[sqlite] proposal for api

Re: [sqlite] how to write line feed "\n\r" when output a txt file

Re: [sqlite] how to write line feed "\n\r" when output a txt file

Re: [sqlite] Trouble importing hex encoded blob

Re: [sqlite] Trouble importing hex encoded blob

Re: [sqlite] Trouble importing hex encoded blob

Re: [sqlite] Trouble importing hex encoded blob

Re: [sqlite] Trouble importing hex encoded blob

Re: [sqlite] Trouble importing hex encoded blob

Re: [sqlite] a couple of questions

Re: [sqlite] a couple of questions

Re: [sqlite] a couple of questions

Re: [sqlite] Trouble importing hex encoded blob

Re: [sqlite] a couple of questions

Re: [sqlite] a couple of questions

[sqlite] a couple of questions

Re: [sqlite] parameters

Re: [sqlite] Is it possible to accurately retrieve UTF-8 String stored in SQLite 3.7.11 databases using sqlite3_column_text?

Re: [sqlite] parameters

[sqlite] Is it possible to accurately retrieve UTF-8 String stored in SQLite 3.7.11 databases using sqlite3_column_text?

[sqlite] parameters

[sqlite] SQLite version 3.7.12

Re: [sqlite] sqlite3_finalize

[sqlite] sqlite3_finalize

Re: [sqlite] Is it possible to insert UTF-8 strings in SQLITE3.EXE?

Re: [sqlite] [sqlite-dev] Bad performance with foreign key contraint

31 matches

Site Navigation

Mail list logo

Footer information