SQL example

Caravelle · Sep 2, 2009

I am evaluating the trial version of BASIC4PPC. My main interest is to use SQLite databases on my iPAQ - for that purpose the language looks ideal, and I must say it seems pretty easy to use - even for a long-time Delphi fan. I wrote a program to input my name and get "Hello, " + name back on a button press and it worked after very little debugging (ie removing the semi-colons I automatically put at the end of every line). Then I explored the Help to see how loops and things were handled - nice and simply, it seems.

Next I downloaded and ran the SQL example on my desktop but it didn't work: the cmdExecuteTable command in line 20 gets the response:
"String was not recognised as a valid DateTime".

Oh dear. I wonder which date in which column in which table that would be? I have SQLiteSpy (excellent free program for playing with Sqlite databases) but I don't know where to start looking for the unexpected data.

I would like to get going with this trial and see if it really is feasible to program a fairly complex SQLite system with BASIC4PPC. Could you explain and/or fix the error in the example, please? Is there some system setting that would make a difference?

Thanks in advance

Caravelle

kalmenchia · Sep 3, 2009

Hi Delphi Fan ,

I am a delphi fan too , ok , i tried the sample u mentioned and I kinda suspect is the sqlite date format , if u opened the northwind.sl3 , the datetime format
in the database is "mm/dd/yyyy 12:00:00 AM" which i think is not a correct date format , where I use another table to test , i found out that
"1/1/2009 13:00:00" ~ "1/1/2009 23:59:59" is correct but
"1/1/2009 24:00:00", "1/1/2009 1:00:00 PM" is not and therefore I concluded that the sample northwind.sl3 database date format cannot be use!

Ariel_Z · Sep 3, 2009

Hi,
As can be seen here
Datatypes In SQLite Version 3, SQLite does not expose a DateTime data type (as MSSql does, to mention one). You can use the string data type instead, and convert the string to date format using the DateParse keyword: Basic4ppc - Main Help. You may also need to change the DateFormat (see there) and use the Date (see there) keyword to convert back: it's all explained in the help file.

For a detailed tutorial and example of SQLite see Basic4ppc SQL library reference. This tutorial also references the SQLite site for more information.

Caravelle · Sep 4, 2009

Well, thank you, but I had already read the tutorial you mention, and I'm familiar with the official sqlite site. It's when you try binding fields to a certain type that the trouble arises when you get something which doesn't match up to what you anticipated.

I'm afraid that the explanation does not change the simple fact that the example won't read the data provided with it. You should either re-format the Date fields into a form your program will read without amendment, or re-write the example so it works with the data as it stands. It certainly should not unexpectedly grind to a halt with a message which will mean nothing to a beginner. And you should test it before before putting it online. An example that doesn't work is a poor advertisement for your product and your production standards.

The db provided is presumably the one you can download from sqlite.org/cvstrac/wiki?p=ConverterTools where it says "Here is the Microsoft sample Northwind database converted to SQLite". It certainly has the same result in your example.

Personally I'm quite happy to store my dates as character strings, using any system - even Excel: I store them in YYYYMMDD form (it sorts nicely), and a small routine to translate into other string representations is just a matter of simple string manipulation and an array of month names. HHMMSS can be added on the end if times are really necessary. I only ever convert them to a date/time format on the fly if and when I need to use them in a calculation. There is rarely any need for such complication and translation between database systems becomes very simple - strings are strings whatever you're using.

Caravelle.

Erel · Sep 4, 2009

It certainly should not unexpectedly grind to a halt with a message which will mean nothing to a beginner. And you should test it before before putting it online. An example that doesn't work is a poor advertisement for your product and your production standards.

I 100% agree. This will be soon fixed. It did work in the past, I guess that the new SQLite version or Basic4ppc wrapper has broke this example for some reason.
Thanks.

Erel · Sep 5, 2009

The Northwind database date columns where formatted in a format not supported by the .Net wrapper.
The solution was to change the columns type to Text instead of timestamp.
You can download the updated file here: http://www.b4x.com/files/SQLExample.zip

glook · Sep 5, 2009

Hello Caravelle,

Just returned to this forum after quite a long time away (busy with other projects) and saw your post.

As Erel said, the example used to work fine and was a useful start (as a newbie!).

I also first looked at Basic4PPC specifically with a need to develop a fairly heavy-weight SQL program. IMHO it was absolutely the best choice. My application uses an SQLite db which is split into 2 big files. It includes over 10,000 master records with 40 searchable columns, plus 5,000 pictures as BLOBs. The end-user has full add/delete/edit access. The main program is about 3000 lines. There was a bit of a learning curve at first, but enabled fast development - great product.

Rgds, Geoff.

RandomCoder · Sep 6, 2009

I too found that the tutorial differs from the source code, having just copy and pasted the lines I wanted to adapt I then found that a reader.close statement was missing in the If Then block. But now I have a new problem...

B4X:

SQLCommand.CommandText = "SELECT Name FROM SQLite_Master WHERE [COLOR="Red"]'Type = 'Table'[/COLOR] AND Name = 'FileNames'"
SQLReader.Value = SQLCommand.ExecuteReader
If SQLReader.ReadNextRow = True Then

The ReadNextRow alway returns False even though I have created the table as defined in the tutorial.
Using the browser that is recommended, I can see that the problem is that the Table is not assigned any Type value. The field is just left blank although the object field says TABLE.

For now I have removed the part in red and all works fine but should I have assigned a type to the table when I created it?

Regards,
RandomCoder

Ariel_Z · Sep 6, 2009

1. How did you create the table (code/browser)?
2. Which tutorial did you found the mistake in? This Basic4ppc SQL library reference?

RandomCoder · Sep 6, 2009

Hi Ariel,

I created the table in code as per your example but I do this everytime I load the application as I am searching a list of filenames that are likely to have changed. Thats why I first check to see if a table already exists and if so DROP it before creating a new table.

Yes the link you provided is the same tutorial that I followed, step 3 para "Now your code should be looking like this:" throws an error because a Reader.Close is missing which is actually present in the source that you provided.

Please don't think that I'm griping as I'm actually very impressed with the tutorial and grateful for the amount of time that you have obviously spent on it :sign0188:

My question was more about the creation of the table, should I have told it that it was of type Table or have I done right by just ommitting the type statement from my SELECT query? This is the first time I have properly played with SQL and so I'm still trying to learn the ropes.

Thanks,
RandomCoder

Ariel_Z · Sep 7, 2009

1. For your question - I think what you are doing is correct. Is it possible that there might be an unnecessary ' sign (apostrophe) in the code you have attached? Just prior to the red word "Type"? If it's there in your source code as well it might be the reason. If it isn't please let me know and I'll try to look for it on here.

2. I cannot explain how much I appreciate any feedback about the tutorial, and the most appreciated are of course cases in which people point out mistakes. I did not think for a moment you were griping as you have the right to have as precise tutorial as possible. Of course there will be some mistakes and I'll try to fix them. But it's a good thing when someont points out an error. So thank you very much for that. As for the missing Reader.Close - fix me if I'm wrong, but the source code does not differ (in this part, there are some other minor differences) than the code provided on the tutorial itself. In both the Reader.Close is written after the ELSE statement, with the comment "Close reader":

B4X:

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 = False Then

      ' No table with this name in the database.

      ' Create one.

      Command.CommandText = "CREATE TABLE t_orders (ID INTEGER PRIMARY KEY, Sum REAL, TableNum INTEGER," & _
      "SeatNum INTEGER, Hamburger INTEGER, French INTEGER, Coke INTEGER, Water INTEGER, " & _
      "isServed INTEGER, Time TEXT)"

      Command.ExecuteNonQuery

   Else

      'Close reader anyhow

      Reader.Close

   End If

End Sub

I might have missed something - please let me know if I'm wrong and I'll correct it.

RandomCoder · Sep 7, 2009

Ariel_Z said:

As for the missing Reader.Close - fix me if I'm wrong, but the source code does not differ (in this part, there are some other minor differences) than the code provided on the tutorial itself. In both the Reader.Close is written after the ELSE statement, with the comment "Close reader":

B4X:

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 = False Then
      [COLOR="Red"]Reader.Close[/COLOR]

      ' No table with this name in the database.

      ' Create one.

      Command.CommandText = "CREATE TABLE t_orders (ID INTEGER PRIMARY KEY, Sum REAL, TableNum INTEGER," & _
      "SeatNum INTEGER, Hamburger INTEGER, French INTEGER, Coke INTEGER, Water INTEGER, " & _
      "isServed INTEGER, Time TEXT)"

      Command.ExecuteNonQuery

   Else

      'Close reader anyhow

      Reader.Close

   End If

End Sub

The ' (apostrophe) is a typo made by me when writting this thread, I had previously cut that part of code out and placed it as a comment within my code (the reason for the ' appearing when I then pasted it back into this thread). The problem can be seen when using the SQL Browser. A table has an object value of Table but the Type field is blank.

As for the code snippet, I've outlined the problem in red above. The Reader.Close is needed before creating the table unless I have done something wrong.
I also think that it is better to do away with the Else statement and place the Reader.Close outside of the If statement ie..

B4X:

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 = False Then
      [COLOR="Red"]Reader.Close[/COLOR]

      ' No table with this name in the database.

      ' Create one.

      Command.CommandText = "CREATE TABLE t_orders (ID INTEGER PRIMARY KEY, Sum REAL, TableNum INTEGER," & _
      "SeatNum INTEGER, Hamburger INTEGER, French INTEGER, Coke INTEGER, Water INTEGER, " & _
      "isServed INTEGER, Time TEXT)"

      Command.ExecuteNonQuery

   End If
   [COLOR="Red"]Reader.Close[/COLOR]
End Sub

This way all commands are closed because (unless I'm misunderstanding) really there should be a reader.close after doing the Command.ExecuteNonQuery that created the table?

Regards,
RandomCoder

Ariel_Z · Sep 7, 2009

1. You are right. I really appreciate it - I missed the obvious. It is fixed on the website now. Thank you.
2. Could you please post your code so that I could have a look at it?

RandomCoder · Sep 7, 2009

Its a work in progress so please be kind

I'm basically searching a folder for files that I've created using another B4PPC program, the file name is made up of:
"MachineNumber_MagazineNumber OrderNumber_BatchNumber Date_Time.rtf"
I drop the table and recreate it every time because I fully expect the user to change file names, move and or delete files (at least until they get used to the system)

Regards,
RandomCoder.

PS the commented out sections are because I started usig a Table control which I'm familiar with but then thought it better to try SQL and learn something new.

anonim · Sep 9, 2009

Caravelle said:
"String was not recognised as a valid DateTime".
Caravelle

I think in order to prevent these messages regardless of the regional settings you have to execute something like this on AppStart

B4X:

DateFormat("yyyy-mm-dd")
TimeFormat("HH:mm:ss")

It helped me with SQLite

Caravelle · Sep 9, 2009

That last post looks helpful if someone really must use date/time variables in their program. As I said, I don't, when I program I'm quite happy with plain text strings which do everything I need - and I like to keep it simple.

I have been busy writing lots of little subs to do things like transfer data in from text files, and write data out to text files. A lot of my data arrives (or has to be sent out) in e-mails and different senders use different formats, so there is really no one common solution - and I sometimes have to resort to manually editing the text to make it work anyway. The first stage is always to convert to good old csv.

As I'm used to a wealth of Delphi string formatting functions, I have already written a few simple subs to perform common functions like LeftStr, RightStr, LeftTrim, RightTrim, Trim and a rather specialised one that should make keyboard input of certain data much easier - one common data field I have to enter is always capital letters and numbers but very often contains a hyphen, sometimes a stop. Now I can type in lower case and type space or any other non-letter/number key available on either keyboard (except stop) when I need a hyphen in the result. The sub substitutes the hyphen and converts to upper-case, so for example, "hb jhc" as typed emerges as "HB-JHC". This not only saves a few keystrokes but validates the data to some extent too. Try doing that with something like HanDBase.

Anyway, thanks to Ariel for sorting out the issue of the sqlite example that wouldn't work. It is always very discouraging when examples fail and sadly it is an all too common annoyance - even with high-end commercial software.

Thanks again
Caravelle

Ariel_Z · Sep 10, 2009

Thank you very much for that. It is hard to explain how deeply we appreciate your feedback regarding this SQL example. As changes take place naturally on peripheral DLL's we try to verify everything still works, but we clearly failed to do so this time. I hope you will find the current example helpful: please don't hesitate to consult the forum further.

Ariel_Z · Sep 11, 2009

RandomCoder, please try:

B4X:

SQLCommand.CommandText = "SELECT Name FROM SQLite_Master WHERE Type = 'table' AND Name = 'FileNames'"
SQLReader.Value = SQLCommand.ExecuteReader
If SQLReader.ReadNextRow = True Then...

and let me know, please, if it works. Note the lower case in the word "table" unlike you previous example. It works here.

Ariel

RandomCoder · Oct 6, 2009

Ariel_Z said:
RandomCoder, please try:

B4X:

SQLCommand.CommandText = "SELECT Name FROM SQLite_Master WHERE Type = 'table' AND Name = 'FileNames'" SQLReader.Value = SQLCommand.ExecuteReader If SQLReader.ReadNextRow = True Then...

and let me know, please, if it works. Note the lower case in the word "table" unlike you previous example. It works here.

Ariel

Ariel, please accept my appologies for not responding sooner (I had somehow missed your reply).
The problem is that the type field is blank as can be seen using the browser...

I have got around the problem by omitting the WHERE type = 'table'.

Regards,
RandomCoder

agraham · Oct 7, 2009

RandomCoder said:
The problem is that the type field is blank as can be seen using the browser

Type is only valid for a database field. Your screenshot is showing the table definitions which don't have a type.

SQL example

Active Member

Member

Active Member

Active Member

B4X founder

B4X founder

Member

Well-Known Member

Active Member

Well-Known Member

Active Member

Well-Known Member

Active Member

Well-Known Member

Attachments

New Member

Active Member

Active Member

Active Member

Well-Known Member

Expert