SQL Tutorial

[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.


Caravelle

[Merlot2309]

Hello,

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

Loading the database goes soooooooo fast

@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:
Editable Table - Device & Desktop
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 ).

Best regards,
Helen.