SQlite3 connection lb5x64-351

[metro]

Any chance another linux user can attempt running "simple database.bas" in a 64 bit environment

I have "sudo apt-get install sqlite3 libsqlite3-dev" and am able to create a db from the command line

however even after the recommended change to the sub I get an error


sub sqlExec query$
   sqliteconnect #records, "Records.db","/usr/lib64"
   #records execute(query$)
   #records disconnect()
end sub

[Chris Iverson]

This works for me, if I use the x86_64 library path.

chris@shinoko:~/lb5alpha-deb$ find /usr/lib -name "libsql*"
/usr/lib/x86_64-linux-gnu/libsqlite3.so.0.8.6
/usr/lib/x86_64-linux-gnu/libsqlite3.so.0
/usr/lib/i386-linux-gnu/libsqlite3.so.0.8.6
/usr/lib/i386-linux-gnu/libsqlite3.so.0

sub sqlExec query$
   sqliteconnect #records, "Records.db","/usr/lib/x86_64-linux-gnu"
   #records execute(query$)
   #records disconnect()
end sub

[metro]

Jun 6, 2020 22:52:20 GMT -5 Chris Iverson said:

This works for me, if I use the x86_64 library path.

chris@shinoko:~/lb5alpha-deb$ find /usr/lib -name "libsql*"
/usr/lib/x86_64-linux-gnu/libsqlite3.so.0.8.6
/usr/lib/x86_64-linux-gnu/libsqlite3.so.0
/usr/lib/i386-linux-gnu/libsqlite3.so.0.8.6
/usr/lib/i386-linux-gnu/libsqlite3.so.0

sub sqlExec query$
   sqliteconnect #records, "Records.db","/usr/lib/x86_64-linux-gnu"
   #records execute(query$)
   #records disconnect()
end sub

Thank you Chris, working here now

[metro]

Rather than create another thread, I'll put the question here as it's related to SQlite and lb5.

Most of us will be aware that Chris has given us a very handy tool for lb4.5 with his latest Dll's for SQlite3.
I have had some great success inserting PDF's and jpegs etc.
At the moment, with lb5-351  I can only insert text into a db field, all be it I can insert a whole binary file
but for reasons beyond my comprehension PDF's and jpeg insertions throw an error.

Is there a chance in the future of adding the ability to insert PDF's?

[Carl Gundel]

Jun 8, 2020 6:38:21 GMT -5 metro said:

Rather than create another thread, I'll put the question here as it's related to SQlite and lb5.

Most of us will be aware that Chris has given us a very handy tool for lb4.5 with his latest Dll's for SQlite3.
I have had some great success inserting PDF's and jpegs etc.
At the moment, with lb5-351  I can only insert text into a db field, all be it I can insert a whole binary file
but for reasons beyond my comprehension PDF's and jpeg insertions throw an error.

Is there a chance in the future of adding the ability to insert PDF's?

Better to not group all SQLite topics into one thread.

The answer to the question is that I don't really understand what Chris has done, but given that he has done it with a DLL, you're gonna need to use that DLL again when I add the ability to use them to LB5, and then that DLL will not be useful on the Mac or for Linux, etc.

[metro]

Jun 8, 2020 8:30:08 GMT -5 Carl Gundel said:

Jun 8, 2020 6:38:21 GMT -5 metro said:

Better to not group all SQLite topics into one thread.

The answer to the question is that I don't really understand what Chris has done, but given that he has done it with a DLL, you're gonna need to use that DLL again when I add the ability to use them to LB5, and then that DLL will not be useful on the Mac or for Linux, etc.

Understand, thanks Carl

[pierre]

Jun 8, 2020 8:30:08 GMT -5 Carl Gundel said:

Jun 8, 2020 6:38:21 GMT -5 metro said:

Rather than create another thread, I'll put the question here as it's related to SQlite and lb5.

Most of us will be aware that Chris has given us a very handy tool for lb4.5 with his latest Dll's for SQlite3.
I have had some great success inserting PDF's and jpegs etc.
At the moment, with lb5-351  I can only insert text into a db field, all be it I can insert a whole binary file
but for reasons beyond my comprehension PDF's and jpeg insertions throw an error.

Is there a chance in the future of adding the ability to insert PDF's?

Better to not group all SQLite topics into one thread.

The answer to the question is that I don't really understand what Chris has done, but given that he has done it with a DLL, you're gonna need to use that DLL again when I add the ability to use them to LB5, and then that DLL will not be useful on the Mac or for Linux, etc.

Chris did two things:

1) he provided us with a couple of dll's allowing us to use some of the extended SQLite3 functions in LB 4.5.1.

2) above all, he wrote a fairly complete set of functions to read directly the sqlite3.dll, thus replacing and improving the "old" SQLite3 wrapper.

So, this is not about the extra dll's and and LB5's ability to use them or not use them in the future.

This is about being able to use the core functions of the sqlite library, just as the SQLite mehod in Run BASIC and in Liberty BASIC 5 alpha is supposed to do.

But there are functions missing in RB and LB5 alpha and - in my opinion - badly needed :  the prepared statements with bound variables. These are the functions needed to bind and store Blob files and also to bind and store Text and Numeric user input, the best way to avoid SQL injection.

All programming languages we so easily criticize on this forum, do have these possibilities.

Therefore, the question is: could the SQLite method in LB5 alpha and subsequently in a future version of Run BASIC, be extended with these functions ?

I already wrote extensively about this question in the thread about the LB 4.5.1 - SQLite interface, but apparently nobody noticed it.

Perhaps Chris could jump in here ?

pierre

[Chris Iverson]

You can do binary blob insertion right now in LB5, but you have to convert the data to hexadecimal blob format first.

Here's some sample code that works for me:

open "..\test.png" for binary as #file

a$ = input$(#file, lof(#file))
close #file

for x = 1 to len(a$)
   hexStr$ = hexStr$ + right$("00" + dechex$(asc(mid$(a$, x, 1))), 2)
next x

sqliteconnect #test, "test.db"
#test execute("create table test ( name text, data blob );")
query$ = "insert into test (name, data) values ('test.png', x'" + hexStr$ + "');"
print query$
#test execute(query$)

#test execute("select * from test;")

if #test hasanswer() then
   for x = 1 to #test rowcount()
       result$ = #test nextrow$("|")
       
       pos = instr(result$, "|")
       name$ = left$(result$, pos - 1)
       fileData$ = right$(result$, len(result$) - pos)

       open "..\COPY-" + name$ for binary as #file
       print #file, fileData$;
       close #file
   next x
else
   print "no answer"
end if

#test disconnect()



The more important feature, however, is prepared statements/bound variables. Unfortunately, I have nothing to say about that, as I do not know how it is implemented in LB5 or in VisualWorks.

If SQLite support is something that Carl has coded from scratch himself, then it is possible he may be able to broaden the support to include such. If it's something that was provided by the VisualWorks toolset, then it depends on whether or not the toolset supports it.


I highly, highly agree with the suggestion to add this capability, though, ESPECIALLY for Run BASIC. The concept can be relaxed somewhat for LB5, since it's intended to run independently on a client computer anyway, but Run BASIC is a web application. It's designed to work directly with untrusted, hostile clients. SQL injection still remains one of the top(if not THE top) reason for data breaches in online websites, and using prepared statements with bound parameters literally makes SQL injection impossible(from those parameters; if you build the prepared statement/query in an insecure way, it's possible to still get hit, but it's far, FAR less likely to come from that than from user-provided, potentially-hostile data input.)

[pierre]

Jun 8, 2020 12:18:46 GMT -5 Chris Iverson said:

You can do binary blob insertion right now in LB5, but you have to convert the data to hexadecimal blob format first.

Here's some sample code that works for me:

open "..\test.png" for binary as #file

a$ = input$(#file, lof(#file))
close #file

for x = 1 to len(a$)
   hexStr$ = hexStr$ + right$("00" + dechex$(asc(mid$(a$, x, 1))), 2)
next x

sqliteconnect #test, "test.db"
#test execute("create table test ( name text, data blob );")
query$ = "insert into test (name, data) values ('test.png', x'" + hexStr$ + "');"
print query$
#test execute(query$)

#test execute("select * from test;")

if #test hasanswer() then
   for x = 1 to #test rowcount()
       result$ = #test nextrow$("|")
       
       pos = instr(result$, "|")
       name$ = left$(result$, pos - 1)
       fileData$ = right$(result$, len(result$) - pos)

       open "..\COPY-" + name$ for binary as #file
       print #file, fileData$;
       close #file
   next x
else
   print "no answer"
end if

#test disconnect()



The more important feature, however, is prepared statements/bound variables. Unfortunately, I have nothing to say about that, as I do not know how it is implemented in LB5 or in VisualWorks.

If SQLite support is something that Carl has coded from scratch himself, then it is possible he may be able to broaden the support to include such. If it's something that was provided by the VisualWorks toolset, then it depends on whether or not the toolset supports it.


I highly, highly agree with the suggestion to add this capability, though, ESPECIALLY for Run BASIC. The concept can be relaxed somewhat for LB5, since it's intended to run independently on a client computer anyway, but Run BASIC is a web application. It's designed to work directly with untrusted, hostile clients. SQL injection still remains one of the top(if not THE top) reason for data breaches in online websites, and using prepared statements with bound parameters literally makes SQL injection impossible(from those parameters; if you build the prepared statement/query in an insecure way, it's possible to still get hit, but it's far, FAR less likely to come from that than from user-provided, potentially-hostile data input.)

Hello Chris, thank you for your answer.

I'll try your code example in LB5. I hope metro will read your post, it could help him out with his pdf and jpeg files.

Thanks also for your answer on prepared statements with bound parameters. I brought this problem up several times already, but
never got any reaction, whatsoever...... Apparently, nobody interested ?

Of course, all I wrote was eventually aimed at Run BASIC. I don't really need it myself, I have no customers and will most probably never have. But I know about some of our members who do have customers actually. They should be interested, after all.

I always mention the two languages - RB and LB5 - for, theoretically, it is the same interface. LB5 inherited it from RB. As far as I have understood, it is all based on the SmallTalk SQLite3 wrapper. I can't imagine that SmallTalk would not support these useful functions.

If the RB/LB5 SQlite method is only a subset of the whole SmallTalk wrapper, then it would perhaps be possible to extend it somehow, at least for Run BASIC.

Only Carl could tell us something more about all this and even then, I understand that it will not be on the top of his to do list.

So let the real pro's speak out themselves about their needs.

I think I've done all I could possibly do.

pierre

[metro]

Jun 8, 2020 13:21:36 GMT -5 pierre said:

Hello Chris, thank you for your answer.

I'll try your code example in LB5. I hope metro will read your post, it could help him out with his pdf and jpeg files.

pierre

Thanks Chris and pierre, I was aware that converting the files enables them to be inserted thanks to Chris's post here libertybasiccom.proboards.com/post/1921

But how to implement it in lb5 was beyond me.

Thanks for the help.

metro

[metro]

Jun 8, 2020 12:18:46 GMT -5 Chris Iverson said:

You can do binary blob insertion right now in LB5, but you have to convert the data to hexadecimal blob format first.

Having a "Seniors Moment", Chris would you mind explaining why the "x" is needed in this query

thanks

query$ = "insert into test (name, data) values ('test.png', x'" + hexStr$ + "');"

[Stefan Pendl]

See www.sqlite.org/lang_expr.html, which describes the BLOB values as:

BLOB literals are string literals containing hexadecimal data and preceded by a single "x" or "X" character. Example: X'53514C697465'.

[metro]

Aug 22, 2020 0:07:08 GMT -5 Stefan Pendl said:

See www.sqlite.org/lang_expr.html, which describes the BLOB values as:

BLOB literals are string literals containing hexadecimal data and preceded by a single "x" or "X" character. Example: X'53514C697465'.

Thanks Stefan

I appreciate you taking the time to answer,