B4J Question Update Else Insert SQL

aaronk

Well-Known Member
Licensed User
Longtime User
Hi,

I am trying to update a record in the MySQL database, however if the record doesn't exists I want to add it.

I can update the record by doing:
B4X:
Dim item, Account, location As String
        item = "123"
        Account = "ABC"
        location = "Test"
        
        sql.ExecNonQuery2("Update Table SET Item = ? WHERE Account = ? AND Location = ?",Array (item,Account,location))

However, I am not sure if there is a query I can use to detect if the record wasn't updated and the add it ?

Anyone know if there is a way in updating the record if it doesn't update then to insert it ?
 

DonManfred

Expert
Licensed User
Longtime User
 
Upvote 0

aaronk

Well-Known Member
Licensed User
Longtime User
When I use that I seem to get an error.

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF ROW_COUNT()=0 INSERT INTO Table (Account, Location, Value) VALUES ('A' at line 1

Maybe I am doing it wrong ?

B4X:
Dim item, Account, location As String
    item = "123"
    Account = "ABC"
    location = "Test"
       
sql.ExecNonQuery2("UPDATE Table SET Item=? WHERE Location=? AND Account=? IF ROW_COUNT()=0 INSERT INTO Table (Account, Location, Item) VALUES (?,?,?)",Array (item,location,Account,Account,location,item))
 
Upvote 0

aaronk

Well-Known Member
Licensed User
Longtime User
I am going to see if I can re-design my SQL database, and include a primary key somehow based on the data I am using. This way I can use the ON DUPLICATE KEY function in the query.
 
Upvote 0

codie01

Active Member
Licensed User
Longtime User
This is probably close to what you require!

B4X:
Dim myItem As String = "123"
Dim myAccount As String  = "ABC"
Dim myLocation As String = "Test"

Dim itemsql1 As SQL
itemsql1.Initialize2("org.mariadb.jdbc.Driver", "jdbc:mariadb://" & ABMShared.myServerAddress & ":3306/" & myServerDatabase, myServerUsername, myServerPassword)

Dim itemCursor1 As ResultSet
itemCursor1 = itemsql1.ExecQuery("SELECT * FROM mytable WHERE Item = '" & myItem & "' AND  account = '" & myAccount & "' AND location = '" & myLocation & "'")

If itemCursor1.Nextrow = True Then
    itemsql1.ExecNonQuery2("Update mytable SET item = ? WHERE location = '" & myLocation & "' AND Account = '" & myAccount & "'",  Array As Object(myItem))
Else
    itemsql1.ExecNonQuery2("INSERT INTO contract VALUES(?,?,?,)", Array As Object(myItem,myAccount,myLocation))
Endif

itemCursor1.Close
itemsql1.close
 
Upvote 0

aaronk

Well-Known Member
Licensed User
Longtime User
I ended up joining the account and location fields together and making that a primary key.

Then I can insert it like:
B4X:
sql.ExecNonQuery2("INSERT INTO Table (Location, Value) VALUES (?,?) ON DUPLICATE KEY UPDATE Value=?",Array (Account & "_" & Location,Value,Value))
 
Upvote 0
Top