Entry not found, but when add violates Unique constraint

LAM_YUK_HONG · July 6, 2017, 7:50am

Hi all,

My web app is a multi-lingual app which supports English and Chinese. For every label on the webpage / webdialog, I used to call a method under WebSession to retrieve the corresponding Language. In order to reduce duplicate D/B activity, I would like to save retrieved entries to a SQLite database. The logic is:

Try to get the record from SQLite database
If found, return string
If not found, get record from MySQL database, save in SQLite database, return string

Here is the code:

GetLanguageString(Label as string, Language as integer, optional WithColon as boolean) As string

dim Loc_Sql as string
dim rs as RecordSet
dim ReturnStr as string

Loc_Sql = "SELECT GUID, Label, English, TChinese FROM MEM_LANGUAGE WHERE Label='" + Label + "'"
rs = session.LanguageDB.LoadTable(Loc_Sql, "app.GetLangugeString", 1)  //LanguageDB is a session instance of SQLite DB
if rs <> nil then
  if rs.Field("GUID").IntegerValue = 0 then
    Loc_Sql = "SELECT GUID, Label, English, TChinese FROM LANGUAGE WHERE Label='" + Label + "'"
    rs = session.LoadTable(Loc_Sql)  //my own defined method to retrieve record from MySQL DB
    if rs <> nil then
      if rs.field("GUID").IntegerValue <> 0 then
        Select Case Language
        Case app.kLanguageEnglish
          ReturnStr = rs.Field("English").StringValue
        Case app.kLanguageChinese
          ReturnStr = rs.Field("TChinese").StringValue
        End Select
        
        //Add entry to In Memory SQLite Language DB
        dim rec as new DatabaseRecord
        rec.IntegerColumn("GUID") = rs.Field("GUID").IntegerValue
        rec.Column("Label") = rs.Field("Label").StringValue
        rec.Column("English") = rs.Field("English").StringValue
        rec.Column("TChinese") = rs.Field("TChinese").StringValue
        session.LanguageDB.InsertRecord("MEM_LANGUAGE", rec)
        if session.LanguageDB.Error then
          msgbox ("session.GetLanguageString Error: " + session.LanguageDB.ErrorMessage + " : " + str(rs.Field("GUID").IntegerValue) + " : " + rs.Field("Label").StringValue)
         end if
      end if
    end if
  else
    Select Case Language
    Case app.kLanguageEnglish
      ReturnStr = rs.Field("English").StringValue
    Case app.kLanguageChinese
      ReturnStr = rs.Field("TChinese").StringValue
    End Select
  end if
end if

if WithColon = true then
  ReturnStr = trim(ReturnStr) + ":"
end if

return ReturnStr

The problem is, although not very often, there is error during session.LanguageDB.InsertRecord (GUID is the PK). Any ideas?

Jean-Yves_Pochez · July 6, 2017, 9:08am

I would check rs.recordcount just after testing the nil.
what is the error number (and description) you get ?
languagedb.errorcode and errormessage you can display at this time.

LAM_YUK_HONG · July 6, 2017, 9:23am

I have added to show the content of the record to be added when the error occurs some days ago (but removed now), so I forgot to drop down the ErrorCode but the Error Message is: UNIQUE constraint failed. I have written a module to display the entire content of the SQLite database, the record that causes the error do exists in the SQLite DB, so the problem is why does it return GUID =0 in the first place as if it does not get the record from SQLite.

Jean-Yves_Pochez · July 6, 2017, 10:24am

that’s what I said then: check rs.recordcount

if rs.recordcount=0 then ' deal with no record found else ' modify found record end if

LAM_YUK_HONG · July 6, 2017, 11:29am

Sorry, maybe I am wrong but what I mean is, no matter using rs.recordcount = 0 or rs.field(“GUID”).integervalue = 0, it implies that the record is not there, but the point is I use another module to show the entire SQLite records, the target record is there just it cannot be returned.

DaveS · July 6, 2017, 2:20pm

Remember that SQL is case sensitive…

 Label='" + Label + "'"

if the database as “XYZ” and your SQL says “xyz”… it will NOT MATCH

Look at COLLATE NOCASE option, which can be applied in the CREATE or SELECT statement

LAM_YUK_HONG · July 6, 2017, 2:35pm

@dave, I know and checked that already. As mentioned in my post, error does not happen every time for the same app

Jean-Yves_Pochez · July 6, 2017, 2:40pm

reconstruct indexes ? analyse database ?

Louis_D · July 6, 2017, 2:49pm

do you have secondary indices defined on any one of the the text fields?

it may not be just the primary index that is violated. If you have a unique index defined on one of the text fields, you may have a violation because an identical text already exists. If you define indices on the text fields, make sure that they allow duplicates.

Tim_Hare · July 6, 2017, 8:04pm

Are you sure the GUID is not actually stored as zero? If there is no match, rs would not be nil, but rs.EOF would be True and the recordset would be empty. So, is rs.EOF true or false?

LAM_YUK_HONG · July 7, 2017, 1:29am

@Louis, no:

  dim Loc_Sql as string = "CREATE TABLE MEM_LANGUAGE " +_
  "(GUID integer, " +_
  "Label nvarchar(30), " +_
  "English nvarchar(100), " +_
  "TChinese nvarchar(100), " +_
  "SChinese nvarchar(100), " +_
  "Primary Key (GUID))"
  
  LocDB.MultiUser = true
  LocDB.SQLExecute(Loc_sql)

@Tim, no, I have a module to display the entire content in the table, no zero GUID

Greg_O_Lone · July 7, 2017, 10:43am

[quote=339316:@Tony Lam]@Louis, no:

  dim Loc_Sql as string = "CREATE TABLE MEM_LANGUAGE " +_
  "(GUID integer, " +_
  "Label nvarchar(30), " +_
  "English nvarchar(100), " +_
  "TChinese nvarchar(100), " +_
  "SChinese nvarchar(100), " +_
  "Primary Key (GUID))"
  
  LocDB.MultiUser = true
  LocDB.SQLExecute(Loc_sql)

@Tim, no, I have a module to display the entire content in the table, no zero GUID[/quote]
Have you tried another tool to look at the table, just in case your “other module” has a bug?

Neil_Burkholder · July 7, 2017, 11:21am

You should be using a prepared statement. It could be that the Label text is causing a problem especially with Chinese characters.

Another thing is before inserting you could do:

Loc_Sql = "SELECT GUID, Label, English, TChinese FROM MEM_LANGUAGE WHERE GUID = " + rs.Field("GUID").StringValue

This would tell you if the GUID really exists or not.

LAM_YUK_HONG · July 7, 2017, 1:06pm

@Greg, not quite understand how to use another tool to look into an in-memory db

@Neil, all labels are English for ease of programming

Jean-Yves_Pochez · July 7, 2017, 1:20pm

to check for these, I temporary make the database on disk, and open it with another sqlite tool to check what’s inside.

Neil_Burkholder · July 7, 2017, 1:23pm

Use db.backup

Then open in any SQLite tool.

LAM_YUK_HONG · July 8, 2017, 4:58am

Yes, I did dump the content to a MySql table, can’t find anything special.

Jeff_Tullin · July 8, 2017, 5:18am

You use the variable rs for both the SQLLite and MYSQL databases
You change it from one to the other using this line:

If your method to get the other table FAILS to change the databases, rs will still point to the original one, in which the GUID does exist.

Try using two variables and see if this gets you more clarity

(untested code… obviously…)

dim Loc_Sql as string
dim rs as RecordSet
dim MYSQLrs as Recordset
dim ReturnStr as string

Loc_Sql = "SELECT GUID, Label, English, TChinese FROM MEM_LANGUAGE WHERE Label='" + Label + "'"
rs = session.LanguageDB.LoadTable(Loc_Sql, "app.GetLangugeString", 1)  //LanguageDB is a session instance of SQLite DB
if rs <> nil then
  if rs.Field("GUID").IntegerValue = 0 then
    Loc_Sql = "SELECT GUID, Label, English, TChinese FROM LANGUAGE WHERE Label='" + Label + "'"
    MYSQLrs = session.LoadTable(Loc_Sql)  //my own defined method to retrieve record from MySQL DB
    if MYSQLrs <> nil  and not MYSQLrs.eof then
      if MYSQLrs.field("GUID").IntegerValue <> 0 then
        Select Case Language
        Case app.kLanguageEnglish
          ReturnStr = MYSQLrs.Field("English").StringValue
        Case app.kLanguageChinese
          ReturnStr = MYSQLrs.Field("TChinese").StringValue
        End Select
        
        //Add entry to In Memory SQLite Language DB
        dim rec as new DatabaseRecord
        rec.IntegerColumn("GUID") = MYSQLrs.Field("GUID").IntegerValue
        rec.Column("Label") = MYSQLrs.Field("Label").StringValue
        rec.Column("English") = MYSQLrs.Field("English").StringValue
        rec.Column("TChinese") = MYSQLrs.Field("TChinese").StringValue

        session.LanguageDB.InsertRecord("MEM_LANGUAGE", rec)
        if session.LanguageDB.Error then
          msgbox ("session.GetLanguageString Error: " + session.LanguageDB.ErrorMessage + " : " + str(rs.Field("GUID").IntegerValue) + " : " + rs.Field("Label").StringValue)
         end if

      end if
    end if
  else
    Select Case Language
    Case app.kLanguageEnglish
      ReturnStr = rs.Field("English").StringValue
    Case app.kLanguageChinese
      ReturnStr = rs.Field("TChinese").StringValue
    End Select
  end if
end if

if WithColon = true then
  ReturnStr = trim(ReturnStr) + ":"
end if

return ReturnStr

LAM_YUK_HONG · July 8, 2017, 5:24am

Thanks Jeff, will have it a test right away, but it needs time to prove as it does not happen always.

Jeff_Tullin · July 8, 2017, 5:43am

*time away from the keyboard and I’m not sure I have the use of the 2 databases correct in my mind.
But I would still recommend 2 variables to make debugging easier.

One of the worst pieces of code I ever had to debug and fix (when a company I worked for bought code from another company)
used global variants like VALUE1, VALUE2 for money or names, or addresses indiscriminately/randomly depending upon which subroutine they were being used in.