Android Question Sqlite REPLACE INTO command question

karld · Jul 24, 2016

Trying to get some SQLcode sorted out.

I am pulling down a XML file from Firebase Storage. That is working great.

I can parse the update into my Sqlite DB on the phone.

What I am trying to figure out is how NOT to keep duplicating the same records over and over.
The update file will change very seldom.

I have tried REPLACE INTO and it still just adds a new record.

Does Sqlite support checking if a record exists, then UPDATE it verses just adding a new record every time?

I tried just dropping the table and re-creating it but it takes to long.

karld · Jul 24, 2016

Additional info

This query I am running on my PC using SQl Database Browser and it works perfectly.

B4X:

replace into Service_codes (ID, DisAdvantage, DisE500, DisE300, DisE700, Tokheim, Wayne, ServiceCode, ErrDisc) values ('302','0','1','0','1','0','0','2222','fake code')

I put it into b4a as this

B4X:

      txt1 = "REPLACE INTO " & DBTableName1
       txt1 = txt1 & " (ID, DisAdvantage, DisE500, DisE300, DisE700, Tokheim, Wayne, ServiceCode, ErrDisc)"
       txt1 = txt1 & " VALUES ('"
       txt1 = txt1 & ID & "','"
       txt1 = txt1 & DisAdvantage & "','"
       txt1 = txt1 & DisE500 & "','"
       txt1 = txt1 & DisE300 & "','"
       txt1 = txt1 & DisE700 & "','"
       txt1 = txt1 & Tokheim & "','"
       txt1 = txt1 & Wayne & "','"
       txt1 = txt1 & ServiceCode & "','"
       txt1 = txt1 & ErrDisc & "')"
     
       sql1.ExecNonQuery(txt1)

Instead of updating the existing table entry it will add another row.

I maybe blind, but I see no difference in the query string?

Does anyone have any suggestions? A better way? Voodoo magic?

Widget · Jul 24, 2016

karld said:
Does anyone have any suggestions? A better way? Voodoo magic?

Maybe a second set of (BIG) eyes?

You need a Where Clause to tell it which row you want to replace. If the row does not exist (wrt Where Clause), it will be inserted. If the row does exist (wrt Where clause) then the row will be updated. Because you did not supply a Where Clause, it did not know which row to look for. Does that make sense?

Try something like this:

B4X:

txt1 = "REPLACE INTO " & DBTableName1
txt1 = txt1 & " (ID, DisAdvantage, DisE500, DisE300, DisE700, Tokheim, Wayne, ServiceCode, ErrDisc)"
txt1 = txt1 & " VALUES ('"
txt1 = txt1 & ID & "','"
txt1 = txt1 & DisAdvantage & "','"
txt1 = txt1 & DisE500 & "','"
txt1 = txt1 & DisE300 & "','"
txt1 = txt1 & DisE700 & "','"
txt1 = txt1 & Tokheim & "','"
txt1 = txt1 & Wayne & "','"
txt1 = txt1 & ServiceCode & "','"
txt1 = txt1 & ErrDisc & "') where Id="&Id

sql1.ExecNonQuery(txt1)

I personally would use SQL parameters "?" to prevent certain characters like " in any of the strings from screwing things up. If you are only inserting numbers then it is not much of a problem.

BTW, if the Replace command find the row in the table, it will do a Delete followed by an Insert which is not very efficient. It is better to test if the row exists using:
Select count(*) from table where Id=?

and then either execute an Update ... where Id=? or Insert Into ...

For more info see http://code.openark.org/blog/mysql/replace-into-think-twice

karld · Jul 24, 2016

Thanks!!

I found a different way of handling it..

I just added a tag into the xml fiile and check it that way. Works like a champ.
The updates will be few and far between, so efficient is not the prime goal.
(Maybe 4 or 5 record adds or updates in a year?)

I will play with this some more... Knowledge is power! as they say..

Android Question Sqlite REPLACE INTO command question

karld

Active Member

karld

Active Member

Widget

Well-Known Member

karld

Active Member

Similar Threads

Android Question Sqlite REPLACE INTO command question

karld

Active Member

karld

Active Member

Widget

Well-Known Member

karld

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency