Android Question Inserting Records into Mysql Table using ExecuteBatch

Kiumbe

Member
Licensed User
Hello members,

Am still new to B4A and on the learning curve. Am working on an app that will post several insert statements into MYSQL database at once when the user licks on post button. The inserts affects 3 tables, after some few searches i found an example of using ExecuteBatch to run batch statements though with no clear documentations. From the example provided, am able to do an insert in one table using an array() as below

B4X:
Sub InsertRecords (Names() As String)
    Dim cmdList As List
    cmdList.Initialize
    For Each Name As String In Names
        Dim cmd As DBCommand = CreateCommand("insert_meter_readings", Array(Name))
        cmdList.Add(cmd)
    Next

    Dim j As HttpJob = CreateRequest.ExecuteBatch(cmdList, Null)
    Wait For(j) JobDone(j As HttpJob)
    If j.Success Then
        Log("Inserted successfully!")
    Else
        Log("Failed to Insert records!")
    End If
    j.Release
End Sub

If I send an insert statement with several fields, e.g.
B4X:
InsertRecords(Array As String(txtConnNo.Text,lblMeterNo.Text,records(Results.Columns.Get("Mno_Number"))
and in my jRDC I have sql.insert_reading=INSERT INTO ewa.reading (Conn_No,Meter_No,MemberNo)VALUES(?,?,?);

I get the error (SQLException) java.sql.SQLException: Column count doesn't match value count at row 1

Where am I getting it wrong on the number of fields and the parameters?

How will i execute several insert statements using ExecuteBatch for the three tables, and if their is an error while doing second insert, does ExecuteBatch rollback transactions that had been inserted in the first row to avoid incomplete entries?

Thank you in advance for this great forum.
 
Solution
Array(name) - creates a new array with a single item and puts name as this single item.

B4X:
Dim cmd As DBCommand = CreateCommand("insert_meter_readings", Name)

Kiumbe

Member
Licensed User
Perfect, thanks Erel. Am now able to save records in my MySQL table with no errors. Incase someone is also wondering about ExecuteBatch if when inserting several records and their is an error, all records will fail, it will return tru only if all items within an array have been inserted, very helpful!!
 
Upvote 0
Top