SQLITE 3 support

Beginner

Thursday, January 29th 2009, 9:33am

What about SQLITE 3 support and binding?

How is it possible to use, under Jabaco, sqlite3.dll and especially the export functions:

int sqlite3_open(
const char *filename, /* Database filename (UTF-8) */
sqlite3 **ppDb /* OUT: SQLite db handle */
);

int sqlite3_exec(
sqlite3*, /* An open database */
const char *sql, /* SQL to be evaluated */
int (*callback)(void*,int,char**,char**), /* Callback function */
void *, /* 1st argument to callback */
char **errmsg /* Error msg written here */
);

int sqlite3_get_table(
sqlite3 *db, /* An open database */
const char *zSql, /* SQL to be evaluated */
char ***pazResult, /* Results of the query */
int *pnRow, /* Number of result rows written here */
int *pnColumn, /* Number of result columns written here */
char **pzErrmsg /* Error msg written here */
);

void sqlite3_free_table(char **result);

int sqlite3_close(sqlite3 *);

Thanks,

Frank

Go to the top of the page

OlimilO

Intermediate

Posts: 277

Location: Germany

Occupation: software engineer

2

Thursday, January 29th 2009, 11:29am

Hello frankp,

did you use sqlite3 in VB6?

i made a short look around the sourcecode of sqlite3 and i did not found the __stdcall calling convention.

in fact it is possible to use winapi-functions like the following, but winapi-functions use the __stdcall calling convention

Jabaco Source

Public WinAPI Function TheFunctionName lib "thedllname" (ByVal ahandle As Integer) As Integer
'int sqlite3_open( 
'Const char *filename, /* Database filename (UTF-8) */ 
'sqlite3 **ppDb /* OUT: SQLite db handle */ 
'); 
Private WinApi Function sqlite3_open Lib "sqlite3"( _
    ByVal filename As String, _
    ByVal ppDb As Integer _
    ) As Integer

i do not know if it is possible to call __cdecl-functions in jabaco, lets see what Manuel will say, i am interested in this point also

another problem would be the callbackfunction for the function sqlite3_exec this must be in __cdecl also

Maybe another (harder) solution would be to get the sourcode of sqlite3 and export all functions with __stdcall.

greetings

OlimilO

Go to the top of the page

frankp

Beginner

Posts: 14

3

Thursday, January 29th 2009, 6:47pm

SQLITE 3 support

Quoted from "OlimilO"

Hello OlimilO and thanks for your answer.

I haven't used sqlite3 with VB6 succesfully, but I do have use it succesfully with Rapid-Q and FreeBasic who also use stdcall (at least I think...).

I agree that using the callback function is rather difficult, so I don't use the sqlite_exec() function for SELECT queries. I use sqlite3_get_table() instead. I have also made a sort of "wrapper" dll in FreeBasic to acquire the result delimited data string from sqlite3_get_table(), as well as the number of rows and columns, without having to pass pointers as parameters. It works well with FreeBasic-compiled programs, but it fails to give the data string in Rapid-Q and VB6. In the latter cases I can only get the number of rows and columns. In Jabaco, I only get JRE error, concerning the sqlite3.dll module, not my own dll.

Greetings,

Frank

This post has been edited 1 times, last edit by "frankp" (Jan 29th 2009, 7:02pm)

Go to the top of the page

Manuel

Administrator

Posts: 255

Location: Erlangen, Germany

Occupation: Software Developer

4

Thursday, January 29th 2009, 7:49pm

SQLiteJDBC is a Java JDBC driver for SQLite. You should wait for the next Jabaco version. The current beta couldn't load this library.

Go to the top of the page

maXim

Beginner

Posts: 26

Location: Sesto Fiorentino, Florence (ITALY)

Occupation: design & development (HW, SW & FW)

5

Tuesday, February 3rd 2009, 10:01am

I found this wrapper: http://www.ch-werner.de/javasqlite/. Provides no error! Someone knows how to manage it properly in Jabaco?

Massimo

Go to the top of the page

Manuel

Administrator

Posts: 255

Location: Erlangen, Germany

Occupation: Software Developer

6

Tuesday, February 3rd 2009, 10:57pm

Hmm... seems simple. The only thing you have to do is copying the files from javasqlite-20081006-win32.zip into your JRE-folder (the structure is already correct in the zip-file). Now you have installed the SQLite driver for JDBC and you can use it like all other JDBC-compatible databases:

Jabaco Source

On Error Resume Next
Dim database As New DataBase 
Dim driver As Class
driver = Class.forName("SQLite.JDBCDriver")
If driver = Nothing Then MsgBox "Not found!"
On Error Goto 0
Call database.Connect("jdbc:sqlite:/:memory:")

Go to the top of the page

maXim

Beginner

Posts: 26

Location: Sesto Fiorentino, Florence (ITALY)

Occupation: design & development (HW, SW & FW)

7

Wednesday, February 4th 2009, 12:09pm

Hi Manuel,

thanks for Your help, now I work with SQLite but I would also like to populate a JBGrid: do You have to submit me some example?

Go to the top of the page

frankp

Beginner

Posts: 14

8

Thursday, February 5th 2009, 6:52pm

Quoted from "Manuel"

And then what? How can I open an sqlite3 database, e.g. "mydata.db" , execute an SQL statement and/or get some rows in a grid?

The question may sound naive, but after all, I am just a BASIC programmer, not a Java one...

Thanks

Go to the top of the page

maXim

Beginner

Posts: 26

Location: Sesto Fiorentino, Florence (ITALY)

Occupation: design & development (HW, SW & FW)

9

Thursday, February 5th 2009, 7:40pm

with the help of Manuel I would have understood how to use JavaSQLite wrapper in Jabaco, what follows is just a simple example to read the fields names:

Jabaco Source

Dim dbFile As String 
dbFile = App.Path & "/test.db" 
dbFile = Replace(dbFile, "\", "/") 
Dim driver As Class 
driver = Class.forName("SQLite.JDBCDriver") 
If driver = Nothing Then 
    MsgBox "SQLite Driver Not Found!" 
Exit Sub 
End If 
Dim database As New DataBase 
Dim rs As ResultSet 
Dim rsMetaData As java#sql#ResultSetMetaData 
database.Connect("jdbc:sqlite:/" & dbFile) 
rs = database.ExecuteStatement("SELECT * FROM country ORDER BY iso_code") 
rsMetaData = rs.getMetaData 
Dim ColumnName(1 To rsMetaData.getColumnCount) AS String 
Dim f As Integer 
For f = 1 To rsMetaData.getColumnCount 
    ColumnName(f) = rsMetaData.getColumnName(f) 
Next f 
rs.close

... the problem is that I do not know what methods and properties to fill a JBGrid with data from a Select!

Go to the top of the page

frankp

Beginner

Posts: 14

10

Thursday, February 5th 2009, 7:56pm

Perhaps this helps?

Jabaco Source

rs = Database1.ExecuteStatement("SELECT * FROM Contacts") 
Do While res.next 
JBGrid1.TextMatrix(res.getRow() - 1, 0) = rs.getString("RecordId") 
JBGrid1.TextMatrix(res.getRow() - 1, 1) = rs.getString("Title") 
JBGrid1.TextMatrix(res.getRow() - 1, 2) = rs.getString("FirstNAME") 
JBGrid1.TextMatrix(res.getRow() - 1, 3) = rs.getString("MiddleName") 
JBGrid1.TextMatrix(res.getRow() - 1, 4) = rs.getString("LastName") 
rows = rows + 1 
JBGrid1.Rows = rows 
Loop

Do you know btw what should I include in Project/References?

Go to the top of the page

Manuel

Administrator

Posts: 255

Location: Erlangen, Germany

Occupation: Software Developer

11

Thursday, February 5th 2009, 8:10pm

I'm too late but i won't trash my sample...

Quoted

Do you know btw what should I include in Project/References?

Nothing. Just "install" the driver by copying the files from the zip into your Java (JRE)-folder.

Quoted

... the problem is that I do not know what methods and properties to fill a JBGrid with data from a Select!

I'll show you a better way in future. For now you could use this sample:

Jabaco Source

On Error Goto ERROUT

   Dim driver As Class
   driver = Class.forName("SQLite.JDBCDriver")
   If driver = Nothing Then MsgBox "Not found!"

   Call database1.Connect("jdbc:sqlite:/:memory:")
   
   Call database1.ExecuteStatement("CREATE TABLE user (id Integer PRIMARY KEY, name varchar(10) NOT NULL, desc varchar(30) NOT NULL)")
   Call database1.ExecuteStatement("INSERT INTO user (id, name, desc) VALUES (1, 'manuel', 'developer')")
   Call database1.ExecuteStatement("INSERT INTO user (id, name, desc) VALUES (2, 'frankp', 'developer')")
   Call database1.ExecuteStatement("INSERT INTO user (id, name, desc) VALUES (3, 'maxim', 'developer')")
   Call database1.ExecuteStatement("INSERT INTO user (id, name, desc) VALUES (4, 'david', 'supporter')")
   
   Dim rsAllUsers As java#sql#ResultSet
   rsAllUsers = database1.ExecuteStatement("SELECT * FROM user")

   Dim i As Integer
   Dim rsAllUserColCount = rsAllUsers.getMetaData.getColumnCount
   JBGrid1.Cols = rsAllUserColCount
   For i = 1 To rsAllUserColCount
  	JBGrid1.Header(i-1) = rsAllUsers.getMetaData.getColumnName(i)
   Next
   
   Do While rsAllUsers.next
  	For i = 1 To rsAllUserColCount
     	JBGrid1.TextMatrix(rsAllUsers.getRow() - 1, i - 1) = rsAllUsers.getString(i)
  	Next
  	rows = rows + 1
  	JBGrid1.Rows = rows
   Loop

   Exit Sub
ERROUT:
   MsgBox Err.toString

Go to the top of the page

maXim

Beginner

Posts: 26

Location: Sesto Fiorentino, Florence (ITALY)

Occupation: design & development (HW, SW & FW)

12

Thursday, February 5th 2009, 8:48pm

Thanks Manuel! :thumbsup:

Go to the top of the page

frankp

Beginner

Posts: 14

13

Friday, February 6th 2009, 8:51am

Thank you both, Manuel and MaXim,

Frank

Go to the top of the page

Sbleck

Beginner

Posts: 13

Location: Santos, Brazil

Occupation: Consultant

14

Wednesday, June 23rd 2010, 10:28pm

Found some compilation problems when tried the SQLite sample code...

Hello,

Quoted from "Manuel"

I'm too late but i won't trash my sample...

Quoted

Do you know btw what should I include in Project/References?

Nothing. Just "install" the driver by copying the files from the zip into your Java (JRE)-folder.

Quoted

... the problem is that I do not know what methods and properties to fill a JBGrid with data from a Select!

I'll show you a better way in future. For now you could use this sample:

Jabaco Source

On Error Goto ERROUT 

Dim driver As Class 
driver = Class.forName("SQLite.JDBCDriver") 
If driver = Nothing Then MsgBox "Not found!" 

Call database1.Connect("jdbc:sqlite:/:memory:") 

Call database1.ExecuteStatement("CREATE TABLE user (id Integer PRIMARY KEY, name varchar(10) NOT NULL, desc varchar(30) NOT NULL)") 
Call database1.ExecuteStatement("INSERT INTO user (id, name, desc) VALUES (1, 'manuel', 'developer')") 
Call database1.ExecuteStatement("INSERT INTO user (id, name, desc) VALUES (2, 'frankp', 'developer')") 
Call database1.ExecuteStatement("INSERT INTO user (id, name, desc) VALUES (3, 'maxim', 'developer')") 
Call database1.ExecuteStatement("INSERT INTO user (id, name, desc) VALUES (4, 'david', 'supporter')") 

Dim rsAllUsers As java#sql#ResultSet 
rsAllUsers = database1.ExecuteStatement("SELECT * FROM user") 

Dim i As Integer 
Dim rsAllUserColCount = rsAllUsers.getMetaData.getColumnCount 
JBGrid1.Cols = rsAllUserColCount 
For i = 1 To rsAllUserColCount 
JBGrid1.Header(i-1) = rsAllUsers.getMetaData.getColumnName(i) 
Next 

Do While rsAllUsers.next 
For i = 1 To rsAllUserColCount 
JBGrid1.TextMatrix(rsAllUsers.getRow() - 1, i - 1) = rsAllUsers.getString(i) 
Next 
rows = rows + 1 
JBGrid1.Rows = rows 
Loop 

Exit Sub 
ERROUT: 
MsgBox Err.toString

I downloaded the file (http://www.ch-werner.de/javasqlite/javas…81006-win32.zip) and have put the contents in the appropriate folders (Bin and in Java\Jre6\lib\ext), as described in other place, here in the forum.

But couldn´t reproduce the connection to the SQLite database, only because in the compilation process showed that there is an error, even when doing a copy-and-paste of the code mentioned above. Could you (or anyone) comment what I´m doing wrong ? I´ve sent the sample code (with the snapshot of the error tha occured), for better analysis. Please comment, when possible.

Regards,
Sven

Sbleck has attached the following file: