I want to create a simple SQL example application that handles all the basic usages needed when making a connection to a SQLite/MySQL database. This is just a simple example you can add your solution to. I want to cover answers to these questions:
* a remote MySQL server connection (is DNS name needed or will IP work)
* INSERT with ExecQuery 1 and 2 (single and with (?) i.e. multiple parameters)
* UPDATE (same as above)
* DELETE
* TRUNCATE (clear that resets index id value to 0)
EDIT: In SQLite it works different how to clear a table.
You should use DELETE TABLE command to delete all the records, it is recommended to use VACUUM command to clear unused space. So you can use:
in MySQL however, you use the TRUNCATE TABLE command.
* I'd like to know how to get the total number of records in the database?
EDIT: I found a solution to this myself. To get the total number of records you use
SQLite
Also how is an image (BLOB) saved (inserted/Updated) into the database. The SQLite database is in this case in the folder at c:\sqlite which can be created/edited with Firefox addon SQLite Manager.
EDIT: This is covered in Erels SQL tutorial, you find links in his post below.
This is what I've got. to read (SELECT):
Thanks
###################################################
Here's my SQLite/SQL source code example for B4J.
You can use it to experiment with SQLite/MySQL databases and others.
* unzip test.zip (test.sqlite) to c:\sqlite
* unzip RogersSQLiteExamplev2.zip (B4J source code)
Simple as that. I have not yet figured out how to put code in UPDATE,
so if someone can please add that and upload a new file it's great.
Oh, and I added an int called index to correspond with the database id,
so with that you can override the db id field and browse between records with the index.
You'll understand when you read the code. It's using scene builder...
You can use Firefox addon SQL manager to browse the database to view your changes.
###################################################
* a remote MySQL server connection (is DNS name needed or will IP work)
* INSERT with ExecQuery 1 and 2 (single and with (?) i.e. multiple parameters)
* UPDATE (same as above)
* DELETE
* TRUNCATE (clear that resets index id value to 0)
EDIT: In SQLite it works different how to clear a table.
You should use DELETE TABLE command to delete all the records, it is recommended to use VACUUM command to clear unused space. So you can use:
B4X:
sql1.ExecNonQuery("DELETE from users")
sql1.ExecNonQuery("VACUUM")in MySQL however, you use the TRUNCATE TABLE command.
* I'd like to know how to get the total number of records in the database?
EDIT: I found a solution to this myself. To get the total number of records you use
SQLite
B4X:
SELECT Count(*) FROM TABLEAlso how is an image (BLOB) saved (inserted/Updated) into the database. The SQLite database is in this case in the folder at c:\sqlite which can be created/edited with Firefox addon SQLite Manager.
EDIT: This is covered in Erels SQL tutorial, you find links in his post below.
This is what I've got. to read (SELECT):
B4X:
#AdditionalJar: sqlite-jdbc-3.7.2
Dim sql1 As SQL
Dim TextFieldID As TextField
Dim TextFieldFirstname As TextField
Dim TextFieldLastname As TextField
Dim ImgProfile As ImageView
sql1.InitializeSQLite("c:\", "sqlite/test.sqlite", True)
Dim RS As ResultSet = sql1.ExecQuery("SELECT * FROM users WHERE id = 0")
Do While RS.NextRow
' Read the records from the database
TextFieldID.Text = RS.GetString("id")
TextFieldFirstname.Text = RS.GetString("firstname")
TextFieldLastname.Text = RS.GetString("lastname")
ImgProfile.GetImage = RS.GetString("image")
Loop
RS.CloseThanks
###################################################
Here's my SQLite/SQL source code example for B4J.
You can use it to experiment with SQLite/MySQL databases and others.
* unzip test.zip (test.sqlite) to c:\sqlite
* unzip RogersSQLiteExamplev2.zip (B4J source code)
Simple as that. I have not yet figured out how to put code in UPDATE,
so if someone can please add that and upload a new file it's great.
Oh, and I added an int called index to correspond with the database id,
so with that you can override the db id field and browse between records with the index.
You'll understand when you read the code. It's using scene builder...
You can use Firefox addon SQL manager to browse the database to view your changes.
###################################################
Last edited: