SQL Tutorial

[Erel]

The SQL tutorial is available here: Basic4ppc SQL library reference

Please feel free to post questions or comments in this thread.

[agraham]

After all this time using computers I thought it was about time I got up to speed on SQL

I'm afraid there is a circular reference to itself in the "Next page: Getting Started" link at the bottom of the "Getting started with SQLite" page

"Step 3 - Creating the database tables, and Basic Queries" says "There must be a DataReader object in you application ready to be assigned to, but there is no need to instantiate it as it will receive a previously instantiated object from the ExecuteReader Sub". Accordingly the example code fails to New the Reader and therefore fails!

Sorry, it looks like I'm being picky but in the same code "Connection.EndTransaction" in Sub CreateTableIfNotExists fails as there is no matching BeginTransaction.

EDIT:- The section on deployment needs updating to cover the new structure of the device SQLite dlls.

[Erel]

Thanks agraham. Will be fixed soon.

[BjornF]

Thank you for the tutorial Erel.

I'm wondering about the "Creating_tables" part more specifically about:

Sub CreateTableIfNotExists

   'Find all the tables in this database

   Command.CommandText = "SELECT name FROM sqlite_master WHERE type = 'table' AND name='t_orders'"

   'instantiate reader

   Reader.New1

   'Fill reader

   Reader.Value = Command.ExecuteReader

   If Reader.ReadNextRow = True Then

      ' No table with this name in the database.

I understand that you do a search / select on the database, but if no table with "t_orders" exists in that database, doesn't the Reader.ReadNextRow return False? I.e. shouldn't the last code line say
"If Reader.ReadNextRow=False Then "

or have I misunderstood something?

all the best / Björn

[Erel]

Let me forward your 'thank you' to Ariel Zamir who wrote this tutorial

You are correct. It should be false instead of true. Apparently it was fixed in the source code but not in the documented code.
We will fix it.

[Ariel_Z]

Agraham and BjornF

I would like to thank you – each personally – for the time and attention. I greatly appreciate your comments and corrections. It is very important to have high quality tutorials and of course you are not being, as Agraham wrote, picky... Your corrections have been corrected hours after you wrote them. I also found some spelling mistakes at the pages Agraham referenced – they are fixed now. I’m looking forward for additional corrections.

Thank you,
Ariel

[Caravelle]

I can't believe I'm the first to notice odd behaviour in the tutorial example - but then maybe I'm the only one who thinks it's odd.

When you select a record on the list screen and click Details, you get the edit screen. When you amend the existing record and click confirm, the table record is duly (and invisibly) amended, no problem. But the edit screen closes and reveals the list screen where the record you just amended is shown in its original unamended state. Surely this screen should be updated before it is shown again? Instead you have to go back to the main screen and then return to the list screen again - and the amended record is now correctly shown. Until you discover this, you keep trying to amend the record and think you have failed every time...

I have based a small program on this example and naturally it does the same thing. I can't quite work out yet how to refresh the table before it is shown again, so any tips would be very helpful. "table1.refresh" at the end of the update sub doesn't make any difference.

Here are a couple of tips from me: avoid using the names "from" and "to" as field names in sqlite - any CommandTexts including them will doubtless fail, as mine did. I had to settle for "pod" and "poa", for point of departure and point of arrival. My table is simply a list of flights I have taken, a sort of flying log book, but the program wil be used as a template for several others.

I also simplified the program to a degree, using a global array to hold the fields of the current record and loops to add and set command parameters; I omitted the sub to create a table if none exists, because my tables will always exist - I use tksqlite to create my tables and import the data from csv files. And I shall probably also store field names and so on in arrays, to make it easier to use the same program with other tables.

Thanks

Caravelle

[Ariel_Z]

It is odd and it will be fixed (in a while, I guess a few weeks) along with some other amendments I should add to this tutorial. As for the "check if table exists" procedure, I'll add a comment indicating this can be easily achieved your way. It is important to know how to do it for the completeness of the program.

[Caravelle]

Thanks Ariel.

I completely agree that it is useful to show how to create a table if none exists, just as it is useful to show how to use the @parameters. That is what example programs are for! I am not the sort of user who needs to fear SQL injection attacks on my PDA or desktop, but the techniques are handy to know.

I found it useful to use completely different variables in the Add and Update subs. I have 9 fields (including the UID), so I called the @parameters p0 to p8 and the sub parameter field values f0 to f8. The table name in the SQL query is also a global variable set in the Globals sub and I am working on using an array for the field names also, and ceating the query string with a For...To...Next loop. This could make the subs almost completely self contained and re-usable without amendment. I need to check to see if sqlite also allows reference to fields by column number instead of name in the query string: it ought to, in a sensible world. In the INSERT INTO query, you don't actually need to refer to field names at all, so long as you have the same number of items to insert as the number of columns in the table and put them in the right order.

As you see, your example provides a good basis for learning and experimentation and that is precisely what program examples are for, so thank you. We all have different needs and favourite ways of doing things and if our final result looks completely different, that is no reflection on the value of the original.

Regards

Caravelle

[Caravelle]

As I tend to think while I type, I know I can become confused (and confusing) sometimes. Maybe this code will explain what I've now done better than I can

Sub addRecord
  ' row is a global array containing the row data from the editing screen
  ' col is a global array containing the column names
  ' p1, p2 etc are the command parameters, the number part matching the index of the relevant column in row()
  ' tableName is a global variable
  Connection.BeginTransaction 
    For i = 1 To ArrayLen(row()) - 1
      Command.AddParameter("p" & i)
      Command.SetParameter("p" & i, row(i))
    Next    
    Command.CommandText = "INSERT INTO " & tableName & " VALUES (null, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8)"
    ' nb although a null is seemingly put in the ID column a unique value will be supplied by sqlite
    Command.ExecuteNonQuery        
  Connection.EndTransaction
End Sub

Sub updateRecord
  ' row is a global array containing the row data from the editing screen
  ' col is a global array containing the column names
  ' tableName is a global variable
  Connection.BeginTransaction
    For i = 0 To ArrayLen(row()) - 1
      Command.AddParameter("p" & i)
      Command.SetParameter("p" & i, row(i))
    Next
    ' p is shorthand for string "=@p" - saves confusion about quote marks
    p = "=@p"
    ' q is a temporary holder for Command.CommandText - (q for query string)
    q = "UPDATE " & tableName & " SET "
    For i = 1 To ArrayLen(row()) - 2
      q = q & col(i) & p & i & ", "
    Next
    q = q & col(i) & p & i & " WHERE " & col(0) & "=@p0"    
    Command.CommandText = q
    Command.ExecuteNonQuery    
    Connection.EndTransaction
End Sub

Sub deleteRecord    
  Connection.BeginTransaction
    ' assumes the ID will always be the first column - name stored in col(0), value in row(0)
    ' row is a global array containing the row data from the editing screen
    ' col is a global array containing the column names
    ' tableName is a global variable
    Command.AddParameter("p0")    
    Command.SetParameter("p0" , row(0))
    Command.CommandText = "DELETE FROM " & tableName & " WHERE " & col(0) & "=@p0"
    Command.ExecuteNonQuery        
  Connection.EndTransaction
End Sub

So long as you set up your arrays at the start of your progam, these subs should work for any single table. The arrays could even be filled from an .ini file, which could also set table column widths.

I hope that helps.

Caravelle