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
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
sqlite database layout:
CREATE TABLE Joined (
game_id NUMERIC,
userID NUMERIC,
Position NUMERIC,
Loginname TEXT,
PRIMARY KEY (
game_id,
userID
)
);
MySQL Structure:
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
B4X:
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
Last edited: