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

 

[Ariel_Z]

This helps a lot. I hope when I update the tutorial I will add a link to this thread (there are few ways to get to this tutorial) and point out the different ways to use it. Thank you !

 

[Caravelle]

My program is changing from day to day. I decided to use ArrayLists instead of Arrays since Arrays were giving me problems and ArrayLists can be referenced by Item number without having to be redimensioned. And I found a sub in one of the Forums here which reads the field names from the table structure - so all you need do is allocate the table name to a variable ("tableName" for example), and the rest happens without you having to put any specific field names into your program. You also have an easy way to get the number of columns in the table - it's the "count" of the ArrayList.

I have also managed to persuade the program to save the table column widths into a simple ini file when the main form is closed, and load from file on start-up. I may change this to a more sql-oriented approach and save the widths to a tiny table in the database. And why not?

My next job is to change this line:

...INSERT INTO " & tableName & " VALUES (null, @p1, etc etc...

so that the parameters are added into the string by a loop, as many times as there are columns - less one because the UID column is a null. I know how to do it, I just need to find the time.

Really, the only thing which stops my program from being completely re-usable for working with any single table is the editing form, because the layout and controls used will depend on the type of data, number of fields, length of expected data and so on. Oh, and input error-checking and normalisation will depend on that too. In my "flights" table, everything is text - even the dates.

I am aware that my b4ppc coding may be unconventional. This isn't because I come to it from Delphi, because my Delphi code is unconventional too! I am, basically, self-taught by trial and error - oh and example programs of course, but then I go and change them all beyond recognition

I am going away to the Far East for 2 weeks shortly (and will be putting the CF card jpg transfer program we discussed a while ago through its paces) so development of this little data program will stop for a while - apart from having 7 more flights added to the table .

I have to say I enjoy programming with b4ppc. Most of what I need to do is quite simple, and it all seems to be covered somewhere in the basic program or the additional libraries. And it's such a pleasure to be able to work with sqlite without having to buy a 3rd party add-on that comes with incomprehensible sample programs and help.

:sign0136:
Caravelle

 

[Merlot2309]

Hello,

First of all: my compliments and great thanks for the SQL lib and tutorial.

Loading the database goes soooooooo fast :sign0060:

@Caravelle:
You would do me a real pleasure if you could upload your Insert into code.
I have a db with quite some columns and I actually hope that there is an easier solution for adding and setting parameters to every individual value.

Thank you in advance,

Helen.

 

[Caravelle]

Hi Helen

Your problem - or should I say our problem - is not so much in setting up a parameter driven INSERT routine, as in getting your new data into a structure from which it can be read by a loop.

Assuming you are keying your data in on the device screen, a multi-line text box would be the obvious solution if it was possible to address the lines one by one, but sadly that is not the case. We need something like the Delphi Memo control. So try a loop in which you key your column data into a textbox, as prompted by a label showing the name of the column. Key a "Next" button to add the column data to a listbox and move on to the next column. Key another "Insert" button when done to start another loop which iterates through the items in the listbox - the looping variable is how you reference your parameters under my system - and finally inserts the new row in the database table.

An alternative to the Listbox might be a two-column table component, with the db column names down the left hand side, in Cell 0 of each row - helpful for those like me who forget what they're doing every few seconds. Your data goes in the second column. A table is probably prettier too. There is an editable table component in this Forum somewhere which you could probably use and then dispense with the textbox and Next button.

Bear in mind that if you want specific forms of data cleansing on specific columns, you really do have to take the long-winded approach and code for each column separately. Likewise if you want to pick the data for some of the columns from ComboBoxes, a Calendar or whatever. I believe it is even possible to put controls such as comboboxes into the cells of the editable table, but again, you need to be prepared to program it specifically to deal with specific columns where a different sort of input technique is needed.

Give me a bit of time to come up with some working code.

Caravelle

 

[BjornF]

Dear Caravelle (and Helen),

I might be missing something here, but why can't you use a multiline textbox if you wanted?

You could just read the contents of the textbox into a string and then split the string into database rows by splitting it at each CRLF.

You could then split each line into columns by using whatever arbitrary sign you have decided to be used (e.g. space or ; )

This is of course not a very elegant way of doing it and it does presuppose that you aren't entering a lot of text, but otherwise I think it should work (he said without having tried it)

all the best / Björn

 

[Caravelle]

Hi Bjorn

No, you're not missing anything... Since posting my message I've thought of three even wackier ways of doing this.

For Helen's benefit, once we have the data in some kind of list structure, this is the code:

Sub addRecord
  ' recList is an ArrayList containing the row data from the editing screen
  ' fieldList is an ArrayList containing the field names
  ' tableName is a global variable holding the table name
  ' fields is the total number of fields in the table - you could use  
  ' fieldList.count if preferred, and forget this global variable
  ' command @ parameters are named p0, p1 etc in series
  ' variable q is a temporary holder for Command.CommandText - (q for query string)
   
  Connection.BeginTransaction   
    For i = 0 To fields - 1
      Command.AddParameter("p" & i)
      Command.SetParameter("p" & i, recList.Item(i))
    Next
    q = "INSERT INTO " & tableName & " VALUES (null"
    For i = 0 To fields -1
      q = q & ", @p" & i
    Next
    q = q & ")"
    Command.CommandText = q
    Command.ExecuteNonQuery      
  Connection.EndTransaction
End Sub

And this is the code that gets the field names into fieldList:

Sub GetFields
  Command.CommandText = "PRAGMA table_info ('" & tableName & "')"
  Reader.Value = command.ExecuteReader
  fieldList.Clear
  Do While Reader.ReadNextRow = True
    fieldList.Add(Reader.GetValue(1))
  Loop
  Reader.Close
  fields = fieldList.Count
End Sub

It's based on something I found here, I can't take the credit. Note the single & double quote characters in the Command string.

The editable table code I mentioned earlier is here:
http://www.b4x.com/forum/code-samples-tips/2609-editable-table-device-desktop.html
but I can't get it to work and am hoping someone can sort me out!

Caravelle

 

[Merlot2309]

Hi Caravelle and Bjorn,

Thanks for your replies.
I will get back to this as soon as my dip is out of the air (it's way too cold for me at the moment and my brain is froozen :BangHead.

Best regards,
Helen.

 

[Domingo]

From csv to sqlite

I'm spanish users and when I translate a table from csv (in unicode format) to sqlite there are mistakes at the characters as like as "ñ" or "á" ...
Can you help me?
Thanks

 

[Erel]

Are you creating the CSV file with Excel? Excel CSV files only support ASCII characters.

If you create a CSV file and save it with Unicode it should work properly.

 

[Domingo]

Hi Erel,

When I open the csv file with note pad displayed correctly. But when I translate to sqlite do that mistake.

Congratulations for basic4android. It is easy and very ussable for the users of basic4ppc.

Thanks for all