How to rollback executed queries from text file executed in cmd if query failed?-CodePudding

I have a program that exports a text file that contains queries (insert and delete queries) which will then needed to be imported in the same program with different database connection (like a back-up and restore).

I have successfully created a feature to export and import the text file which runs the queries in cmd. My problem is, how do I rollback the executed queries if it fails in the middle of execution?

I know how to do COMMIT-TRANSACTION-AND ROLLBACK if it is executed within the vb.net code, but not when executed inside the command prompt.

This is a sample data from my text file:

DELETE FROM io_generated WHERE (`id`='220401-00001-015' AND employee_id=1) OR (`id`='220402-00001-015' AND employee_id=1) OR (`id`='220404-00001-015' AND employee_id=1) OR (`id`='220405-00001-015' AND employee_id=1);
INSERT INTO io_generated(`id`, `employee_id`, `date`, `branch_id`, `in1`, `out1`, `in2`, `out2`, `in3`, `out3`, `in4`, `out4`, `branch1`, `branch2`, `branch3`, `branch4`, `branch5`, `branch6`, `branch7`, `branch8`, `in1_edited`, `out1_edited`, `in2_edited`, `out2_edited`, `in3_edited`, `out3_edited`, `in4_edited`, `out4_edited`, `late1`, `late2`, `late3`, `late4`, `hrs1`, `hrs2`, `ot_hrs`, `ut1`, `ut2`, `ut3`, `ut4`, `day_count`, `date_generated`, `generated_by`, `is_edited`, `edited_by`, `last_date_edited`, `uploaded`, `upload_date`, `verified`) VALUES('220401-00001-015', 1, '2022-04-01', 15, "2022-04-01 07:46:03", NULL, NULL, "2022-04-01 12:03:23", "2022-04-01 12:49:16", NULL, NULL, "2022-04-01 17:27:26", 15, NULL, NULL, 15, 15, NULL, NULL, 15, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 4, 4, 0, 0, 0, 0, 0, 1, "2022-04-29 09:51:20", 599, 0, NULL, NULL, 2, NOW(), 0),('220402-00001-015', 1, '2022-04-02', 15, "2022-04-02 07:37:35", NULL, NULL, "2022-04-02 12:09:03", "2022-04-02 12:50:25", NULL, NULL, "2022-04-02 17:01:24", 15, NULL, NULL, 15, 15, NULL, NULL, 15, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 4, 4, 0, 0, 0, 0, 0, 1, "2022-04-29 09:51:21", 599, 0, NULL, NULL, 2, NOW(), 0),('220404-00001-015', 1, '2022-04-04', 15, "2022-04-04 09:02:25", NULL, NULL, "2022-04-04 12:05:04", "2022-04-04 12:49:17", NULL, NULL, "2022-04-04 17:02:59", 15, NULL, NULL, 15, 15, NULL, NULL, 15, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2.97, 4, 0, 1.03, 0, 0, 0, 1, "2022-04-29 09:51:22", 599, 0, NULL, NULL, 2, NOW(), 0),('220405-00001-015', 1, '2022-04-05', 15, "2022-04-05 07:53:10", NULL, NULL, "2022-04-05 12:05:39", "2022-04-05 12:56:48", NULL, NULL, "2022-04-05 17:01:25", 15, NULL, NULL, 15, 15, NULL, NULL, 15, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 4, 4, 0, 0, 0, 0, 0, 1, "2022-04-29 09:51:23", 599, 0, NULL, NULL, 2, NOW(), 0);
INSERT INTO io_official(`id`, `employee_id`, `date`, `branch_id`, `log`, `type`, `uploaded`, `upload_date`) VALUES('220401-074603-00001', 1, '2022-04-01', 15, '2022-04-01 07:46:03', 'In', 2, NOW()),('220401-120323-00001', 1, '2022-04-01', 15, '2022-04-01 12:03:23', 'Out', 2, NOW()),('220401-124916-00001', 1, '2022-04-01', 15, '2022-04-01 12:49:16', 'In', 2, NOW()),('220401-172726-00001', 1, '2022-04-01', 15, '2022-04-01 17:27:26', 'Out', 2, NOW()),('220402-073735-00001', 1, '2022-04-02', 15, '2022-04-02 07:37:35', 'In', 2, NOW()),('220402-120903-00001', 1, '2022-04-02', 15, '2022-04-02 12:09:03', 'Out', 2, NOW()),('220402-125025-00001', 1, '2022-04-02', 15, '2022-04-02 12:50:25', 'In', 2, NOW()),('220402-170124-00001', 1, '2022-04-02', 15, '2022-04-02 17:01:24', 'Out', 2, NOW()),('220404-090225-00001', 1, '2022-04-04', 15, '2022-04-04 09:02:25', 'In', 2, NOW()),('220404-120504-00001', 1, '2022-04-04', 15, '2022-04-04 12:05:04', 'Out', 2, NOW()),('220404-124917-00001', 1, '2022-04-04', 15, '2022-04-04 12:49:17', 'In', 2, NOW()),('220404-170259-00001', 1, '2022-04-04', 15, '2022-04-04 17:02:59', 'Out', 2, NOW()),('220405-075310-00001', 1, '2022-04-05', 15, '2022-04-05 07:53:10', 'In', 2, NOW()),('220405-120539-00001', 1, '2022-04-05', 15, '2022-04-05 12:05:39', 'Out', 2, NOW()),('220405-125648-00001', 1, '2022-04-05', 15, '2022-04-05 12:56:48', 'In', 2, NOW()),('220405-170125-00001', 1, '2022-04-05', 15, '2022-04-05 17:01:25', 'Out', 2, NOW());
INSERT INTO io_unofficial(`id`, `employee_id`, `date`, `branch_id`, `log`, `type`, `remarks`, `uploaded`, `upload_date`) VALUES('220404-142625-00001', 1, '2022-04-04', 15, '2022-04-04 14:26:25', 'Out', "online payment at SM Pili", 2, NOW()),('220404-144509-00001', 1, '2022-04-04', 15, '2022-04-04 14:45:09', 'In', NULL, 2, NOW());

I can use this code if the text file queries are line by line, where I can rollback the transaction if it fails:

Execute directly using ReadLine

Dim conn As New MySqlConnection(AMSLocalCS)
Try
    For Each updateFile As String In openFileDialog1.FileNames
        conn.Open()
        DB.START_TRANSACTION(conn)
        While (reader.Peek() <> -1)
            line = reader.ReadLine()
            If line.StartsWith("<item key=""") Then
                Dim Firstpart As String = Nothing
                Firstpart = line.Substring(11, line.IndexOf(""" value=") - 11)
                Debug.WriteLine(Firstpart)
                lines = lines   1
                Application.DoEvents()
            Else
                command = New MySqlCommand(line, conn)
                command.ExecuteNonQuery()
                Application.DoEvents()
            End If
        End While
        reader.Close()
        DB.COMMIT(conn)
        System.IO.File.Delete(updateFile)
    Next
    MsgBox("Update Complete!", MsgBoxStyle.Information, "Updated")
Catch ex As MySqlException
    DB.ROLLBACK(conn)
Catch ex As Exception
    DB.ROLLBACK(conn)
Finally
    conn.Close()
End Try

But the problem is, sometimes the textfile contains an enter or line-feed which will make the queries not in line. So I decided to use this code where the queries are executed directly in the command prompt, this is a working code but I don't know how to rollback if it fails:

Execute text file in cmd

Dim myProcess As New Process()
myProcess.StartInfo.FileName = "cmd.exe"
myProcess.StartInfo.UseShellExecute = False
myProcess.StartInfo.WorkingDirectory = mysql_directory
myProcess.StartInfo.RedirectStandardInput = True
myProcess.StartInfo.RedirectStandardOutput = True
myProcess.Start()
Dim myStreamWriter As StreamWriter = myProcess.StandardInput
Dim mystreamreader As StreamReader = myProcess.StandardOutput

myStreamWriter.WriteLine("mysql -u " & My.Settings.amsLocalUid & " --password=" & My.Settings.amsLocalPwd & " -h " & My.Settings.amsLocalServer & " """ & My.Settings.amsLocalDatabase & """ < """   file   """ ")
myStreamWriter.Close()
myProcess.WaitForExit()
myProcess.Close()
MsgBox(filetype & " File Imported Successfully!", MsgBoxStyle.Information, "Imported")
System.IO.File.Delete(file)

Is there a way so I can check if the queries failed to execute in the command prompt and rollback them after failing?

CodePudding user response：

So, this is what I did, according to @danblack's suggestion I should just continue with reading the files and executing them directly in vb.net code rather than using the cmd. Here's what I did, instead of reading the lines of the text file 1-by-1, I used the File.ReadAllText() function so I can easily detect if the query fails using Try Catch.

Code Snippet

.....
Try
    For Each updateFile As String In openFileDialog1.FileNames
        conn.Open()
        DB.START_TRANSACTION(conn)
        Dim script As String
        script = File.ReadAllText(updateFile) ' use this insead of .ReadLine

        Using cmd As New MySqlCommand(script, conn)
            cmd.ExecuteNonQuery()
        End Using

        DB.COMMIT(conn)
        System.IO.File.Delete(updateFile)
    Next
    MsgBox("Update Complete!", MsgBoxStyle.Information, "Updated")
Catch ex As MySqlException
    DB.ROLLBACK(conn)
Catch ex As Exception
    DB.ROLLBACK(conn)
Finally
    conn.Close()
End Try
.....