@Roger
You forgot
PowerBasic, Roger!
Rolf Brandt
Awesome... Got some other PB users here too! Hey Rolf and Rog! Personally I like the structure of Basic over C because it's alot easier to read and follow when debugging. But, even thou I'm new around here, I've got a couple of good tips for SQL users here that aren't really explained very well in the beginner's guide that I've had to scour the bases for...
First off, make sure you have the SQL library check marked in your LIBS panel and then you have to define a variable for holding your SQL handle... do so with:
I prefer to place it in my Globals so I can access it thruout the code. Next here are a few good examples of all the major functions you will need to create a database handling application:
To check to see if a table exists in the database:
tmp = SQL1.ExecQuerySingleResult("SELECT count(name) FROM sqlite_master WHERE type='table' AND name ='config'")
tmp will return 0 if the table doesn't exist.
To create a table, try the following:
SQL1.ExecNonQuery("CREATE TABLE IF NOT EXISTS config (id INTEGER, itemname TEXT, itemcount DOUBLE)")
To insert values into your database you can do so directly or indirectly with variables. But make sure you place single ' (quotes) around text entries.
Directly:
SQL1.ExecNonQuery("INSERT INTO config VALUES (1, 'Apples', 1234.56)")
or Indirectly:
Dim Id as Integer: Id = 1
Dim ItemName as String: ItemName = "Apples"
Dim ItemCount as Double: ItemCount = "1234.56"
SQL1.ExecNonQuery("INSERT INTO config VALUES (" & Id & ", '" & ItemName & "', " & ItemCount & ")")
(Side note, the ExecNonQuery will not return a value but will trigger an exception if there is a problem and can be called directly)
To update an existing entry in the database with different data use the following example:
SQL1.ExecNonQuery("UPDATE config SET id='"& et_ID.Text & "', itemname='" & et_ItemName.Text & "', itemcount='" & et_ItemCount.Text & "' WHERE id="& et_ID.Text )
To delete or remove a record from the database, chose a unique column and issue the following command:
SQL1.ExecNonQuery("DELETE FROM config WHERE id='" & et_ID.Text & " AND itemname='" & et_ItemName.Text & "'")
To select a whole bunch of rows from your database and utilize them for use in your program using the wildcard *, here's the method I've been using:
Dim Cur As Cursor
Dim Id as Int
Dim ItemName as String
Dim ItemCount as Double
Cur = SQL1.ExecQuery("SELECT * FROM config ORDER BY id DESC")
For i = 0 To Cur.RowCount - 1
Cur.Position = i
Id = Cur.GetInt("id")
ItemName = Cur.GetString("itemname")
ItemCount = Cur.GetDouble("itemcount")
' Do stuff to the values here
Next
You could refine your search to just return a couple values like this:
Cur = SQL1.ExecQuery("SELECT itemname, itemcount FROM config ORDER BY id ASC")
Only the two values will be returned in the search. Note, you can sort your search using the ORDER BY command at the end in either ASCending or DESCending order. You could probably do more with that but those two are the most common I've personally used.
You can also return a complete sum of all the values of database column using the following
Cur = SQL1.ExecQuery("SELECT SUM(itemcount) AS itemtotal FROM config")
ItemTotal = Cur.GetDouble("itemtotal")
You can replace SUM with AVG to return the Average value of all the values. Note it will add all the non-Null values from the itemcount column and place the total sum value into the new value you create called itemtotal. You retrieve the info just like you would any other column.
Now as a bit of a tip, I prefer to place SQL direct commands in UPPERCASE and all my database table names in lowercase because it makes it easier for me to read and differentiate between a command and a variable.
As stated in a prior post, I find that BeginTransaction/EndTransaction greatly improve the speed of a large database but don't do much for smaller ones (less then 50 entries)
These are just a few of the SQL commands I've used and find very helpful in dealing with a database app. I'm sure I'm not perfect, and I cut and paste as much code as I could, but edited a few lines to give a but more clarity. If any of it doesn't work, please let me know and feel free to correct any typos I made. Hopefully it's of use to someone else. Happy programming all and it feels great to be excited about programming again! Thanks Erel!
-=> Jim! <=-
