• Welcome, Guest. Please login.
 
March 30, 2020, 01:17:52 am

News:

Welcome to the SQLitening support forums!


Newbie - Starting out with SQLite Client/Server

Started by Fredrick Ughimi, April 16, 2008, 06:34:42 am

Previous topic - Next topic

Fredrick Ughimi

April 16, 2008, 06:34:42 am Last Edit: April 17, 2008, 05:41:32 pm by Fredrick Ughimi
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,
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

David L Morris

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

Paul Squires

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


Paul Squires

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]

Fredrick Ughimi

April 16, 2008, 06:11:10 pm #4 Last Edit: April 17, 2008, 07:16:02 am by Fredrick Ughimi
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,







Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Paul Squires

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.

Fredrick Ughimi

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,





Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Paul Squires

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.

Paul Squires

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) & "'"


Fredrick Ughimi

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,


Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Paul Squires

Did you call sql3_connect ? If you did, then what is the value of hSession after you called it?

Fredrick Ughimi

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,
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Paul Squires

Maybe you post all of the sample code you're using so I can trace where the error occurs?


Fredrick Ughimi

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,
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Paul Squires

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