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?
0000000 4241 0000 0000 0000 0000
A B nul nul nul nul nul nul nul nul
0000012
Here is the FULL code listing with the added PEEK so that I can write
out the data to a file again.....
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "sqlite3.h"
#include <time.h>
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 = sqlite3_finalize(stmt);
return 0;
}
//Peek By TID
int queue_peekByTID(const char *tid, message *msg){
char *peekText = "SELECT * FROM queue WHERE tid = :tid;";
const char *value;
int idx;
int len;
sqlite3_prepare_v2(handle,peekText,-1,&stmt,0 );
idx = sqlite3_bind_parameter_index( stmt, ":tid" );
sqlite3_bind_text( stmt, idx, tid, -1, SQLITE_STATIC );
rc = sqlite3_step(stmt);
if(rc == SQLITE_ROW){
value = (const char*)sqlite3_column_text(stmt,1);
strcpy(msg->tid, value);
len = sqlite3_column_int(stmt,3);
msg->num_bytes_in = len;
msg->raw_stream_in = malloc(len * sizeof(char));
value = (const char*)sqlite3_column_blob(stmt,2);
strncpy(msg->raw_stream_in, value, len);
}else{
sqlite3_finalize(stmt);
return 1;
}
sqlite3_finalize(stmt);
return 0;
}
Any ideas?
Thanks again for all your help!!!!
Lynton
On 03/04/2011 14:19, Black, Michael (IS) wrote:
> 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: [email protected] [[email protected]] on
> behalf of Lynton Grice [[email protected]]
> 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
> data....in fact I should just leave off the extension entirely.
>
> I have submitted my full code in my previous message, thanks....please
> 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
>
> 0000000 4241 000a
> A B nl
> 0000003
>
> 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<stdio.h>
>> 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
>> 0000000 4241 4400 4645 4847 4a49
>> A B nul D E F G H I J
>> 0000012
>> 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: [email protected] [[email protected]] on
>> behalf of Lynton Grice [[email protected]]
>> 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
>>> [email protected]
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> _______________________________________________
>> sqlite-users mailing list
>> [email protected]
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> _______________________________________________
>> sqlite-users mailing list
>> [email protected]
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
