Description
Hi
I have had great success with mysqlconnector . It works very well , it took a bit of effort to get to work in vb.net
Now it works very stably.
I have written some really big SQL procedure functions scripts which I need to load into the MYSQL db using
vb.net and mysqlconnector .
What is the best way to do it , as at present it does not seem to work for this specific ?
This is an example sql procedure script
=========================
DROP PROCEDURE IF EXISTS Search_Error_activity_status_count_reports ; --- Important this ís added deletes the function if it is already in the db.
-- uses procedure : CREATE PROCEDURE Search_Error_activity_status_reports(export_tmp_table_name boolean)
DELIMITER //
CREATE PROCEDURE Search_Error_activity_status_count_reports()
BEGIN
SELECT Error_Code , count(*) as 'Count of Error_code type' , Procedure_Code from tmp_table_name group by Error_Code;
DROP TABLE IF EXISTS tmp_table_name ;
END;
This is the debug dump from vb.net
========================
binary_read_file_and_parse_file : file to read : C:\work\pc_software\vb.net_database\vb_db_test_1\bin\Debug\net5.0-windows\Search_Error_activity_status_count_reports_MYSQL_SQL.txt
File processing : C:\work\pc_software\vb.net_database\vb_db_test_1\bin\Debug\net5.0-windows\Search_Error_activity_status_count_reports_MYSQL_SQL.txt :: Number of bytes read = 2552
Bytes processed : 0
binary_read_file_and_parse_file : complete : file : C:\work\pc_software\vb.net_database\vb_db_test_1\bin\Debug\net5.0-windows\Search_Error_activity_status_count_reports_MYSQL_SQL.txt :: Number of bytes read = 2552
File sent to sql engine
Exception thrown: 'MySqlConnector.MySqlException' in MySqlConnector.dll
Exception thrown: 'MySqlConnector.MySqlException' in System.Private.CoreLib.dll
Exception thrown: 'MySqlConnector.MySqlException' in System.Private.CoreLib.dll
sql engine file processed
This is the dump from the mysql db log file
==============================
210702 13:24:21 20 Query SET NAMES utf8mb4
It is like the mysqlconnector driver is throwing itself in a knot !!!
This is my first try in VB.net
======================================
Public Function load_sql_procedure_to_db(sql_text As String) As Boolean
' select version();
Dim test_result As Boolean = False
Try
' ---------------------------------------------------------------------------------------------------
'
' tested Fully working
'
'----------------------------------------------------------------------------------------------------
'
' Function to read an whole db-table and dump it to a datatable
'
Dim op As String = ""
Dim dt_op As New DataTable
Dim dt_table_schema As New DataTable
Dim row As DataRow
Dim db_connection As MySqlConnection = New MySqlConnection(sql_connection)
Using db_connection
db_connection.Open()
Dim db_table_select As String = sql_text ' check if the db talks back with erors
Dim db_command As MySqlCommand = New MySqlCommand(db_table_select, db_connection)
Dim reader As MySqlDataReader
reader = db_command.ExecuteReader()
'
' Continously read the data base table until there are no more rows to read
'
'dt_table_schema = reader.GetSchemaTable() ' get data schema from db and place into table to get column headings and datatype
'For Each row_in_schema As DataRow In dt_table_schema.Rows
'dt_op.Columns.Add(New DataColumn(row_in_schema("ColumnName"), row_in_schema("DataType")))
'Next
'While reader.Read()
'row = dt_op.NewRow()
'For j As Integer = 0 To (reader.VisibleFieldCount - 1)
'row.Item(j) = reader.GetValue(j)
'Next
'dt_op.Rows.Add(row)
'End While
db_connection.Close()
End Using
test_result = True
Catch ex As Exception
test_result = False
End Try
Return test_result
End Function
======================================