Batch Insert From SQLite Table to Remote MySQL Table

M

Mahares

Expert
Licensed User
Longtime User
I use the below code to insert records from a SQLite table to a remote MySQL table which has a primary key. I also use Erel 's php script exactly as shown on his post.
1. It does not insert all records. If I have 10 records in SQLite, it may insert 5 of them even without duplicates. How do I get it to insert all 10?
2. If there are already records in the MySQL table, exactly the same as the SQlite records, the INSERT crashes from the start and no records are transferred. How do I bypass the duplicate records in the MySQL and jump to the next record and avoid the crash?
I hope someone can help with this problem. Thank you very much.

B4X: 
Sub btnExportToMySQL_Click
  Dim F(4), D(4)  As String  'F is field names, D is data content
   Dim i, j As Int
   Cursor1.Position = 0
   For j=0 To 4-1
      F(j)=Cursor1.GetColumnName(j)
   Next
   For i=0 To Cursor1.RowCount-1
      Cursor1.Position=i
      For j=0 To 4-1
         D(j)=Cursor1.GetString(F(j))
      Next   
     ExecuteRemoteQuery("INSERT INTO mysqlremotetable (Field1, Field2, Field3, Field4" _
            & "VALUES ('" &D(0)& "','" & D(1)& "','"  & D(2) _
            & "','"  &D(3)& "')",i)
     Next    'go to next SQLite table record
End Sub   



Sub ExecuteRemoteQuery(Query As String, TaskId As Int)
   Dim req As HttpRequest
   req.InitializePost2("http://www.website.com/phpfilename.php", Query.GetBytes("UTF8"))
   hc.Execute(req, TaskId)
End Sub

Sub hc_ResponseError (RemoteResponse As HttpResponse, Reason As String, StatusCode As Int, TaskId As Int)
   Log("Error: " & Reason & ", StatusCode: " & StatusCode)
   Msgbox("Possible duplicate record, bad data or no internet."  & CRLF & " Error: "& Reason & ", StatusCode: " & StatusCode,"Record NOT inserted".ToUpperCase)
End Sub

Sub hc_ResponseSuccess (RemoteResponse As HttpResponse, TaskId As Int)
      Msgbox("Record was saved to remote server successfully.".ToUpperCase,"SUCCESS")
End Sub
 
Sort by date Sort by votes
mc73

mc73

Well-Known Member
Licensed User
Longtime User
I think I've read about this problem again in the past, but unfortunately I don't remember the suggested solution. Anyway, my idea is this: Send your queries as one string and then let php handle each one of them. You can, for example, send your queries delimited by something like '//'. Then, let php split the result and perform a loop, there at the server side, in order to have your queries safely performed. Again, that's just an idea.

Just saw an erel's post and he is right. No need for separator, just use the default ';' and php will handle the multi query I guess
 
Last edited:
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
Can someone please take a look at my B4A code in the first post and see if it is properly coded using B4A. Not being familiar with PHP and its code, I prefer the queries are done in B4A if it is doable. If I do not have to modify or tamper with the Erel's original PHP script, that would be the best approach. If I must create the queries through PHP, then I may have to abandon the pursuit to solve this problem.
Thank you
 
Upvote 0
Erel

Erel

B4X founder
Staff member
Licensed User
Longtime User
Upvote 0
eps

eps

Expert
Licensed User
Longtime User
Surely you just query the MySQL database to see if the record exists, before inserting, if it does, don't insert and keep going round the loop.

Or have I missed something here?
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
I am still looking for some help with my button click code in the 1st post to give me some guidance. The INSERT is done in a very erratic fashion as described below:
1. If I have 10 records in the SQLite table and click the button to insert them to the MySQL table, only 5 are inserted, despite NO duplicates in the MySQL table. No error is shown in the log.
2. If I exit the app with Activity.finish and reenterthe app, the 6th record is inserted without even clicking the button to initiate the insert.
3. If I exit and get back again, the 7th gets inserted on its own.
It is a very strange behavior. For all this, I am using the code I posted in Post#1 and Erel's famous PHP script. If someone has a more sure way and can share some code tips, that would be great.
In summary, what I am looking for is: At the end of the day all SQLite records which can be up to 100 are inserted into the MySQL remote table with the click of the button for which I have the code in post #1.
Thank you.
 
Upvote 0
mc73

mc73

Well-Known Member
Licensed User
Longtime User
Ok, back.

Taking a closer look at your code, I see that you simply want to do an insertion in the same table. I think there is no need to use msqli multiple queries, since this can easily be done by using INSERT INTO youtable VALUES (...,...,...),(...,...,...),...
I've just tested the following code and it works for me.

B4A - The only modification is the part where we create the string containing the values as mentioned above.
B4X: 
'Activity module
Sub Process_Globals
    Dim hc As HttpClient
    
End Sub

Sub Globals
    
End Sub

Sub Activity_Create(FirstTime As Boolean)
    If FirstTime Then
        hc.Initialize("hc")
    End If
    ProgressDialogShow("Sending a multi-insertion string")
    Dim aTaskID As Int, myPHP As String 
    aTaskID=1:myPHP="http://yourSiteHere/b4aphp.php"
    Dim aMultiInsertion As String 
    For i=0 To 99
    Dim testRow As String 
    testRow="testRow" & (i+1)
    aMultiInsertion=aMultiInsertion & ",(null,'" & testRow & "')"
    Next
    aMultiInsertion="INSERT INTO justatest VALUES " & aMultiInsertion.substring(1)
    ExecuteRemoteQuery(aMultiInsertion, aTaskID,myPHP)
End Sub
Sub ExecuteRemoteQuery(Query As String, TaskId As Int,myPHP As String)
    Dim req As HttpRequest
    req.InitializePost2(myPHP, Query.GetBytes("UTF8"))
    hc.Execute(req, TaskId)
End Sub
Sub hc_ResponseError (Response As HttpResponse, Reason As String, StatusCode As Int, TaskId As Int)
    Log("Error: " & Reason & ", StatusCode: " & StatusCode)
    If Response <> Null Then
        Msgbox(Response.GetString("UTF8"),"response")
        Response.Release
    End If
    ProgressDialogHide
End Sub
Sub hc_ResponseSuccess (Response As HttpResponse, TaskId As Int)
    Dim res As String
    res = Response.GetString("UTF8")
    Msgbox("Response from server: " & res,"response")
    ProgressDialogHide
    Response.Release
End Sub


Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub
PHP - just a slight modification in order to return a 'true' or 'false' (1 or 0) when we send nonQueries.
PHP: 
 <?php

$databasehost = "yourHost";
$databasename = "yourDB";
$databaseusername ="YourName";
$databasepassword = "YourPassword";

$con = mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
mysql_select_db($databasename) or die(mysql_error());
$query = file_get_contents("php://input"); 
$sth = mysql_query($query);

if (mysql_errno()) { 
    header("HTTP/1.1 500 Internal Server Error");
    echo $query.'\n';
    echo mysql_error(); 
}
else
{    
    // here's where I modify Erel's code a bit. I didn't want to 'destroy' its structure
    // so I simply added an if, which will return true when we send a multiple insertion and it succeeds.
    // Otherwise, it will return the 'ordinary' expected arrays we want to process.
    $rows = array();
    if (is_bool($sth)){$rows[0]=(int)$sth;} else
    {
    while($r = mysql_fetch_assoc($sth)) {
        $rows[] = $r;
        }}
    print json_encode($rows);
}
?>
Please note that the above code will not work for multiple queries in general, i.e. a multi-select plus a multi-insert and so on. If this is the case, you should consider using mysqli or my suggestion with seperators (this can turn to be slow though). Good night.
 
Last edited:
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
@mc73: Thank you so much for taking the time. I am going to try to adapt the code you created and tweak it to make it apply to my application in terms of B4A and PHP. The aMultiInsertion string will the tough one as I actually have 54 fields in the real table in each record to concatenate and up to 100 records to insert at a time exracted from a SQLite table. I will absolutely report back to you.
Again, thank you for your valuable contribution.
 
Upvote 0
mc73

mc73

Well-Known Member
Licensed User
Longtime User
Mahares said:
@mc73: Thank you so much for taking the time. I am going to try to adapt the code you created and tweak it to make it apply to my application in terms of B4A and PHP. The aMultiInsertion string will the tough one as I actually have 54 fields in the real table in each record to concatenate and up to 100 records to insert at a time exracted from a SQLite table. I will absolutely report back to you.
Again, thank you for your valuable contribution.
Click to expand...
please note that in large files posting, you may run in trouble because php may process may run out of time. If you face this, consider altering the max_input_time and max_execution_time in the php.ini file at your server.
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
@mc73: I adapted your code so it works with my application. You are terrific. It works well. I have not inserted a large number like 100 records, but inserted 10 records simultaneously. I wil get a chance to test it with a large insert.
Also, for your information, in order to bypass DUPLICATE RECORDS that exist in the MySQL table, I used: INSERT IGNORE. Otherwise, it crashes.
Thank you again for your valuable support.
 
Upvote 0
You must log in or register to reply here.
Top