Re: [sqlite] Import data in SQLite from excel using C# code
>From what I see in the examples in libxml, I can read an excel file in my program, but I still cannot figure out how to upload that file(the data in that file) to a table in SQlite. Mohd Radzi Ibrahim wrote: > > Hi, > Try use www.libxl.com that has C/C++/C#/Delphi interface to read excel > files. The rest are just normal sqlite commands. > > > On 28-Mar-2011, at 9:17 AM, Deepti Marathe wrote: > >> >> Hi, >> >> I am new to SQLite and am using it for the first time. I need to create >> an >> application using C# that will export the data from EXCEL to SQLite. >> Please >> can anybody guide me. >> Thanks in advance! >> >> -- >> View this message in context: >> http://old.nabble.com/Import-data-in-SQLite-from-excel-using-C--code-tp31253267p31253267.html >> Sent from the SQLite mailing list archive at Nabble.com. >> >> ___ >> 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 > > -- View this message in context: http://old.nabble.com/Import-data-in-SQLite-from-excel-using-C--code-tp31253267p31311644.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Help with changing the code
Hi! I'm new in SQLite, and I'm studying it to do a work in my college, and would be helpful if I get the SQLite to print, while executing the sql, after each table scan or join, the name of the table and number of rows that are going to the next operator... for example.. the following sql: select * from student join college on student.id = college.id where student.age = 20; would print: After table student scan - rows = 2324; After 1st join - rows = 200; Is there anything like this in sqlite that I could use? If not, is it too difficult change the code? Could anyone give me some simple code examples? Thanks!! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IF-THEN-ELSE sqlite
On Apr 3, 2011, at 3:18 PM, Marcelo Serrano Zanetti wrote: > It does not work in this way ... could somebody tell me please what is > the correct sintax or whether this is possible at all. As mentioned, SQL is not a procedural language, so, no. That said, you can achieve the same effect with two SQL statements called in succession: (1) insert or ignore into table( item ) values( new ) [1] (2) select itemID from table where item = new In other words, always try to create the new item, then select it. [1] http://www.sqlite.org/lang_insert.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IF-THEN-ELSE sqlite
On Sun, Apr 3, 2011 at 3:49 AM, Marcelo S Zanetti wrote: > IF 1==SELECT COUNT(*) from table > WHERE item==new THEN SELECT itemID from tabel WHERE item==new ELSE > INSERT INTO table (item) VALUES (new) INSERT INTO t (item) SELECT :new WHERE NOT EXISTS (SELECT item FROM t WHERE item = :new); Here :new is a parameter to be bound with sqlite3_bind_*(). Of course, it also seems like you could just INSERT OR IGNORE, as it seems likely that you want that item column to be a unique key (if not the primary key). There's no IF in SQL, and you can't embed an INSERT/UPDATE/DELETE inside a SELECT (so CASE won't help). But you _can_ have WHERE clauses that don't obviously relate to the rows being selected. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] IF-THEN-ELSE sqlite
Hi I would like to do the following I have an item to insert in the table which will be inserted only if this item is not yet in that table otherwise I would like to return the item's key. like that IF 1==SELECT COUNT(*) from table WHERE item==new THEN SELECT itemID from tabel WHERE item==new ELSE INSERT INTO table (item) VALUES (new) It does not work in this way ... could somebody tell me please what is the correct sintax or whether this is possible at all. Thank You ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] :Re: sqlite3_bind_blob CHOPS off at first NULL char
Great, thanksI have made the change as you suggested;-) memcpy(msg->raw_stream_in, sqlite3_column_blob(stmt, 2), len); Lynton On 03/04/2011 15:25, Paul van Helden wrote: > On Sun, Apr 3, 2011 at 3:15 PM, Lynton > Gricewrote: > >> Thanks, issue solved with the following: >> >> len = sqlite3_column_bytes(stmt,2); >> memcpy(msg->raw_stream_in, sqlite3_column_text(stmt, 2), len); >> >> sqlite3_column_blob is a better function to use. sqlite3_column_text will > add a zero character and if your database uses UTF16 encoding will give you > an interesting result. > > Regards, > > Paul. > ___ > 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] IF-THEN-ELSE sqlite
On 03/04/2011, at 6:49 PM, Marcelo S Zanetti wrote: > I have an item to insert in the table which will be inserted only if this > item is not yet in that table otherwise I would like to return the item's key. > > like that > > IF 1==SELECT COUNT(*) from table > WHERE item==new THEN SELECT itemID from tabel WHERE item==new ELSE > INSERT INTO table (item) VALUES (new) > > It does not work in this way ... could somebody tell me please what is the > correct sintax or whether this is possible at all. SQL is a set manipulation language, not a procedural language. So you write commands that affect a subset of data all at once. To accomplish your task, you'd write this: insert into Table (Item) select new where new not in (select item from Table); select ItemID from Table where Item = new; Furthermore, if you are returning the key for some more manipulation, it's best done in the same SQL call, rather than manipulated in your application code only to be re-injected into the SQL from which it came. Tom BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IF-THEN-ELSE sqlite
On 04/03/2011 03:12 PM, Igor Tandetnik wrote: > Marcelo S Zanetti wrote: >> I >> have an item to insert in the table which will be inserted only if this >> item is not yet in that table otherwise I would like to return the >> item's key. >> >> like that >> >> IF 1==SELECT COUNT(*) from table >> WHERE item==new THEN SELECT itemID from tabel WHERE item==new ELSE >> INSERT INTO table (item) VALUES (new) >> >> It does not work in this way ... could somebody tell me please what is the >> correct sintax or whether this is possible at all. > You use SQLite API to execute the three SQL statements, and use whatever > programming language your host application is written in to implement the > surrounding logic. Thank you Igor! I thought it could be organized in a single query ... like you said, the only way I managed to do it was using three separated queries. Best Marcelo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] :Re: sqlite3_bind_blob CHOPS off at first NULL char
On Sun, Apr 3, 2011 at 3:15 PM, Lynton Grice wrote: > Thanks, issue solved with the following: > > len = sqlite3_column_bytes(stmt,2); > memcpy(msg->raw_stream_in, sqlite3_column_text(stmt, 2), len); > > sqlite3_column_blob is a better function to use. sqlite3_column_text will add a zero character and if your database uses UTF16 encoding will give you an interesting result. Regards, Paul. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] :Re: sqlite3_bind_blob CHOPS off at first NULL char
Lynton Grice wrote: > Thanks, issue solved with the following: > > len = sqlite3_column_bytes(stmt,2); > memcpy(msg->raw_stream_in, sqlite3_column_text(stmt, 2), len); Actually, you are supposed to use sqlite3_column_blob for binary data. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] :Re: sqlite3_bind_blob CHOPS off at first NULL char
Thanks, issue solved with the following: len = sqlite3_column_bytes(stmt,2); memcpy(msg->raw_stream_in, sqlite3_column_text(stmt, 2), len); Thanks to everyone for your help ;-) Lynton On 03/04/2011 14:52, Paul van Helden wrote: > On Sun, Apr 3, 2011 at 2:46 PM, Lynton > Gricewrote: > >> char* from SQLite? You say that SELECT treats message as TEXT which is >> fine, but then how can I get the FULL payload back into a char* so that >> I can write it to a file? >> >> SELECT doesn't treat the BLOB as text, the command line client (and > apparently .output) does. > > >> My "proof of concept" goal is to now get that full binary stream back >> 100% and write it to a file >> >> Read the results from the SELECT with a C program. sqlite3_column_bytes > >> I am getting this currently with your sample file when I do a full LOOP >> back test ;-( I must be doing the SELECT wrong somehow? >> > ___ > 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] IF-THEN-ELSE sqlite
Marcelo S Zanetti wrote: > I > have an item to insert in the table which will be inserted only if this > item is not yet in that table otherwise I would like to return the > item's key. > > like that > > IF 1==SELECT COUNT(*) from table > WHERE item==new THEN SELECT itemID from tabel WHERE item==new ELSE > INSERT INTO table (item) VALUES (new) > > It does not work in this way ... could somebody tell me please what is the > correct sintax or whether this is possible at all. You use SQLite API to execute the three SQL statements, and use whatever programming language your host application is written in to implement the surrounding logic. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question:how to insert row with multiple values from same field of different rows of another table?
On Apr 3, 2011, at 7:50 AM, Luuk wrote:
> On 03-04-2011 14:43, Colin Cuthbert wrote:
>> First time I've used this (or any!) mailing list, so sorry if I've done
>> something wrong.
>>
>> Pretty sure my question (in the subect) is phrased badly but it's the best I
>> could do!
>>
>> create table People(id integer primary key, name text);
>> insert into People (name) values ('bob');
>> insert into People (name) values ('fred');
>>
>> create table Cars(id integer primary key, name text);
>> insert into Cars (name) values ('ford');
>> insert into Cars (name) values ('volvo');
>>
>> create table CarOwners(id integer primary key, carId integer references
>> Cars(id), ownerId integer references People(id));
>> insert into CarOwners (carId, ownerId) select Cars.id, People.id from Cars,
>> People where Cars.name='ford' and People.name='bob';
>>
>> create table Couples(id integer primary key, personId1 integer references
>> People(id), personId2 integer references People(id));
>>
>> The last 'insert' statement seems to work for inserting a row into the
>> 'CarOwners' table, but I'm not sure that's the right/best way to do it.
>>
>> But how can I do a similar insert into the 'Couples' table? ie, how can I
>> insert a row (specifying 'personId1' and 'personId2' via queries based on
>> 'People.name') into the 'Couples' table?
>
> You forgot to define 'Couples'.
>> Does it start something like this?
>>
>> insert into Couples (personId1, personId2) select id, id from People where...
>
> select id, id from People will return the same id (from the same record)
> twice
>
> somehting like:
> select a.id, b.id from People a join People b on a.id<>b.id
> will give other results, but what youactually want to be returned
> depends on the definition of a 'Couple'...
>
You also want to do all of the above in a TRANSACTION, preferably with a
TRIGGER, to ensure the correct relationships are preserved.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] :Re: sqlite3_bind_blob CHOPS off at first NULL char
On Sun, Apr 3, 2011 at 2:46 PM, Lynton Grice wrote: > char* from SQLite? You say that SELECT treats message as TEXT which is > fine, but then how can I get the FULL payload back into a char* so that > I can write it to a file? > > SELECT doesn't treat the BLOB as text, the command line client (and apparently .output) does. > My "proof of concept" goal is to now get that full binary stream back > 100% and write it to a file > > Read the results from the SELECT with a C program. sqlite3_column_bytes > I am getting this currently with your sample file when I do a full LOOP > back test ;-( I must be doing the SELECT wrong somehow? > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question:how to insert row with multiple values from same field of different rows of another table?
On 03-04-2011 14:43, Colin Cuthbert wrote:
> First time I've used this (or any!) mailing list, so sorry if I've done
> something wrong.
>
> Pretty sure my question (in the subect) is phrased badly but it's the best I
> could do!
>
> create table People(id integer primary key, name text);
> insert into People (name) values ('bob');
> insert into People (name) values ('fred');
>
> create table Cars(id integer primary key, name text);
> insert into Cars (name) values ('ford');
> insert into Cars (name) values ('volvo');
>
> create table CarOwners(id integer primary key, carId integer references
> Cars(id), ownerId integer references People(id));
> insert into CarOwners (carId, ownerId) select Cars.id, People.id from Cars,
> People where Cars.name='ford' and People.name='bob';
>
> create table Couples(id integer primary key, personId1 integer references
> People(id), personId2 integer references People(id));
>
> The last 'insert' statement seems to work for inserting a row into the
> 'CarOwners' table, but I'm not sure that's the right/best way to do it.
>
> But how can I do a similar insert into the 'Couples' table? ie, how can I
> insert a row (specifying 'personId1' and 'personId2' via queries based on
> 'People.name') into the 'Couples' table?
You forgot to define 'Couples'.
> Does it start something like this?
>
> insert into Couples (personId1, personId2) select id, id from People where...
select id, id from People will return the same id (from the same record)
twice
somehting like:
select a.id, b.id from People a join People b on a.id<>b.id
will give other results, but what youactually want to be returned
depends on the definition of a 'Couple'...
;)
> Thanks.
>
> Colin.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] :Re: sqlite3_bind_blob CHOPS off at first NULL char
Hi Michael,
Thanks for you feedback, I must say I have learnt something for
sure.BUT I still face the problem on how to I READ the BLOB into a
char* from SQLite? You say that SELECT treats message as TEXT which is
fine, but then how can I get the FULL payload back into a char* so that
I can write it to a file?
My "proof of concept" goal is to now get that full binary stream back
100% and write it to a file
I am getting this currently with your sample file when I do a full LOOP
back test ;-( I must be doing the SELECT wrong somehow?
0004241
A B nul nul nul nul nul nul nul nul
012
Here is the FULL code listing with the added PEEK so that I can write
out the data to a file again.
#include
#include
#include
#include "sqlite3.h"
#include
typedef struct messageStruct {
char tid[37];
void *raw_stream_in;
int num_bytes_in;
void *raw_stream_out;
int num_bytes_out;
char timestamp[20];
}message;
sqlite3_stmt *stmt;
static sqlite3 *handle;
int rc;
void* p;
char *queue;
int queue_createQueue(char *queueName);
int queue_openQueue(char *queueName);
int queue_closeQueue();
int queue_receive(message *msg);
int queue_send(message msg);
int queue_peekByTID(const char *tid, message *msg);
int main(){
char *buffer = NULL;
time_t t;
struct tm *tmp;
FILE *fp;
if((fp = fopen("./in.txt", "rb"))==NULL){
printf("Cannot open file2\n");
exit(1);
}
fseek(fp, 0, SEEK_END);
long int fsize = ftell(fp);
printf("File size: %i\n", fsize);
rewind(fp);
buffer = malloc(fsize * sizeof(char));
fread(buffer, 1, fsize, fp);
fclose(fp);
//Create Queue
rc = queue_createQueue("test_queue");
if (rc ==0){
printf("Queue created...\n");
}
message msg_in = {};
strcpy(msg_in.tid, "123");
msg_in.tid[36] = '\0';
msg_in.raw_stream_in = buffer;
msg_in.num_bytes_in = fsize;
msg_in.raw_stream_out = NULL;
msg_in.num_bytes_out = 0;
t = time(NULL);
tmp = localtime(&t);
if (tmp == NULL) {
exit(1);
}
strftime(msg_in.timestamp, sizeof(msg_in.timestamp), "%Y-%m-%d
%H:%M:%S", tmp);
rc = queue_send(msg_in);
if(rc == 0){
printf("Message sent to queue successfully\n");
}
message msg_out = {};
rc = queue_peekByTID("123", &msg_out);
if(rc == 0){
printf("Message PEEKED successfully\n");
fp = fopen("./out.txt", "w");
if (fp== NULL) {
exit(1);
}
fwrite(msg_out.raw_stream_in, msg_in.num_bytes_in, 1, fp);
fclose(fp);
}
rc = queue_closeQueue();
if (rc ==0){
printf("Queue closed...\n");
}
return 0;
}
//Queue handler while waiting to get queue lock
int queueBusyHandler(void* db, int count){
return 1;
}
//Create Queue
int queue_createQueue(char *queueName){
char *queueTable = "CREATE TABLE [queue] ( "
"[idx] INTEGER NOT NULL PRIMARY KEY
AUTOINCREMENT, "
"[tid] CHAR(24) NOT NULL, "
"[raw_stream_in] BLOB, "
"[num_bytes_in] INTEGER "
"[raw_stream_out] BLOB, "
"[num_bytes_out] INTEGER "
"[timestamp] CHAR(20));";
char *index = "CREATE UNIQUE INDEX tid on queue([tid]);";
char *journalMode = "PRAGMA journal_mode=wal;";
int successFlag = 1;
rc = sqlite3_open_v2(queueName,&handle, SQLITE_OPEN_READWRITE |
SQLITE_OPEN_SHAREDCACHE | SQLITE_OPEN_CREATE, NULL);
if (rc == 0){
p = sqlite3_malloc(256);
sqlite3_busy_handler(handle, &queueBusyHandler, p);
sqlite3_free(p);
sqlite3_exec(handle,"PRAGMA default_cache_size = 50;",0,0,0);
rc = sqlite3_exec(handle,journalMode,0,0,0);
if(rc == 0){
rc = sqlite3_exec(handle,queueTable,0,0,0);
if(rc == 0){
rc = sqlite3_exec(handle,index,0,0,0);
if (rc == 0){
successFlag = 0;
}
}
}
}
return successFlag;
}
//Close Queue
int queue_closeQueue(){
rc = sqlite3_close(handle);
return rc;
}
//Send
int queue_send(message msg){
int idx;
char *sendText = "INSERT INTO queue (tid, raw_stream_in,
num_bytes_in) "
"VALUES(:tid, :raw_stream_in, :num_bytes_in)";
sqlite3_exec(handle,"BEGIN IMMEDIATE TRANSACTION;",0,0,0);
sqlite3_prepare_v2(handle,sendText,-1,&stmt,0 );
idx = sqlite3_bind_parameter_index( stmt, ":tid" );
sqlite3_bind_text( stmt, idx, msg.tid, -1, SQLITE_STATIC );
idx = sqlite3_bind_parameter_index( stmt, ":raw_stream_in" );
sqlite3_bind_blob( stmt, idx, msg.raw_stream_in, msg.num_bytes_in,
SQLITE_TRANSIENT );
idx = sqlite3_bind_parameter_index( stmt, ":num_bytes_in" );
sqlite3_bind_int( stmt, idx, msg.num_bytes_in);
rc = sqlite3_step(stmt);
rc = sqlite3_exec(handle,"COMMIT TRANSACTION;",0,0,0);
rc = sqlit
[sqlite] Question:how to insert row with multiple values from same field of different rows of another table?
First time I've used this (or any!) mailing list, so sorry if I've done
something wrong.
Pretty sure my question (in the subect) is phrased badly but it's the best I
could do!
create table People(id integer primary key, name text);
insert into People (name) values ('bob');
insert into People (name) values ('fred');
create table Cars(id integer primary key, name text);
insert into Cars (name) values ('ford');
insert into Cars (name) values ('volvo');
create table CarOwners(id integer primary key, carId integer references
Cars(id), ownerId integer references People(id));
insert into CarOwners (carId, ownerId) select Cars.id, People.id from Cars,
People where Cars.name='ford' and People.name='bob';
create table Couples(id integer primary key, personId1 integer references
People(id), personId2 integer references People(id));
The last 'insert' statement seems to work for inserting a row into the
'CarOwners' table, but I'm not sure that's the right/best way to do it.
But how can I do a similar insert into the 'Couples' table? ie, how can I
insert a row (specifying 'personId1' and 'personId2' via queries based on
'People.name') into the 'Couples' table?
Does it start something like this?
insert into Couples (personId1, personId2) select id, id from People where...
Thanks.
Colin.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] :Re: sqlite3_bind_blob CHOPS off at first NULL char
That she blows!!! (to quote Popeye).
Your select treats the message as text..and properly truncates at the first nul
character.
Since it's actuallly binary and NOT text use this:
select hex(raw_stream_in) from queue;
4142004445464748494A
You also forgot to do
select length(raw_stream_in) from queue;
Which would have showed you 10 so you should know you were doing something
wrong.
Hopefully you understand the diff between binary and text now.
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Lynton Grice [lynton.gr...@logosworld.com]
Sent: Sunday, April 03, 2011 7:05 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] sqlite3_bind_blob CHOPS off at first NULL char
Hi Michael,
When I mean't the ".output" I was meaning when I am using the SQLite
command line tool, I type in ".output result.txt" and then type "select
raw_stream_in from queue;" to stream that result to file.
The result file only contains character up until the first NUL.
Also, I know it is an image file (binary) I am just in the habit on
having .txt extensions when doing testing as all I care about is the raw
datain fact I should just leave off the extension entirely.
I have submitted my full code in my previous message, thanksplease
let me know if you see anything?
I ran your test below, on my Linux machine and it still has the same
issue, meaning after the INSERT into SQLite I do a ".output result.txt"
and it still only shows "AB" in your example
Here is the result of running od -xa result.txt
0004241000a
A B nl
003
I must be doing something fundamentally wrong.;-(
Thanks for the help so far, I really appreciate it.
Lynton
On 03/04/2011 13:44, Black, Michael (IS) wrote:
> How are you trying to view the ouitput.result.txt (and I"ll note that it'sNOT
> a text file...it's an image according to what you said.). What's the size of
> the file.
>
> And you should be able to post a COMPLETE example to show your testing. What
> you say you want to do has been done by many before so you're doing something
> wrong but you aren't showing it.
>
> Create a small test file using this:
>
> #include
> main()
> {
>FILE *fp=fopen("test.dat","w");
>char buf[10];
>int i;
>for(i=0;i<10;++i) buf[i]=i+'A';
>buf[2] = 0;
>fwrite(buf,1,sizeof(buf),fp);
>fclose(fp);
> }
>
> If you're on Unix (or have Unix utils installed on Windows) you should see
> this:
> ls -l test.dat
> -rw-r--r-- 1 mblack users 10 Apr 3 07:41 test.dat
> od -xa test.dat
> 000 4241 4400 4645 4847 4a49
>A B nul D E F G H I J
> 012
> more test.dat
> AB
>
> Note the "more" stop at the first NUL character like it should.
>
> Then...use this file to test your stream, show us the resulting file with "od
> -xa" and show us your code.
>
>
> Michael D. Black
> Senior Scientist
> NG Information Systems
> Advanced Analytics Directorate
>
>
>
>
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Lynton Grice [lynton.gr...@logosworld.com]
> Sent: Sunday, April 03, 2011 6:24 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] sqlite3_bind_blob CHOPS off at first NULL char
>
>Hi Paul,
>
> Yes, I am using fsize as the msg.num_bytes_in..
>
> When I run "SELECT Length(raw_stream_in) FROM test " I get the FULL
> 13035138 bytes
>
> But it I read the "raw_stream_in" and sent it to a file (using ".output
> result.txt") I only see chars up until the FIRST NUL.
>
> Any ideas?
>
> Thanks
>
> Lynton
>
>
>
> On 03/04/2011 12:52, Paul van Helden wrote:
>> Hi Lynton,
>>
>> What is the value of msg.num_bytes_in? Is it fsize?
>>
>> And what do you get when you SELECT Length(raw_stream_in) FROM test ?
>>
>> Regards,
>>
>> Paul.
>> ___
>> 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] sqlite3_bind_blob CHOPS off at first NULL char - FULL CODE
I ran your code with my test file and I get this...which is perfectly correct.
Do you get something different? What makes you think the stream is truncated
in the database?
Also...change SQLITE_STATIC to SQLITE_TRANSIENT...that could be your culprit if
you are still seeing truncation.
Plus "rb" is a Windows thing...not Unix...Windows treats fopen() files as text
by default and Windows puts CTRL-Z at the end of text files so the "rb" is
necessary to remove that. Unix does no such stupid thing.
This is redundant as the strcpy() in front of ti already puts a nul after the
string.
msg_in.tid[36] = '\0';
Rather than
printf("Cannot open file2\n");
Please use
perror("in.txt");
I always kicked my students for not using REAL error messages that actually
tell you what went wrong.
ls -l in.txt
-rw-r--r-- 1 mblack users 10 Apr 3 08:03 in.txt
od -xa in.txt
000 4241 4400 4645 4847 4a49
A B nul D E F G H I J
012
SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select length(raw_stream_in) from queue;
10
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE [queue] ( [idx] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [tid]
CHAR(24) NOT NULL, [raw_stream_in] BLOB, [num_bytes_in] INTEGER
[raw_stream_out] BLOB, [num_bytes_out] INTEGER [timestamp] CHAR(20));
INSERT INTO "queue" VALUES(1,'123',X'4142004445464748494A',10,NULL);
DELETE FROM sqlite_sequence;
INSERT INTO "sqlite_sequence" VALUES('queue',1);
CREATE UNIQUE INDEX tid on queue([tid]);
COMMIT;
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_bind_blob CHOPS off at first NULL char
Hi Michael,
When I mean't the ".output" I was meaning when I am using the SQLite
command line tool, I type in ".output result.txt" and then type "select
raw_stream_in from queue;" to stream that result to file.
The result file only contains character up until the first NUL.
Also, I know it is an image file (binary) I am just in the habit on
having .txt extensions when doing testing as all I care about is the raw
datain fact I should just leave off the extension entirely.
I have submitted my full code in my previous message, thanksplease
let me know if you see anything?
I ran your test below, on my Linux machine and it still has the same
issue, meaning after the INSERT into SQLite I do a ".output result.txt"
and it still only shows "AB" in your example
Here is the result of running od -xa result.txt
0004241000a
A B nl
003
I must be doing something fundamentally wrong.;-(
Thanks for the help so far, I really appreciate it.
Lynton
On 03/04/2011 13:44, Black, Michael (IS) wrote:
> How are you trying to view the ouitput.result.txt (and I"ll note that it'sNOT
> a text file...it's an image according to what you said.). What's the size of
> the file.
>
> And you should be able to post a COMPLETE example to show your testing. What
> you say you want to do has been done by many before so you're doing something
> wrong but you aren't showing it.
>
> Create a small test file using this:
>
> #include
> main()
> {
>FILE *fp=fopen("test.dat","w");
>char buf[10];
>int i;
>for(i=0;i<10;++i) buf[i]=i+'A';
>buf[2] = 0;
>fwrite(buf,1,sizeof(buf),fp);
>fclose(fp);
> }
>
> If you're on Unix (or have Unix utils installed on Windows) you should see
> this:
> ls -l test.dat
> -rw-r--r-- 1 mblack users 10 Apr 3 07:41 test.dat
> od -xa test.dat
> 000 4241 4400 4645 4847 4a49
>A B nul D E F G H I J
> 012
> more test.dat
> AB
>
> Note the "more" stop at the first NUL character like it should.
>
> Then...use this file to test your stream, show us the resulting file with "od
> -xa" and show us your code.
>
>
> Michael D. Black
> Senior Scientist
> NG Information Systems
> Advanced Analytics Directorate
>
>
>
>
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Lynton Grice [lynton.gr...@logosworld.com]
> Sent: Sunday, April 03, 2011 6:24 AM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] sqlite3_bind_blob CHOPS off at first NULL char
>
>Hi Paul,
>
> Yes, I am using fsize as the msg.num_bytes_in..
>
> When I run "SELECT Length(raw_stream_in) FROM test " I get the FULL
> 13035138 bytes
>
> But it I read the "raw_stream_in" and sent it to a file (using ".output
> result.txt") I only see chars up until the FIRST NUL.
>
> Any ideas?
>
> Thanks
>
> Lynton
>
>
>
> On 03/04/2011 12:52, Paul van Helden wrote:
>> Hi Lynton,
>>
>> What is the value of msg.num_bytes_in? Is it fsize?
>>
>> And what do you get when you SELECT Length(raw_stream_in) FROM test ?
>>
>> Regards,
>>
>> Paul.
>> ___
>> 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] sqlite3_bind_blob CHOPS off at first NULL char - FULL CODE
Hi Drake, Hi Richard, Hi Paul,
Thanks for all your comments, I really appreciate the help.
Please see FULL code below. You can literally copy and paste this and
compile it with something like:
gcc -m64 -Wall -g -I./ -c ./main.c -o ./bigbyte.o
gcc -o ./bigbyte ./bigbyte.o -L./ -Wl,-rpath,./ -lsqlite3 -lpthread -ldl
Then if you test it with a file called "in.txt" that has a NUL in it you
will see that it cuts it off in the SQLite db "test_queue"
Can you perhaps see something I am doing wrong below? C is not my day
job ..as you can see ;-)
#include
#include
#include
#include "sqlite3.h"
#include
typedef struct messageStruct {
char tid[37];
void *raw_stream_in;
int num_bytes_in;
void *raw_stream_out;
int num_bytes_out;
char timestamp[20];
}message;
sqlite3_stmt *stmt;
static sqlite3 *handle;
int rc;
void* p;
char *queue;
int queue_createQueue(char *queueName);
int queue_openQueue(char *queueName);
int queue_closeQueue();
int queue_send(message msg);
int main(){
char *buffer = NULL;
time_t t;
struct tm *tmp;
FILE *fp;
if((fp = fopen("./in.txt", "rb"))==NULL){
printf("Cannot open file2\n");
exit(1);
}
fseek(fp, 0, SEEK_END);
long int fsize = ftell(fp);
printf("File size: %i\n", fsize);
rewind(fp);
buffer = malloc(fsize * sizeof(char));
fread(buffer, 1, fsize, fp);
fclose(fp);
//Create Queue
rc = queue_createQueue("test_queue");
if (rc ==0){
printf("Queue created...\n");
}
message msg_in = {};
strcpy(msg_in.tid, "123");
msg_in.tid[36] = '\0';
msg_in.raw_stream_in = buffer;
msg_in.num_bytes_in = fsize;
msg_in.raw_stream_out = NULL;
msg_in.num_bytes_out = 0;
t = time(NULL);
tmp = localtime(&t);
if (tmp == NULL) {
exit(1);
}
strftime(msg_in.timestamp, sizeof(msg_in.timestamp), "%Y-%m-%d
%H:%M:%S", tmp);
rc = queue_send(msg_in);
if(rc == 0){
printf("Message sent to queue successfully\n");
}
rc = queue_closeQueue();
if (rc ==0){
printf("Queue closed...\n");
}
return 0;
}
//Queue handler while waiting to get queue lock
int queueBusyHandler(void* db, int count){
return 1;
}
//Create Queue
int queue_createQueue(char *queueName){
char *queueTable = "CREATE TABLE [queue] ( "
"[idx] INTEGER NOT NULL PRIMARY KEY
AUTOINCREMENT, "
"[tid] CHAR(24) NOT NULL, "
"[raw_stream_in] BLOB, "
"[num_bytes_in] INTEGER "
"[raw_stream_out] BLOB, "
"[num_bytes_out] INTEGER "
"[timestamp] CHAR(20));";
char *index = "CREATE UNIQUE INDEX tid on queue([tid]);";
char *journalMode = "PRAGMA journal_mode=wal;";
int successFlag = 1;
rc = sqlite3_open_v2(queueName,&handle, SQLITE_OPEN_READWRITE |
SQLITE_OPEN_SHAREDCACHE | SQLITE_OPEN_CREATE, NULL);
if (rc == 0){
p = sqlite3_malloc(256);
sqlite3_busy_handler(handle, &queueBusyHandler, p);
sqlite3_free(p);
sqlite3_exec(handle,"PRAGMA default_cache_size = 50;",0,0,0);
rc = sqlite3_exec(handle,journalMode,0,0,0);
if(rc == 0){
rc = sqlite3_exec(handle,queueTable,0,0,0);
if(rc == 0){
rc = sqlite3_exec(handle,index,0,0,0);
if (rc == 0){
successFlag = 0;
}
}
}
}
return successFlag;
}
//Close Queue
int queue_closeQueue(){
rc = sqlite3_close(handle);
return rc;
}
//Send
int queue_send(message msg){
int idx;
char *sendText = "INSERT INTO queue (tid, raw_stream_in,
num_bytes_in) "
"VALUES(:tid, :raw_stream_in, :num_bytes_in)";
sqlite3_exec(handle,"BEGIN IMMEDIATE TRANSACTION;",0,0,0);
sqlite3_prepare_v2(handle,sendText,-1,&stmt,0 );
idx = sqlite3_bind_parameter_index( stmt, ":tid" );
sqlite3_bind_text( stmt, idx, msg.tid, -1, SQLITE_STATIC );
idx = sqlite3_bind_parameter_index( stmt, ":raw_stream_in" );
sqlite3_bind_blob( stmt, idx, msg.raw_stream_in, msg.num_bytes_in,
SQLITE_TRANSIENT );
idx = sqlite3_bind_parameter_index( stmt, ":num_bytes_in" );
sqlite3_bind_int( stmt, idx, msg.num_bytes_in);
rc = sqlite3_step(stmt);
rc = sqlite3_exec(handle,"COMMIT TRANSACTION;",0,0,0);
rc = sqlite3_finalize(stmt);
return 0;
}
On 03/04/2011 13:11, Drake Wilson wrote:
> Quoth Lynton Grice, on 2011-04-03 12:37:06 +0200:
>>There are NULL characters
> You mean NUL characters.
>
>> in the first couple header fields [...]
>>
>> I am no C expert but I have the following table defined:
>>
>> char *queueTable = "CREATE TABLE [test] ( "
>> "[idx] INTEGER NOT NULL PRIMARY KEY
>> AUTOINCREMENT, "
>> "[raw_stream_in] BLOB, "
>> "[num_bytes_in] INTEGER );";
> I wouldn't recommend the use of [] for
Re: [sqlite] sqlite3_bind_blob CHOPS off at first NULL char
Hi Michael,
Well to be honest I am trying to mimic an "SAP IDoc (essentially a raw
stream) coming out of SAP. In this case the "SAP IDoc" contains a large
image, but 99% of the time it is just "plain text". So I probably forgot
the "rb", my mistake
When I say "point to MSG" I mean I do this:
msg_in.raw_stream_in = buffer;
I am sending all the code in next message...
Thanks
Lynton
On 03/04/2011 12:53, Black, Michael (IS) wrote:
> You apparently don't understand "strings" in C. Or are you actually reading
> in binary data?
>
> #1 Since you said "image" I assume you're reading binaary. So get rid of
> buffer[fsize]=0. You don't null terminate binary data and that statement is
> 1-beyond the end of the array (which is from 0 to fsize-1).
>
> #2 You then say you "point the MSG". I suspect you're doing that wrong with
> strcpy() or such. So if you would care to show ALL your code (it's just a
> few more lines) we can probably help.
>
> #3 Also, what happens to "buffer"?? Do you free it at some point after the
> bind? If so, that
>
>
>
> Michael D. Black
> Senior Scientist
> NG Information Systems
> Advanced Analytics Directorate
>
>
>
>
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
> behalf of Lynton Grice [lynton.gr...@logosworld.com]
> Sent: Sunday, April 03, 2011 5:37 AM
> To: sqlite-users@sqlite.org
> Subject: EXT :[sqlite] sqlite3_bind_blob CHOPS off at first NULL char
>
>Hi there,
>
> I have a 15MB file I need to read and store in an SQLite database.
>
>There are NULL characters in the first couple header fields and the
> rest is pure BINARY data. You can imagine the file looking like:
>
> ppphNULNUL3STR.and then all the BINARY data..
>
> I am no C expert but I have the following table defined:
>
> char *queueTable = "CREATE TABLE [test] ( "
> "[idx] INTEGER NOT NULL PRIMARY KEY
> AUTOINCREMENT, "
> "[raw_stream_in] BLOB, "
> "[num_bytes_in] INTEGER );";
>
> And then later in the code I use the "sqlite3_bind_blob" to send the
> stream to SQLIte
>
> sqlite3_bind_blob( stmt, idx, msg.raw_stream_in, msg.num_bytes_in,
> SQLITE_STATIC );
>
> For testing purposes I simply read the 15MB file from file into a char *.
>
> char *buffer = NULL;
> FILE *fp;
> if((fp = fopen("./in.txt", "r"))==NULL){
> exit(1);
> }
>
> fseek(fp, 0, SEEK_END);
> long int fsize = ftell(fp);
> printf("File size: %i\n", fsize);
> rewind(fp);
> buffer = malloc(fsize * sizeof(char));
> fread(buffer, 1, fsize, fp);
> buffer[fsize] = '\0';
> fclose(fp);
>
> I then point the MSG "raw_stream_in" to the buffer:
>
> msg_in.raw_stream_in = buffer;
>
> NOTE: Msg is a "message" as defined below:
>
> typedef struct messageStruct {
> char *raw_stream_in;
> int num_bytes_in;
> }message;
>
> I then use the following statement as mentioned before to insert the
> stream into the BLOB field:
>
> sqlite3_bind_blob( stmt, idx, msg.raw_stream_in, msg.num_bytes_in,
> SQLITE_STATIC );
>
> However, _only the first 4 bytes are copied_ (the "ppph" in the attached
> image). _So essentially all characters are copied until the first NULL
> char._
>
> How can I store EVERYTHING, including NULLs? Must I use a BYTE array or
> something? Does anyone have any sample code?
>
> I would be hugely appreciative for any help in this regard, thanks
>
> Lynton
>
> ___
> 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] sqlite3_bind_blob CHOPS off at first NULL char
How are you trying to view the ouitput.result.txt (and I"ll note that it'sNOT a
text file...it's an image according to what you said.). What's the size of the
file.
And you should be able to post a COMPLETE example to show your testing. What
you say you want to do has been done by many before so you're doing something
wrong but you aren't showing it.
Create a small test file using this:
#include
main()
{
FILE *fp=fopen("test.dat","w");
char buf[10];
int i;
for(i=0;i<10;++i) buf[i]=i+'A';
buf[2] = 0;
fwrite(buf,1,sizeof(buf),fp);
fclose(fp);
}
If you're on Unix (or have Unix utils installed on Windows) you should see this:
ls -l test.dat
-rw-r--r-- 1 mblack users 10 Apr 3 07:41 test.dat
od -xa test.dat
000 4241 4400 4645 4847 4a49
A B nul D E F G H I J
012
more test.dat
AB
Note the "more" stop at the first NUL character like it should.
Then...use this file to test your stream, show us the resulting file with "od
-xa" and show us your code.
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Lynton Grice [lynton.gr...@logosworld.com]
Sent: Sunday, April 03, 2011 6:24 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] sqlite3_bind_blob CHOPS off at first NULL char
Hi Paul,
Yes, I am using fsize as the msg.num_bytes_in..
When I run "SELECT Length(raw_stream_in) FROM test " I get the FULL
13035138 bytes
But it I read the "raw_stream_in" and sent it to a file (using ".output
result.txt") I only see chars up until the FIRST NUL.
Any ideas?
Thanks
Lynton
On 03/04/2011 12:52, Paul van Helden wrote:
> Hi Lynton,
>
> What is the value of msg.num_bytes_in? Is it fsize?
>
> And what do you get when you SELECT Length(raw_stream_in) FROM test ?
>
> Regards,
>
> Paul.
> ___
> 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] sqlite3_bind_blob CHOPS off at first NULL char
Hi Richard, My apologies on the subject line, I did not mean it to say the function is incorrect, but merely to understand why it is chopping it off I am responding to the other now Lynton On 03/04/2011 13:16, Richard Hipp wrote: > Others have responded with requests for information needed to help track > down the root of this problem. (Thanks!) But in the meantime, let me just > assure you that your subject line is absolutely, positively incorrect. > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_bind_blob CHOPS off at first NULL char
Hi Paul, Yes, I am using fsize as the msg.num_bytes_in.. When I run "SELECT Length(raw_stream_in) FROM test " I get the FULL 13035138 bytes But it I read the "raw_stream_in" and sent it to a file (using ".output result.txt") I only see chars up until the FIRST NUL. Any ideas? Thanks Lynton On 03/04/2011 12:52, Paul van Helden wrote: > Hi Lynton, > > What is the value of msg.num_bytes_in? Is it fsize? > > And what do you get when you SELECT Length(raw_stream_in) FROM test ? > > Regards, > > Paul. > ___ > 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] sqlite3_bind_blob CHOPS off at first NULL char
Others have responded with requests for information needed to help track down the root of this problem. (Thanks!) But in the meantime, let me just assure you that your subject line is absolutely, positively incorrect. -- 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_bind_blob CHOPS off at first NULL char
Quoth Lynton Grice , on 2011-04-03 12:37:06 +0200:
> There are NULL characters
You mean NUL characters.
> in the first couple header fields [...]
>
> I am no C expert but I have the following table defined:
>
> char *queueTable = "CREATE TABLE [test] ( "
> "[idx] INTEGER NOT NULL PRIMARY KEY
> AUTOINCREMENT, "
> "[raw_stream_in] BLOB, "
> "[num_bytes_in] INTEGER );";
I wouldn't recommend the use of [] for quoting identifiers (I'd prefer
double-quotes for more ANSI-esque SQL), but that looks like it should
work fine.
> sqlite3_bind_blob( stmt, idx, msg.raw_stream_in, msg.num_bytes_in,
> SQLITE_STATIC );
Be careful with SQLITE_STATIC. AIUI, the lifetime of the buffer must
then be a superset of the lifetime of the statement.
> For testing purposes I simply read the 15MB file from file into a char *.
>
>char *buffer = NULL;
>FILE *fp;
>if((fp = fopen("./in.txt", "r"))==NULL){
> exit(1);
>}
Not "rb" for binary mode?
>fseek(fp, 0, SEEK_END);
>long int fsize = ftell(fp);
>printf("File size: %i\n", fsize);
>rewind(fp);
>buffer = malloc(fsize * sizeof(char));
>fread(buffer, 1, fsize, fp);
>buffer[fsize] = '\0';
You're overwriting beyond the end of the array. You don't need an
extra NUL here.
>fclose(fp);
>
> I then point the MSG "raw_stream_in" to the buffer:
>
> msg_in.raw_stream_in = buffer;
And you set msg.num_bytes_in where?
> I then use the following statement as mentioned before to insert the
> stream into the BLOB field:
>
> sqlite3_bind_blob( stmt, idx, msg.raw_stream_in, msg.num_bytes_in,
> SQLITE_STATIC );
That looks fine by itself, subject to the caveats of SQLITE_STATIC
above and whether stmt and idx are valid.
> _So essentially all characters are copied until the first NULL
> char._
No. I strongly suspect that's a red herring.
In summary:
- Make sure msg.num_bytes_in is actually set to what you want.
- Make sure you're handling the lifetime of the buffer correctly;
for testing purposes I'd use SQLITE_TRANSIENT rather than
SQLITE_STATIC, since that evades that issue at the cost of a
memcpy.
- Don't write past the end of the array.
---> Drake Wilson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_bind_blob CHOPS off at first NULL char
Hi there,
I have a 15MB file I need to read and store in an SQLite database.
I have attached a file to show you that there are NULL characters in the
first couple header fields and the rest is pure BINARY data. But just
incase the image gets stipped off while posting you can imagine the file
looking like:
ppphNULNUL3STR.and then all the BINARY data..
I am no C expert but I have the following table defined:
char *queueTable = "CREATE TABLE [test] ( "
"[idx] INTEGER NOT NULL PRIMARY KEY
AUTOINCREMENT, "
"[raw_stream_in] BLOB, "
"[num_bytes_in] INTEGER );";
And then later in the code I use the "sqlite3_bind_blob" to send the
stream to SQLIte
sqlite3_bind_blob( stmt, idx, msg.raw_stream_in, msg.num_bytes_in,
SQLITE_STATIC );
For testing purposes I simply read the 15MB file from file into a char *.
char *buffer = NULL;
FILE *fp;
if((fp = fopen("./in.txt", "r"))==NULL){
exit(1);
}
fseek(fp, 0, SEEK_END);
long int fsize = ftell(fp);
printf("File size: %i\n", fsize);
rewind(fp);
buffer = malloc(fsize * sizeof(char));
fread(buffer, 1, fsize, fp);
buffer[fsize] = '\0';
fclose(fp);
I then point the MSG "raw_stream_in" to the buffer:
msg_in.raw_stream_in = buffer;
NOTE: Msg is a "message" as defined below:
typedef struct messageStruct {
char *raw_stream_in;
int num_bytes_in;
}message;
I then use the following statement as mentioned before to insert the
stream into the BLOB field:
sqlite3_bind_blob( stmt, idx, msg.raw_stream_in, msg.num_bytes_in,
SQLITE_STATIC );
However, _only the first 4 bytes are copied_ (the "ppph" in the attached
image). _So essentially all characters are copied until the first NULL
char._
How can I store EVERYTHING, including NULLs? Must I use a BYTE array or
something? Does anyone have any sample code?
I would be hugely appreciative for any help in this regard, thanks
Lynton
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_bind_blob CHOPS off at first NULL char
You apparently don't understand "strings" in C. Or are you actually reading in
binary data?
#1 Since you said "image" I assume you're reading binaary. So get rid of
buffer[fsize]=0. You don't null terminate binary data and that statement is
1-beyond the end of the array (which is from 0 to fsize-1).
#2 You then say you "point the MSG". I suspect you're doing that wrong with
strcpy() or such. So if you would care to show ALL your code (it's just a few
more lines) we can probably help.
#3 Also, what happens to "buffer"?? Do you free it at some point after the
bind? If so, that
Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Lynton Grice [lynton.gr...@logosworld.com]
Sent: Sunday, April 03, 2011 5:37 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] sqlite3_bind_blob CHOPS off at first NULL char
Hi there,
I have a 15MB file I need to read and store in an SQLite database.
There are NULL characters in the first couple header fields and the
rest is pure BINARY data. You can imagine the file looking like:
ppphNULNUL3STR.and then all the BINARY data..
I am no C expert but I have the following table defined:
char *queueTable = "CREATE TABLE [test] ( "
"[idx] INTEGER NOT NULL PRIMARY KEY
AUTOINCREMENT, "
"[raw_stream_in] BLOB, "
"[num_bytes_in] INTEGER );";
And then later in the code I use the "sqlite3_bind_blob" to send the
stream to SQLIte
sqlite3_bind_blob( stmt, idx, msg.raw_stream_in, msg.num_bytes_in,
SQLITE_STATIC );
For testing purposes I simply read the 15MB file from file into a char *.
char *buffer = NULL;
FILE *fp;
if((fp = fopen("./in.txt", "r"))==NULL){
exit(1);
}
fseek(fp, 0, SEEK_END);
long int fsize = ftell(fp);
printf("File size: %i\n", fsize);
rewind(fp);
buffer = malloc(fsize * sizeof(char));
fread(buffer, 1, fsize, fp);
buffer[fsize] = '\0';
fclose(fp);
I then point the MSG "raw_stream_in" to the buffer:
msg_in.raw_stream_in = buffer;
NOTE: Msg is a "message" as defined below:
typedef struct messageStruct {
char *raw_stream_in;
int num_bytes_in;
}message;
I then use the following statement as mentioned before to insert the
stream into the BLOB field:
sqlite3_bind_blob( stmt, idx, msg.raw_stream_in, msg.num_bytes_in,
SQLITE_STATIC );
However, _only the first 4 bytes are copied_ (the "ppph" in the attached
image). _So essentially all characters are copied until the first NULL
char._
How can I store EVERYTHING, including NULLs? Must I use a BYTE array or
something? Does anyone have any sample code?
I would be hugely appreciative for any help in this regard, thanks
Lynton
___
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] sqlite3_bind_blob CHOPS off at first NULL char
Hi Lynton, What is the value of msg.num_bytes_in? Is it fsize? And what do you get when you SELECT Length(raw_stream_in) FROM test ? Regards, Paul. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_bind_blob CHOPS off at first NULL char
Hi there,
I have a 15MB file I need to read and store in an SQLite database.
There are NULL characters in the first couple header fields and the
rest is pure BINARY data. You can imagine the file looking like:
ppphNULNUL3STR.and then all the BINARY data..
I am no C expert but I have the following table defined:
char *queueTable = "CREATE TABLE [test] ( "
"[idx] INTEGER NOT NULL PRIMARY KEY
AUTOINCREMENT, "
"[raw_stream_in] BLOB, "
"[num_bytes_in] INTEGER );";
And then later in the code I use the "sqlite3_bind_blob" to send the
stream to SQLIte
sqlite3_bind_blob( stmt, idx, msg.raw_stream_in, msg.num_bytes_in,
SQLITE_STATIC );
For testing purposes I simply read the 15MB file from file into a char *.
char *buffer = NULL;
FILE *fp;
if((fp = fopen("./in.txt", "r"))==NULL){
exit(1);
}
fseek(fp, 0, SEEK_END);
long int fsize = ftell(fp);
printf("File size: %i\n", fsize);
rewind(fp);
buffer = malloc(fsize * sizeof(char));
fread(buffer, 1, fsize, fp);
buffer[fsize] = '\0';
fclose(fp);
I then point the MSG "raw_stream_in" to the buffer:
msg_in.raw_stream_in = buffer;
NOTE: Msg is a "message" as defined below:
typedef struct messageStruct {
char *raw_stream_in;
int num_bytes_in;
}message;
I then use the following statement as mentioned before to insert the
stream into the BLOB field:
sqlite3_bind_blob( stmt, idx, msg.raw_stream_in, msg.num_bytes_in,
SQLITE_STATIC );
However, _only the first 4 bytes are copied_ (the "ppph" in the attached
image). _So essentially all characters are copied until the first NULL
char._
How can I store EVERYTHING, including NULLs? Must I use a BYTE array or
something? Does anyone have any sample code?
I would be hugely appreciative for any help in this regard, thanks
Lynton
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IF-THEN-ELSE sqlite
Hi I would like to do the following I have an item to insert in the table which will be inserted only if this item is not yet in that table otherwise I would like to return the item's key. like that IF 1==SELECT COUNT(*) from table WHERE item==new THEN SELECT itemID from tabel WHERE item==new ELSE INSERT INTO table (item) VALUES (new) It does not work in this way ... could somebody tell me please what is the correct sintax or whether this is possible at all. Thank You ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
