SQLitening Support Forum

SQLitening Support => You've got Questions? We've got Answers! => Topic started by: Fredrick Ughimi on April 16, 2008, 06:34:42 am

Title: Newbie - Starting out with SQLite Client/Server
Post by: Fredrick Ughimi on April 16, 2008, 06:34:42 am
Hello,

Been waiting to get to this point for a while now - An opportunity to start using SQLite Client/Server. Just landed a contract to develop an inventory program for a small pharmacy outfit. I would be asking some basic questions as I move along. Hope you would be patient with me.  ;D

First of, I am having problems with Adding table/index in my database. I get error 1. Here is my code snippet: Mostly Paul's sample codes.



sDatabase = "PharmacyProDB.db3"
   
       StartAgain:
     
       sql3_StartSession hSession, 0   ' do not use Unicode support

       sSQL = "SERVER = localhost; PORT = default; UID = pss234; PASSWORD = sqlrocks"
       errcode = sql3_connect( hSession, sSQL )
       If errcode Then
          MsgBox "Error:" & Str$(errcode) & " during sql3_connect.":  GoTo ExitOut
       End If
       
       errcode = sql3_Use( hSession, sDatabase, %SQL3_REMOTE)
       If errcode Then
          MsgBox "Error:" & Str$(errcode) & " during sql3_use.":  GoTo ExitOut
       End If
   
       sSQL = "CREATE TABLE [tblLogin] ( "   & _
               "[Username] Text Primary Key, "  & _
               "[Password] Text, "          & _
               "[Status] Text; "     & _
       "Create INDEX [Usernamendx] On [tblLogin] ([Username]);"               
       errcode = sql3_exec( hSession, sSQL )
       If errcode Then
          MsgBox "Error:" & Str$(errcode) & " during sql3_exe (Adding table/index).":  GoTo ExitOut
       End If

      ExitOut:

       If hSession Then
          If rs Then sql3_rsDestroy hSession, rs
          sql3_Disconnect hSession
          sql3_EndSession hSession
       End If
   


From the sql3routine file it says error 1 is means - "SQL error or missing database". Hmm... I also get the same error when I run the sample_create.bas file.

Best regards,
Title: Re: Newbie - Starting out with SQLite Client/Server
Post by: David L Morris on April 16, 2008, 08:32:51 am
I am getting error 1 when using the latest SQLite3.dll (423kb) where earlier version (356kb) works OK with Paul's Testclient.exe.  I am about to download Paul's latest sql3_cs_0010 examples to try out.

Good luck
Title: Re: Newbie - Starting out with SQLite Client/Server
Post by: Paul Squires on April 16, 2008, 08:43:50 am
You are missing a closing parenthesis ")" in your CREATE statement right before the semicolon ";" that separates your CREATE TABLE statement from your CREATE INDEX statement.

The correct SQL would be:


   sSQL = "CREATE TABLE [tblLogin] ( " & _
   "[Username] Text Primary Key, " & _
   "[Password] Text, " & _
   "[Status] Text); " & _
   "Create INDEX [Usernamendx] On [tblLogin] ([Username]);"

   errcode = sql3_exec( hSession, sSQL )
   If errcode Then
      MsgBox "Error:" & Str$(errcode) & " during sql3_exe (Adding table).": GoTo ExitOut
   End If

Title: Re: Newbie - Starting out with SQLite Client/Server
Post by: Paul Squires on April 16, 2008, 09:44:33 am
The "testclient.exe" program may indeed have been broken because at the time I was doing a lot of changes to the "sql3client.dll" that provides the interface between the user's program, the sqlite dll, and the server. I was also starting work on the username/password validation code in the server for the sql_connect. Having said that, I am attaching a new "testclient.exe" to this post that should work correctly.


[attachment deleted by admin]
Title: Re: Newbie - Starting out with SQLite Client/Server
Post by: Fredrick Ughimi on April 16, 2008, 06:11:10 pm
Hello Paul,

>>You are missing a closing parenthesis ")" in your CREATE statement right before the semicolon ";" that separates your CREATE TABLE statement from your CREATE INDEX statement.

Thank you for point out the error. The first time I ran the program after I made the correction the error 1 disappeared. Subsequent runs still produced error 1.

Hi David,

>>I am getting error 1 when using the latest SQLite3.dll (423kb) where earlier version (356kb) works OK with Paul's Testclient.exe.

I did try the earlier version (356kb), I still got error 1. Thank you for your response.

Please, can someone try running sample_create.bas file, I still get error 1 running it.

Best regards,







Title: Re: Newbie - Starting out with SQLite Client/Server
Post by: Paul Squires on April 16, 2008, 06:40:40 pm
Well, if you re-run your example then doesn't the database already exist at that point??? Therefore the error code produced on the second run is correct. Try doing a check for the existance of the database and if it does not exist then do the CREATE TABLE and CREATE INDEX. If the database already exists then simply bypass the create.
Title: Re: Newbie - Starting out with SQLite Client/Server
Post by: Fredrick Ughimi on April 16, 2008, 08:00:26 pm
Hello Paul,

I get what you mean. Did that. Same error. This error occurs when I am trying to add a table/index into the database. Now if I do this:


If Dir$(sDatabase) = "" Then
       sDatabase = "PharmacyProDB.db3"
End If


This only test for the existence of the database, not the table embedded in it. Is their a way to test for the existence of a table in a database prior to creating the table?

Hmm... I think I would just bypass creating the database, table and index with codes and let SQLite Expert do those for me and see how it goes from there.

Best regards,





Title: Re: Newbie - Starting out with SQLite Client/Server
Post by: Paul Squires on April 16, 2008, 08:19:52 pm
Yes, there are ways to check for the existence of a Table but the "client" application should not have to do this. You probably should not give the client program the opportunity to send creates to the server. I prefer your approach to create the database/tables/indexes outside of the program and simply put that database in the same directory as the SQLite3 server. The client application should really only have to deal with adding, modifying, removing records.... altering the database/table structure should be done on the server either by a dedicated application like SQLite Expert or a custom developed application specific to your program. Doing this gives you a layer of security because you can restrict who has access to the file server and the actual database. Try not to give your client applications too much power. The database administration should hold all the power to handle the critical database related functions.
Title: Re: Newbie - Starting out with SQLite Client/Server
Post by: Paul Squires on April 16, 2008, 08:29:06 pm
FYI, to test for the existence of a Table you would query the sqlite_master table for the table name. If the query returns any records then the table exists. Here is what the SQL query would look like:


   sSQL = "SELECT name FROM sqlite_master WHERE (type='table') AND " & "name='" & sql3_Fix(sTableName) & "'"

Title: Re: Newbie - Starting out with SQLite Client/Server
Post by: Fredrick Ughimi on April 17, 2008, 06:24:16 am
Hi Paul,

Thank you for that piece of code and your advice generally.

Here are my codes for inserting records to a table:


sSQL = "BEGIN IMMEDIATE TRANSACTION; " & _
       "INSERT INTO [tblLogin] ([Username], [Password], [Status])" _
          & "VALUES " & "( '" & sUsername$ & "','" & sPassword$ & "','" & sStatus$ & "')" & _ 
       "COMMIT TRANSACTION;"
   errcode = sql3_exec( hSession, sSQL )
   If errcode Then
      MsgBox "Error:" & Str$(errcode) & " during sql3_exe (Adding record).":  GoTo ExitOut
     
   End If


ExitOut:

       If hSession Then
          sql3_Disconnect hSession
          sql3_EndSession hSession
       End If


With this I get error 1050. Meaning bad session (from Mike Doty's sqlddt.bas) .

What am I doing wrong?

Cheers,


Title: Re: Newbie - Starting out with SQLite Client/Server
Post by: Paul Squires on April 17, 2008, 12:48:21 pm
Did you call sql3_connect ? If you did, then what is the value of hSession after you called it?
Title: Re: Newbie - Starting out with SQLite Client/Server
Post by: Fredrick Ughimi on April 17, 2008, 05:39:31 pm
Hello Paul,

>>Did you call sql3_connect ?

Yes, I did.

>>If you did, then what is the value of hSession after you called it?

I got 2121032.

Best regards,
Title: Re: Newbie - Starting out with SQLite Client/Server
Post by: Paul Squires on April 17, 2008, 06:57:24 pm
Maybe you post all of the sample code you're using so I can trace where the error occurs?

Title: Re: Newbie - Starting out with SQLite Client/Server
Post by: Fredrick Ughimi on April 18, 2008, 04:39:58 am
Hello Paul,

Here are my codes:


#Compile Exe

#Include "sql3client.inc"

Function PBMain() As Long
    Local sSQL As String
    Local hSession As Dword
    Local errcode As Long
    Local sUsername As String
    Local sPassword As String
    Local sStatus As String
    Local sDatabase As String
   
    'Mock values for this sample
    sUsername = "Fredrick"
    sPassword = "Ughimi"
    sStatus = "Administrator"
   
   
    If Dir$(sDatabase) = "" Then
       sDatabase = "EmlenPharmacyProDB.db3"
    End If
       StartAgain:
     
       sql3_StartSession hSession, 0   
       
       sSQL = "SERVER = localhost; PORT = default; UID = pss234; PASSWORD = sqlrocks"
       errcode = sql3_connect( hSession, sSQL )
       'MsgBox "hSeession" & Str$(hSession)
       If errcode Then
          MsgBox "Error:" & Str$(errcode) & " during sql3_connect." :  GoTo ExitOut
       End If
       
       errcode = sql3_Use( hSession, sDatabase, %SQL3_REMOTE)
       If errcode Then
          MsgBox "Error:" & Str$(errcode) & " during sql3_use.":  GoTo ExitOut
       End If
       
       sSQL = "BEGIN IMMEDIATE TRANSACTION; " & _
       "INSERT INTO [tblLogin] ([Username], [Password], [Status])" _
          & "VALUES " & "( '" & sUsername$ & "','" & sPassword$ & "','" & sStatus$ & "')" & _ 
       "COMMIT TRANSACTION;"
       errcode = sql3_exec( hSession, sSQL )
       If errcode Then
          MsgBox "Error:" & Str$(errcode) & " during sql3_exe (Adding record).":  GoTo ExitOut
       End If
         
       ExitOut:

       If hSession Then
          sql3_Disconnect hSession
          sql3_EndSession hSession
       End If


End Function



Interestingly, sample.bas that ships with sql3_cs_0010 produced the same error 1.

Best regards,
Title: Re: Newbie - Starting out with SQLite Client/Server
Post by: Paul Squires on April 18, 2008, 08:28:29 am
This section of code does not make sense:


If Dir$(sDatabase) = "" Then
sDatabase = "EmlenPharmacyProDB.db3"
End If


You are only setting the database name variable when the database does not exist (actually, the value of sDatabase is Null at the time you do the Dir$ test ??). In my case, it did not exist, therefore it set the name and proceeded to try to execute a query on the database. The SQL failed because even though sql3_use created the database, it did NOT create the required table (i.e. tblLogin).

You should create your database using SQLite Expert and then use code like the following to alert the user in situations where the database does not exist or is not installed with your application.


sDatabase = "EmlenPharmacyProDB.db3"
If Dir$(sDatabase) = "" Then
   ' Warn the user and abort
   MsgBox "Oops, database " & sDatabase & " does not exist."
   Goto ExitOut
End If


Title: Re: Newbie - Starting out with SQLite Client/Server
Post by: Fredrick Ughimi on April 18, 2008, 03:48:17 pm
Hello Paul,

>>You are only setting the database name variable when the database does not exist ot exist...

Oops! You are right. Should have done this (sDatabase = "EmlenPharmacyProDB.db3") before testing. The DB actually exist.

>>You should create your database using SQLite Expert and then use code like the following to alert the user in situations where the database does not exist or is not installed with your application.

That is what I did. I created my DB and table using SQLite Expert. Made the correcttion, but the error 1 is still there.

Best regards,


Title: Re: Newbie - Starting out with SQLite Client/Server
Post by: Paul Squires on April 18, 2008, 07:37:02 pm
In your sSQL statement you forgot to separate the "INSERT INTO" statement from the "COMMIT TRANSACTION" with a semi-colon ";".

:)
Title: Re: Newbie - Starting out with SQLite Client/Server
Post by: Fredrick Ughimi on April 19, 2008, 07:21:47 am
Hello Paul,

You are right once again. Its now working. You have Eagle eyes.  ;)

Thank you so much.

Best regards,
Title: Re: Newbie - Starting out with SQLite Client/Server
Post by: Jim Harre on April 22, 2008, 01:45:40 am
I'm working on a simple "Today in History" app based on Patrick Kincaid's "Today" program from yesteryear. There's a ton of data. Using sample_create.bas as a starting point, I got it working building a table from the text data.

But when adding records, I get "Error: 1 during sql3_exec (Adding record)." It seems that single quotes in the text made it unhappy. So, I surrounded the text with double quotes and that worked OK for a few more lines. But it really gets confused when it sees text like this:
   first running of SF's famed "Bay to Breakers" race (7.63 miles)

Anyone have an idea how to prep the text to make it acceptable to SQLite?  ???
Title: Re: Newbie - Starting out with SQLite Client/Server
Post by: Paul Squires on April 22, 2008, 08:39:03 am
Hi Jim,

First off, thanks a million for using the SQLite3 Client/Server code. I really appreciate it. The more people who use this the more incentive it gives me to continue work on it.

You have encountered a common problem with text strings and SQL. When you are trying to store a string with embedded single quotes in it (i.e. SF's) then you need to escape the quote with another quote. The library provides a built in function that does this for you. It is called sql3_fix. To be safe, you should probably wrap all of your text in this function.

For example:


   sString = "first running of SF's famed "Bay to Breakers" race (7.63 miles)"
   sSQL = "BEGIN IMMEDIATE TRANSACTION; " & _
          "INSERT INTO parts (manuf) VALUES ( '" & sql3_Fix( sString ) & "');" & _
          "COMMIT TRANSACTION;"
   errcode = sql3_exec( hSession, sSQL )

Title: Re: Newbie - Starting out with SQLite Client/Server
Post by: Jim Harre on April 22, 2008, 11:56:50 pm
Ahhh, thanks, Paul!

I knew I was missing something. (Well, that and an extra set of single quotes around the text that I forgot.) Your example helped immensely.

When importing text to the database the Total Hit counter really starts spinning - reminds me of a pinball game I used to play...