SQLite in Emergence BASIC

[tekrat]

I've generated the sqlite3.lib file and can import function fine.  But the sqlite3_open, sqlite3_open16, and sqlite3_close are expecting an object with a type definition of "sqlite3".  In C/C++ you can do:

typedef struct sqlite3 sqlite3;

How can I setup an object with the sqlite3 object's type definition?

Thanks in advance

Additional Information:
I thought I would add the place I'm getting the SQLite information from:
http://www.sqlite.org/capi3ref.html

[sapero]

If 'sqlite3' is declared only here, or always in this form, define it as pointer, integer, or what you prefer, using typedef sqlite3 pointer.

[tekrat]

I think I've found the proper question to ask for the problem I'm having:

Can you import data structures from a C++ created DLL into Emergence BASIC? 

The SQLite DLL has 6 or 7 different structures built into it with sqlite3 being the most common used.  So I need to import that structure in something that will let me us it in Emergence BASIC and pass it back to the variable as needed.

I'll be glad to share my include file with anyone who wants it, when I get it working.

[pistol350]

I may have not understood your question well,
but to me the first step seems to create an import library from your DLL using Emergence's tools option.
Cheers!
Peter

[tekrat]

Ok, I've done that and have written the following code:

Code Select Expand

$use "sqlite3.lib"

DECLARE IMPORT,sqlite3_open(filename:string,sqlite3:sqlite3),int
DECLARE IMPORT,sqlite3_open16(filename:string,sqlite3:sqlite3),int

sqlite3_open("sqlitetest.db", sqlite3)


When I compile I get this error:

Code Select Expand

Compiling...
sqlite3.eba
File: C:\Program Files\EBDev\projects\sqlite3.eba (6) undefined variable - sqlite3 - )
File: C:\Program Files\EBDev\projects\sqlite3.eba (6) no appropriate conversion exists - )
Error(s) in compiling "C:\Program Files\EBDev\projects\sqlite3.eba"


When I try to define a variable with this code:

Code Select Expand

$use "sqlite3.lib"

EXTERN sqlite3:sqlite3

DECLARE IMPORT,sqlite3_open(filename:string,sqlite3:sqlite3),int
DECLARE IMPORT,sqlite3_open16(filename:string,sqlite3:sqlite3),int

sqlite3_open("sqlitetest.db", sqlite3)

I get these errors:

Code Select Expand

Compiling...
sqlite3.eba
File: C:\Program Files\EBDev\projects\sqlite3.eba (6) unknown type - sqlite3
File: C:\Program Files\EBDev\projects\sqlite3.eba (8) undefined variable - sqlite3 - )
File: C:\Program Files\EBDev\projects\sqlite3.eba (8) no appropriate conversion exists - )
Error(s) in compiling "C:\Program Files\EBDev\projects\sqlite3.eba"

By what you said earlier 'sqlite3' should have defined as a datatype in the sqlite2.lib library.

Please forgive my ignorance; most of professional life has been build GUI's for this database or that database.  I was hoping to use this for building an RSS reader with advanced search capabilities.  The reasons why I select SQLite and eBasic is that Accesses' speed and file size limitation just wasn't cutting it for me. VB6 has ran its life span and has way to many dependencies for me.  .NET is even worse and slow.  Freebasic just isn't there yet.  eBasic is an expansion of an already time tested product.  I've used SQLite in VB6 with a great degree of success and I believe if we could come with a working class or helper DLL for eBasic a lot of people would find it very useful and expand the eBasic universe.

[LarryMc]

I'm just making a guess but try adding this on the next line after the $use directive

Code Select Expand

extern sqlite3:sqlite3
since it is defined externally to the source file you're trying to compile.
It would look like this:

Code Select Expand

$use "sqlite3.lib"

EXTERN sqlite3:sqlite3
DECLARE IMPORT,sqlite3_open(filename:string,sqlite3:sqlite3),int
DECLARE IMPORT,sqlite3_open16(filename:string,sqlite3:sqlite3),int

sqlite3_open("sqlitetest.db", sqlite3)

I'm really not sure about having the same name on both sides of the ":".

Larry

[tekrat]

I gave it shoot with what you said.  Here the results:

Code Select Expand

$use "sqlite3.lib"

EXTERN sqlite3

DECLARE IMPORT,sqlite3_open(filename:string,sqlite3),int
DECLARE IMPORT,sqlite3_open16(filename:string,sqlite3),int

sqlite3_open("sqlitetest.db", sqlite3)
and got this

Code Select Expand

Compiling...
sqlite3.eba
File: C:\Program Files\EBDev\projects\sqlite3.eba (3) unknown type - sqlite3
File: C:\Program Files\EBDev\projects\sqlite3.eba (5) syntax error - )
File: C:\Program Files\EBDev\projects\sqlite3.eba (6) syntax error - )
File: C:\Program Files\EBDev\projects\sqlite3.eba (8) Warning: undeclared function 'sqlite3_open' - )
File: C:\Program Files\EBDev\projects\sqlite3.eba (8) undefined variable - sqlite3 - )
Error(s) in compiling "C:\Program Files\EBDev\projects\sqlite3.eba"

I then tried it this way:

Code Select Expand

$use "sqlite3.lib"

EXTERN sqlite3:sqlite3

DECLARE IMPORT,sqlite3_open(filename:string,sqlite3:sqlite3),int
DECLARE IMPORT,sqlite3_open16(filename:string,sqlite3:sqlite3),int

sqlite3_open("sqlitetest.db", sqlite3)

and got this

Code Select Expand

Compiling...
sqlite3.eba
File: C:\Program Files\EBDev\projects\sqlite3.eba (3) unknown type - sqlite3
File: C:\Program Files\EBDev\projects\sqlite3.eba (8) undefined variable - sqlite3 - )
File: C:\Program Files\EBDev\projects\sqlite3.eba (8) no appropriate conversion exists - )
Error(s) in compiling "C:\Program Files\EBDev\projects\sqlite3.eba"


[sapero]

tekrat, try this. It works

Code Select Expand

$use "sqlite3.lib"
typedef sqlite3 pointer
typedef sqlite3_stmt pointer
DECLARE CDECL IMPORT,sqlite3_open(filename:string, ppsqlite3:pointer),int
DECLARE CDECL IMPORT,sqlite3_exec(db:sqlite3, zSql:string,sqlite3_callback:int, pArg:pointer,ppzErrMsg:pointer),int
DECLARE CDECL IMPORT,sqlite3_close(db:sqlite3)
DECLARE CDECL IMPORT,sqlite3_prepare(db:sqlite3, query:string, nBytes:int, ppsqlite3_stmt:pointer, pppzTail:pointer),int
DECLARE CDECL IMPORT,sqlite3_step(stmt:sqlite3_stmt),int
DECLARE CDECL IMPORT,sqlite3_column_count(stmt:sqlite3_stmt),int
DECLARE CDECL IMPORT,sqlite3_finalize(stmt:sqlite3_stmt),int
DECLARE CDECL IMPORT,sqlite3_column_text(stmt:sqlite3_stmt, iCol:int),pointer
const SQLITE_ROW = 100

sqlite3      db
sqlite3_stmt stmt
pointer pStr : settype pStr,string

sqlite3_open(GetStartPath()+"sqlitetest.db", &db)

if (0=sqlite3_exec(db, "CREATE TABLE files (name TEXT,date INTEGER)", 0, 0, 0))
' insert only once
sqlite3_exec(db, "INSERT INTO files VALUES('sqlite.eba', '20070619')", 0, 0, 0)
sqlite3_exec(db, "INSERT INTO files VALUES('readme.htm', '20061113')", 0, 0, 0)
sqlite3_exec(db, "INSERT INTO files VALUES('zdemo3.txt', '20060628')", 0, 0, 0)
sqlite3_exec(db, "INSERT INTO files VALUES('acpars.exe', '20060210')", 0, 0, 0)
endif

' ---------------- query 1 -------------------------
if (0= sqlite3_prepare(db, "SELECT * FROM files", -1, &stmt, 0))

while SQLITE_ROW = sqlite3_step(stmt)
pStr = sqlite3_column_text(stmt, 1)
print *pStr, " ",
pStr = sqlite3_column_text(stmt, 0)
print *pStr
wend

sqlite3_finalize(stmt)
print "---------------------"
endif
' ---------------- query 2 -------------------------
if (0= sqlite3_prepare(db, "SELECT * FROM files WHERE date<20070101", -1, &stmt, 0))

while SQLITE_ROW = sqlite3_step(stmt)
pStr = sqlite3_column_text(stmt, 1)
print *pStr, " ",
pStr = sqlite3_column_text(stmt, 0)
print *pStr
wend

sqlite3_finalize(stmt)
print "---------------------"
endif


sqlite3_close(db)

print "press enter": input(db)

[LarryMc]

Sapero,
You did it again.

From now on I think instead of posting questions on the forum that I'll just email them to you.

Larry

[REDEBOLT]

Sapero,
You did it again.

From now on I think instead of posting questions on the forum that I'll just email them to you.

Larry

Please don't.  We want to learn too.

[tekrat]

Thank you so much!  When get home I'll start ripping the SQLite functions into an eBasic include. 

Thanks again to all!!!

[pistol350]

Please don't.  We want to learn too.

LOL
We had the same thought !

[LarryMc]

just kidding but we do know who can answer most of our questions (besides Paul, of course)

[JoaoAfonso]

Hello!

I've asked something like this long ago and in other forum, but I still have this doubt.

I'm constructing a program that needs much information from a database, which is added constantly. Appart that database growing up very quickly, I still need to do queries and have fast answers. And for last, I want to do it all with IB std, which I believe I can code well, or EB, that Im still a newbie but I can see it's even more easy to do it.

A DB with 20Mb can work as fast as a 1Mb DB? I've heard yes, it can work as fast, but only if I map correctly databases. But need to know it for sure before change all my program to this new structure

A comparison, to see if I am thinking well, might be ionic wind forum, right? all posts, all users, all info is saved at just one database, which with almost 17k posts must be huge. Anyway works fast each time we want to open a topic inside a forum inside another forum. Am I thinking correctly?

Thank you in advance

[Ionic Wind Support Team]

This forum, along with others use MySQL and PHP.  MySQL is very fast with relational databases with multiple tables.

You can use MySQL in EB or Aurora by downloading their ODBC drivers and connect to any MySQL database using the built in commands.

[JoaoAfonso]

Hmm... I do not need to know anything about mysql, right? built in eb commands do it all, thats true (they have create database, add columns to database, add data to database...)?
I might be confusing things, but aren't those programs with declarations of sqlite using mysql (a free dll)?

Thanks! I guess I'll start thinking seriously about change all structure to this.

[Ionic Wind Support Team]

No SQLite is a completey different beast.  Using the OBDC driver you can use the same code base for any database format.  And yes you would use the built-in DB commands.

Paul,

[Dogge]

I'm using PostgreSQL with EBasic via the built-in ODBC. It works great, good performance, very flexible and as is is an object-relation database it has the full capabilities of a normal SQL database like MySQL but also additional object-oriented functionality like inheritance.
You can find more information at http://www.postgresql.org/
/Douglas

[JoaoAfonso]

Can someone paste here an example code, and files and operations needed to make it work?
SQLite seems just need a dll and type the correct declares in my program... seems easier.
Thanks in advance

[GJ]

Douglas do you have a small Ebasic example using Postgresql ? At the moment i'm still using Cheetah2 and happy with that, but it can never harm to compare 


Thanks in advance,


Gertjan

[Dogge]

Sure,my routines are part of a larger solution and are rather generic, they get included into my programs.

Code Select Expand


...
pointer MyDatabase
MyDatabase=Connect2Postgres(MyWin)
....
sub Connect2Postgres(_callingWin as window),pointer
def _return as int
def _hDatabase as pointer
_hDatabase = dbConnectDSN("MyDatabase","")
if _hDatabase=NULL
messagebox(_callingWin,"Could not connect to the database","MyProg - ERROR",@mb_ok|@mb_iconexclamation)
endif
return _hDatabase
endsub
'------------------------------------------------------------------------------------------------------------------------------------------------
sub DisconnectFromPostgres(_hDatabase as pointer)
dbDisconnect(_hDatabase)
return
endsub


Standard ODBC connect/disconnect stuff basically from the documentation.

Here is a routine for authenticating a user towards my users table in the database

Code Select Expand


string SQLstmt
SQLstmt="SELECT userid FROM users WHERE usernamelower=lower('"+Username+"') AND userpassword='"+Password+"'"
int _userOK : _userOK=false
_userOK=AuthenticateUser(MyDatabase, SQLstmt, MyWin)
if _userOK<>0 then


Sub AuthenticateUser(_hDatabase as pointer, _sql as string, _callingWin as window),int
def _hStmt, _bindNo, _userID as int
_userID=0
_hStmt=dbExecSQL(_hDatabase,_sql)
if _hStmt
_bindNo=1
dbBindVariable(_hStmt, _bindNo, _userID)
dbGet(_hStmt)
endif
dbFreeSQL(_hStmt)
return _userID
EndSub


and a simple update
please note that this is taken out of context from a larger application

Code Select Expand


istring _sqlUU[1024]

_sqlUU="Update users Set "
if (_updateCode & 1) = 1 then _sqlUU+="firstname='"+rtrim$(left$(_newFirstname,40))+"',"
if (_updateCode & 2) = 2 then _sqlUU+="lastname='"+rtrim$(left$(_newLastname,40))+"',"
if (_updateCode & 4) = 4 then _sqlUU+="address1='"+rtrim$(left$(_newAddress1,40))+"',"
if (_updateCode & 8) = 8 then _sqlUU+="address2='"+rtrim$(left$(_newAddress2,40))+"',"
if (_updateCode & 16) = 16 then _sqlUU+="addrcity='"+rtrim$(left$(_newCity,40))+"',"
if (_updateCode & 32) = 32 then _sqlUU+="addrzipcode='"+rtrim$(left$(_newZipcode,40))+"',"
if (_updateCode & 64) = 64 then _sqlUU+="addrcountry='"+rtrim$(left$(_newCountry,40))+"',"
if (_updateCode & 128) = 128 then _sqlUU+="phone1='"+rtrim$(left$(_newPhone1,40))+"',"
if (_updateCode & 256) = 256 then _sqlUU+="phone2='"+rtrim$(left$(_newPhone2,40))+"',"
if (_updateCode & 512) = 512 then _sqlUU+="birthdate='"+Date2String(_newBirthdate)+"',"
_sqlUU=left$(_sqlUU,len(_sqlUU)-1) /* trim off the last comma */
_sqlUU+=" Where  userid="+ltrim$(str$(_userIDUU))+" And userpassword='"+rtrim$(left$(_currPWUU,20))+"'"

_result=RunUpdateSQL4Me(MyDatabase, _sqlUU, MyWin)
...

'-----------------------------------------------------------------------------------------------------------------------------------
Sub RunUpdateSQL4Me(_hDatabase as pointer, _sql as istring[1024], _callingWin as window),int
/* This routine is only used for complete statements that does not need to bind to parameters */
int _return, _hStmt, _bindNo, _sqlres
string _errorCode, _errorText
_hStmt=dbExecSQL(_hDatabase,_sql)
if _hStmt=false
_errorCode=dbGetErrorCode(_hStmt)
_errorText=dbGetErrorText(_hStmt)
MessageBox(_callingWin,"Error calling the Database.\n"+_sql+"\nError Code:"+_errorCode+"\nError Text: "+_errorText,"Database ERROR", @mb_ok|@mb_iconexclamation)
_return=false
else
_return=true
endif
dbFreeSQL(_hStmt)
Return _return
EndSub



Basically I think one should select a database based on what it should be used for. I need it for both application and access from web which basically rules out all small single user databases or DLL based databases. ODBC and a database backend that can be on either windows or Linux gives me some freedom for the future.

I hope it is useful

/Douglas

[JoaoAfonso]

For me it is for sure. I am somewhat getting used to databases now. Thank you

[GJ]

Douglas, thank you for the ebasic examples. Because i use cheetah2 with the wrapper and still needing a dll for access to the database, i could have used the build in database functions in Ebasic. If i want to get rid of cheetah2 ( dont think so at this moment), i'm wondering if Postgresql was an option . At my work they also using it in a Linux environment, but i really have to learn a lot making it work with Windows... Anyway i will study the examples and try it out with Postgresql   

Gertjan

[Dogge]

My pleasure, glad if I can give something back to the community.
/Douglas