B4J Question SQLite/SQL handling

ThRuST · Dec 8, 2016

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:

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

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

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

Erel · Dec 8, 2016

What is the question?

ThRuST · Dec 8, 2016

Multiple questions which answers handling of SQLite, or any other SQL supported database.
If you upload a source code example will be highly appreciated.

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)
* Get the total number of records in the database

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.

ThRuST · Dec 8, 2016

For example I tried INSERT, but this didn't work. I made a misstake:

B4X:

Dim query As String="INSERT INTO users(firstname) VALUES (?)"
sql1.ExecNonQuery2(query,Array As String("'test'"))

etc

ThRuST · Dec 8, 2016

To connect to a remote MySQL database:

B4X:

sql1.Initialize("com.mysql.jdbc.Driver", "jdbc:mysql://localhost/test?characterEncoding=utf8")

Erel · Dec 8, 2016

The two relevant tutorials are:
https://www.b4x.com/android/forum/threads/sql-tutorial.6736/#content
And:
https://www.b4x.com/android/forum/threads/sql-tutorial.35185/

ThRuST · Dec 8, 2016

That's what I was looking for, a complete set of answers thanks.

ThRuST · Dec 8, 2016

Please Erel, it does not work to delete a record. What is wrong with this query?

B4X:

sql1.ExecNonQuery("DELETE FROM users WHERE id = 0")

EDIT: THIS COMMAND WORKS. I JUST USED ANOTHER DIM NAME FOR SQL1.

I also struggle with INSERT. Do you mind please type a simple correct command for this.
Thanks a million.

ThRuST · Dec 8, 2016

I tried this:

B4X:

sql1.ExecNonQuery2("INSERT INTO users VALUES (null, ?, ?)", Array As Object("'hello', 'world'", 2))

Also tried Array As String. It returns (NullPointerException) java.lang.NullPointerException

ThRuST · Dec 8, 2016

It seems like all cells must be filled. My misstake, sorry but I am not sure about the array parameters.

B4X:

sql1.ExecNonQuery2("INSERT INTO users VALUES(?, ?, ?)", Array As Object("def", 3, 4))

Log:
(SQLException) java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (table users has 7 columns but 3 values were supplied)

klaus · Dec 8, 2016

As you have 7 columns you must provide 7 values!
sql1.ExecNonQuery2("INSERT INTO users VALUES(?, ?, ?, ?, ?, ?, ?)", Array As Object("def", EditText1.Text, 4, "x", 5, 90, "yx"))

ThRuST · Dec 8, 2016

Thanks Klaus. id is the first cell and seems to should be part of the INSERT query, even though it's set to autoincrease.
Do you mind poiting out why not 'Array As String' is used, and when it's used, if ever!!? Database items is handled as objects always?
I assume it's safer to use (?) parameters because of safety. I've read about the importance when using PHP and MySQL.
Because of some hacking techniques called SQL INJECTIONS. But that's a separate topic from this thread.

keirS · Dec 8, 2016

ThRuST said:
View attachment 50800
Thanks Klaus. id is the first cell and seems to should be part of the INSERT query, even though it's set to autoincrease.
Do you mind poiting out why not 'Array As String' is used, and when it's used, if ever!!? Database items is handled as objects always?
I assume it's safer to use (?) parameters because of safety. I've read about the importance when using PHP and MySQL.
Because of some hacking techniques called SQL INJECTIONS. But that's a separate topic from this thread.

The id column shouldn't be part of the INSERT statement if it's an Auto increment. As an example (in SQLITE) in the table:

B4X:

CREATE TABLE [AUTHORS] (
  [AUTHORID] INTEGER PRIMARY KEY AUTOINCREMENT,
  [FIRST_NAME] CHARACTER(30) DEFAULT NULL,
  [LAST_NAME] CHARACTER(40) DEFAULT NULL);

CREATE INDEX [AUTHORS_FIRSTNAME] ON [AUTHORS] ([LAST_NAME]);

CREATE INDEX [AUTHORS_LASTNAME] ON [AUTHORS] ([LAST_NAME]);

This insert code works fine:

B4X:

sql1.BeginTransaction
sql1.ExecNonQuery("insert into authors (Last_name) Values('test')")
sql1.TransactionSuccessful

ThRuST · Dec 8, 2016

Thanks I will try that. Earlier I got klaus answer to work but after I recreated the table with fewer cells it won't autoincrease the id value, even thougth I selected it when creating the table.

B4X:

sql1.ExecNonQuery2("INSERT INTO users VALUES(?, ?, ?, ?)", Array As Object("def", TextFieldFirstname.Text, TextFieldLastname.Text, TextAreaProfile.Text))

This error is most annoying. Can you see what's wrong?!!! Please

ThRuST · Dec 8, 2016

About Array As Object
If I use ("def" or ("0" or (0, it will not insert a new only the first one!! I go crazy Erel come to the rescue you created B4X!!!!!

klaus · Dec 8, 2016

As your first column is INTEGER PRIMARY KEY you should use, at least for SQLite:
sql1.ExecNonQuery2("INSERT INTO users VALUES(Null, ?, ?, ?, ?, ?, ?)", Array As Object(EditText1.Text, 4, "x", 5, 90, "yx"))

I think you have a test project, yould post it as a zip file so we could what you have done.
IDE menu Files / Export As Zip.
You may have a look at chapter 5 SQLite Database in the B4A User's Guide.
The difference between B4A and B4J is that the Cursor object doesn't exist in B4J you must use RecordSet instead.
RecordSet does also exist now in B4A, this will be updated in the next version of the B4A User's Guide.

ThRuST · Dec 8, 2016

keirS, I tested your code and it worked. It stores "test" into only the Last_name cell
Transaction seems the better way, or what do you think!!
Yes klaus I will study your solution better, thanks.
Damn cold I need to rest. Been struggling with this Sqlite for hours!! But I learned some things, great!

keirS · Dec 8, 2016

ThRuST said:
View attachment 50809
Thanks I will try that. Earlier I got klaus answer to work but after I recreated the table with fewer cells it won't autoincrease the id value, even thougth I selected it when creating the table.

B4X:

sql1.ExecNonQuery2("INSERT INTO users VALUES(?, ?, ?, ?)", Array As Object("def", TextFieldFirstname.Text, TextFieldLastname.Text, TextAreaProfile.Text))

This error is most annoying. Can you see what's wrong?!!! Please

That's not valid SQL syntax for an INSERT statement. It's valid syntax for MySQL but I not for SQLite. I would always use the "INSERT (col1,col2,col3....) VALUES(1,2,3..)" syntax.

ETA: Actually it is valid for SQLITE but it's IMO a terrible way of doing an INSERT.

ThRuST · Dec 8, 2016

ah now I get it. I must have used a (?) for the autoincrement id that was causing the error. According to klaus post. Hat off to you code magicians

ThRuST · Dec 8, 2016

What about SQL INJECTION and B4X? or maybe it's only relevant to PHP code. Thank god for you guys

B4J Question SQLite/SQL handling

Well-Known Member

Attachments

B4X founder

Well-Known Member

Well-Known Member

Well-Known Member

B4X founder

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Expert

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Expert

Well-Known Member

Well-Known Member

Well-Known Member

Well-Known Member

Similar Threads

Privacy & Transparency

Privacy & Transparency