B4J Question issue when inserting records into sqlite database [SOLVED]

[Peter Lewis]

Hi All
I have been puzzling over my code to get some data from a MySQL database and saving it into the local sqlite database. The local SQLite database has 2 columns in the key.

I have added code to make sure I am not adding in a record that is already there.

What is is doing is correctly getting the first record and then saving it. When it loops to next row I am getting an error when adding it stating that there is a duplicate. At this stage I only have one record in the file and the Log values just before are correct and are not creating a duplicate. However there are 3 records that need to be added. The other 2 give me this error.

Then coming into the sub, i Get the next record saving correctly and then the other 2 fail

the third time I run the sub, the last record comes through and the other 2 fail. They should all have come though the first time i ran the procedure. I have spent a few hours trying different options and LOTS of (LOG) commands but somehow does not seem to want to work for me.

I think I need another person to see the code in a different view.

SQL2 is MySQL remote database and sql1 is the local Sqlite database.


Thank you

CREATE TABLE Joined (
    game_id   NUMERIC,
    userID    NUMERIC,
    Position  NUMERIC,
    Loginname TEXT,
    PRIMARY KEY (
        game_id,
        userID
    )
);

CREATE TABLE `Joined_db` (
  `game_id` int(11) NOT NULL,
  `userID` int(11) NOT NULL,
  `Position` int(3) NOT NULL,
  `LoginName` varchar(20) NOT NULL,
  KEY `game_id_idx` (`game_id`),
  KEY `userID_idx` (`userID`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=latin1

    Dim rp As ResultSet '
    rp = sql2.ExecQuery2("Select game_id, userID, Position, LoginName FROM Joined_db WHERE `game_id` LIKE ?",Array As String(dtaGameID.Text))
    Dim ListOfMaps As List
    ListOfMaps.Initialize
    Do While rp.NextRow
       
       
        Dim rem  As Map
        rem.Initialize
        rem.Put("userID", rp.Getint("userID"))
        rem.Put("game_id", rp.Getint("game_id"))
        rem.Put("LoginName", rp.GetString("LoginName"))
        rem.Put("Position",rp.Getint("Position"))

   
            If sql1.IsInitialized = False Then
                sql1.InitializeSQLite(File.DirApp, "data/userinfo.db", True)
            End If

        Dim cursor2 As ResultSet
        cursor2 = sql1.ExecQuery2("SELECT game_id FROM Joined WHERE game_id = ? and userID = ?" , Array As String(rp.Getint("game_id"), rp.Getint("userID")))
            If cursor2.NextRow Then
               
               
                Else
                    If sql1.IsInitialized = False Then
                        sql1.InitializeSQLite(File.DirApp, "data/userinfo.db", True)
                    End If
                ListOfMaps.Add(rem)
       
            DBUTILS.InsertMaps(sql1,"Joined",ListOfMaps)
            End If
           
           
           
       
    Loop
    rp.Close

 

[OliverA]

1) No clue why your are initializing sq1 so many times, but that's not your issue
2) The issue: You're calling DBUTILS.InsertMaps too soon. As is, the following happens
0) Initialize list
a) Fetch record from MySQL
b) Add record to a new map
c) Check if exists in SQLite and if so, back to a)
d) Add map to List
e) Update SQLite with List

The issue is, every time you do d) you are ADDING and additional map to the list. So the first time you reach d) you have 1 map in the list, the second time, you have 2 and so on. Since you call InsertMaps immediately after d), the first time it tries to insert 1 record, the second time 2 (and the first record has already been added and now you get a duplicate record error), the third time 3 (and now you have two duplicate records you are trying to insert). Your flow needs to be more like

a) Initialize list
b) Fetch record from MySQL. If no records f)
c) Check if exists in SQLite and if so, back to b)
d) Add record to a new map
e) Add map to list, back to b)
f) Check size of list and if greater than 0, use InsertMaps to add all records to SQLite database

 

[Peter Lewis]

1) No clue why your are initializing sq1 so many times, but that's not your issue
2) The issue: You're calling DBUTILS.InsertMaps too soon. As is, the following happens
0) Initialize list
a) Fetch record from MySQL
b) Add record to a new map
c) Check if exists in SQLite and if so, back to a)
d) Add map to List
e) Update SQLite with List

The issue is, every time you do d) you are ADDING and additional map to the list. So the first time you reach d) you have 1 map in the list, the second time, you have 2 and so on. Since you call InsertMaps immediately after d), the first time it tries to insert 1 record, the second time 2 (and the first record has already been added and now you get a duplicate record error), the third time 3 (and now you have two duplicate records you are trying to insert). Your flow needs to be more like

a) Initialize list
b) Fetch record from MySQL. If no records f)
c) Check if exists in SQLite and if so, back to b)
d) Add record to a new map
e) Add map to list, back to b)
f) Check size of list and if greater than 0, use InsertMaps to add all records to SQLite database

Thank you will try that and let you know. I was adding in so many initi SQL in case that was the problem,
Need to take them all out now.

Somehow |i thought that one pass into the list then add the list to the DB was the way to go. Tried to search the forum but not much on Insertmaps to show the structure. Thank you for clearing that up