B4J Question SQLite/SQL handling

[ThRuST]

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:

    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

SELECT Count(*) FROM TABLE

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):

#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.Close

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)

EDIT: The source code was updated to v2. UPDATE now works correctly.

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.

###################################################

 

[ThRuST]

I rest my case, exchausted from cold virus

 

[Erel]

What about SQL INJECTION and B4X?

You should start a new thread for a new question.

 

[ThRuST]

No need, found your excellent explanation from the B4A SQL source code example. Again B4X shines

Sub ReadBlob
    Dim Cursor1 As Cursor
    'Using ExecQuery2 is safer as it escapes special characters automatically.
    'In this case it doesn't really matter.

 

[ThRuST]

Btw, I am using version 4.70 of B4J. Does it have the new auto formatting of code? can I use it with this version or will the new update come before X-mas?
Maybe I should post a new question about this? what do you recommend.

 

[ThRuST]

I need help with UPDATE. Anyone got a solution for this? I did not find it in the SQL tutorial.

SQLite

sql1.ExecNonQuery2("UPDATE SET users VALUES(?, ?, ?, ?)", Array As Object(Null, TextFieldFirstname.Text, TextFieldLastname.Text, TextAreaProfile.Text))

 

[Erel]

Btw, I am using version 4.70 of B4J. Does it have the new auto formatting of code? can I use it with this version or will the new update come before X-mas?
Maybe I should post a new question about this? what do you recommend.

These questions have nothing to do with this thread.

I need help with UPDATE. Anyone got a solution for this? I did not find it in the SQL tutorial.

http://www.w3schools.com/sql/sql_update.asp

 

[ThRuST]

I allready know about w3School, but I did not figure out a way how to make it work in B4J code with parameters (for extra safety, like you recommend).
After SET it says I must specify every record cellnames, which makes it complicated to solve with parameters, since w3s don't cover that.

W3school way

UPDATE Customers SET City='Hamburg' WHERE CustomerID=1;

How to use with parameters with Array As Object?

 

[ThRuST]

Dear Erel, you are amazing with programming, please can I know the B4J way to handle SQlite command for UPDATE.
I can update my source code example so that community can learn from it. It's a useful tool, you can try it

 

[Daestrum]

Not sure, but maybe

ExecNonQuery2("UPDATE SET users VALUES(?, ?, ?, ?)", ArrayAs Object(Null, TextFieldFirstname.Text, TextFieldLastname.Text, TextAreaProfile.Text))

Should be

ExecNonQuery2("UPDATE users SET col1=?, col2=?, col3=?, col4=?", Array(Null, TextFieldFirstname.Text, TextFieldLastname.Text, TextAreaProfile.Text))

Where col1 etc are your column names.

 

[ThRuST]

Daestrum, your first solution is the same as I posted in the source code example in the first post. It does not work.

ExecNonQuery2("UPDATE SET users VALUES(?, ?, ?, ?)", Array As Object(Null, TextFieldFirstname.Text, TextFieldLastname.Text, TextAreaProfile.Text))

Solution #2 (Corrected your Array, probably typo)

sql1.ExecNonQuery2("UPDATE users SET id=?, firstname=?, lastname=?, profile=?", Array(Null, TextFieldFirstname.Text, TextFieldLastname.Text, TextAreaProfile.Text))

That generated an error
(SQLException) java.sql.SQLException: [SQLITE_MISMATCH] Data type mismatch (datatype mismatch)

rwblinn, I have not tested your solution since I first want to get my SQLite source code example to work in first post.
My example source might be simple, but the whole idea with it was to provide a complete solution for a basic connection to SQLite.
Change LblID.Text to Index and it might work with DbUtils (which is out of scope in this tutorial). Thanks anway

 

[Erel]

Using parameterized queries is very simple. You just replace the values with question marks.

The syntax of the command itself is wrong. This is why I pointed you to the tutorial about the UPDATE command. It has nothing to do with B4J.

Note that you should also add the WHERE clause. Otherwise all the records will be updated.

Daestrum, your first solution is the same as I posted in the source code example in the first post. It does not work.

You misread his post. He wrote that instead of the first code you should use the second one.

(SQLException) java.sql.SQLException: [SQLITE_MISMATCH] Data type mismatch (datatype mismatch)

This is related to your database structure. You didn't provide the table structure so we can only guess the types.

 

[ThRuST]

sql1.ExecNonQuery2("UPDATE users SET id='?', firstname='?', lastname='?', profile='?'", Array As Object(Null, TextFieldFirstname.Text, TextFieldLastname.Text, TextAreaProfile.Text))

Returns error:
(IllegalMonitorStateException) java.lang.IllegalMonitorStateException

Come on guys, let's solve it There is no tomorrow!!

 

[ThRuST]

ah, good points I was not aware of that. However my database test.sqlite (test.zip in first post) is very simple.
Table users is made up by

id (integer)
firstname (VarChar)
lastname (VarChar)
Profile (Text)

sorry for not pointing this out. I was thinking everyone was to try my source code example in first post

 

[ThRuST]

Erel has a point: There's no WHERE id=" & index clause in our examples!! Sorry for this simple misstake.
One step closer to the solution

 

[ThRuST]

I finally solved it. Source code example is now updated to version 2. UPDATE now works correctly.
Download the source code and see for yourself