Ciao a tutti,
ho il seguente problema: devo utilizzare un'applicazione java che importa un file csv nel db mysql.
La connessione funziona, soltanto che ottengo il seguente errore:
"java.sql.SQLException: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement".
Cercando in rete ho trovato che per risolvere il problema occorre inserire il file da importare dentro la cartella "Uploads" data dal percorso ottenuto con il comando
SHOW VARIABLES LIKE "secure_file_priv";.
Il file in questione si trova già in tale cartella, tant'è che se provo ad eseguire l'import con il seguente comando su db mysql:
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/report.csv'
INTO TABLE chiamate_report
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(uniqueId,direction,@startTime,answerTime,endTime,callerId,gatewayId,gatewayName,callerNumber,status,waitingTime,conversationTime,operator,operatorExten,detailDestination,detailDestinationId,detailDestinationName,detailEnterTime,detailOpeningFlag,detailEnqueueTime,detailAnswerTime,detailOperator,detailOperatorExten,detailExitTime,detailExitCause,detailWaitingTime,detailWaitingTimeNoGreeting,detailNextHopType,detailNextHopId,eventOperator,operatorStartRingingTime,operatorEndRingingTime,operatorAnswerTime,operatorRingTime,operatorHangupTime,operatorTalkTime,operatorExitCause)
SET startTime = str_to_date(@startTime, '%Y-%m-%d %H:%i:%s');
l'import funziona correttamente.
Di seguito il codice java per effettuare l'import del csv nel db:
package kalreport;
import java.sql.Connection;
import java.sql.Statement;
public class ManipolazioneDB {
public static void readCsv() {
try {
Connection conn = ConnessioneDB.getConnection();
String loadQuery = "LOAD DATA INFILE ' " +
"C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/report.csv" +
" ' INTO TABLE chiamate_report FIELDS TERMINATED BY ','" +
" ENCLOSED BY '\"' LINES TERMINATED BY '\\n' IGNORE 1 ROWS" +
" (uniqueId,direction,@startTime,answerTime,endTime,callerId,gatewayId,gatewayName,callerNumber,status,waitingTime,conversationTime,operator,operatorExten,detailDestination,detailDestinationId,detailDestinationName,detailEnterTime,detailOpeningFlag,detailEnqueueTime,detailAnswerTime,detailOperator,detailOperatorExten,detailExitTime,detailExitCause,detailWaitingTime,detailWaitingTimeNoGreeting,detailNextHopType,detailNextHopId,eventOperator,operatorStartRingingTime,operatorEndRingingTime,operatorAnswerTime,operatorRingTime,operatorHangupTime,operatorTalkTime,operatorExitCause)" +
" SET startTime = str_to_date(@startTime, '%Y-%m-%d %H:%i:%s');";
Statement st = conn.createStatement();
st.executeQuery(loadQuery);
} catch (Exception e) {
System.out.println("Errore caricamento file");
e.printStackTrace();
}
}
}
Potreste aiutarmi?
Grazie