0

I need to make the MySQL query "LOAD DATA LOCAL INFILE..." statement as parameterized SQL Query that accepts the value from variable. But unable to resolve it. The query along with C# code is given as below:

The SQL used is:

const string TableNameParam = "@tablename";
const string FieldDelimiterParam = "@fieldDelimiter";
const string ColSqlParam = "@colSql";

var sqlQuery = $"LOAD DATA LOCAL INFILE '{FilePathParam}' 
INTO TABLE {TableNameParam} FIELDS TERMINATED BY '{FieldDelimiterParam}' {ColSqlParam}";

cmd.CommandText = sqlQuery;
cmd.Parameters.AddWithValue(FilePathParam, filePath);
cmd.Parameters.AddWithValue(TableNameParam, tableName);
cmd.Parameters.AddWithValue(FieldDelimiterParam, fieldDelimiter);
cmd.Parameters.AddWithValue(ColSqlParam, colSql.ToString().ToUpper());
cmd.ExecuteNonQuery();

It throws the same exception with message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? FIELDS TERMINATED BY '@fieldDelimiter' ?' at line 1

It seems like the SQL syntax error, but picking & mapping the individual value from the parameters does not give any syntax error.

Similarly, I have also tried by using the StoredProcedure for the same as below:

cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@filePath", filePath);
cmd.Parameters.AddWithValue("@tablename", tableName);
cmd.Parameters.AddWithValue("@fieldDelimiter", fieldDelimiter);
cmd.Parameters.AddWithValue("@colSql", colSql.ToString().ToUpper());
cmd.ExecuteNonQuery();

The StoredProcedure used is as below:

CREATE PROCEDURE `sp_AggregateReport_LoadData`(
    IN filePath VARCHAR(255), IN tablename VARCHAR(50), 
    IN fieldDelimiter VARCHAR(5), colSql VARCHAR(2500)
)
BEGIN
    SET @sqlQuery= CONCAT("LOAD DATA LOCAL INFILE '",filePath,"' INTO TABLE ", tablename," FIELDS TERMINATED BY '",fieldDelimiter,"' ",colSql);
    PREPARE stmt FROM @sqlQuery;
    EXECUTE stmt;
END

It throws the exception with message: This command is not supported in the prepared statement protocol yet

Does anyone having the same issue previously using the parameterized MySQL query? Need help.

Kamal Bhusal
  • 31
  • 1
  • 1
  • 4
  • 'SQL Syntax Permitted in Prepared Statements' - https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html https://stackoverflow.com/questions/44360756/mysql-load-data-this-command-is-not-supported-in-the-prepared-statement-protoco – P.Salmon Sep 13 '22 at 07:12
  • @P.Salmon Thanks but, I am not asking for how to use prepared statement for the parameterized mysql in general, but asking to use for that "load data local infile" statement. – Kamal Bhusal Sep 13 '22 at 07:23
  • You cannot parametrize objects names (file, table or column name). You must construct final query SQL code in your application. – Akina Sep 13 '22 at 07:50
  • Thanks @Akina for sharing the information. Can you please share addition information why it is not allowed & is there any mysql documentation confirming this(if any)? – Kamal Bhusal Sep 13 '22 at 08:11
  • ?? P.Salmon have provided you the link to the documentation already. – Akina Sep 13 '22 at 08:16

0 Answers0