Android Question Sqlite REPLACE INTO command question

[karld]

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]

Additional info

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

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

      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]

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:

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]

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