I'm currently facing an issue with inserting spreadsheet data into a MySQL database. The problem arises when there are empty cells in the 'CUSTOMER' column, resulting in the following error message when attempting to execute the code:
"Incorrect integer value: '' for column 'CLIENTE' at row 1"
My attempt to replace empty cells with a 'null' value while preparing the query has not been successful.
I am grateful for any assistance provided.
Thank you
Spreadsheet:
function writeManyRecords() {
const conn = Jdbc.getConnection(dbUrl, user, userPwd);
conn.setAutoCommit(false);
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('FREEZERS');
const data = sheet.getDataRange().getValues();
const start = new Date();
var stmt = conn.prepareStatement('INSERT INTO FREEZERS ' + '(PATRIMONIO,DESCRICAO,CLIENTE,LOCAL_ESTOQUE,LOCAL_ANTERIOR_ESTOQUE) values (?, ?, ?, ?, ?)');
for (var i = 1; i < data.length; i++) {
stmt.setString(1,data[i][0]);
stmt.setString(2,data[i][1]);
if(!data[i][2] === '') {
stmt.setString(3,data[i][2]);
}
stmt.setNull(3,4);
stmt.setString(4,data[i][3]);
stmt.setString(5,data[i][4]);
stmt.addBatch();
Logger.log('patrimonio: '+data[i][0]+'descricao: ' + data[i][1] +' cliente: '+ data[i][2])
}
const batch = stmt.executeBatch();
conn.commit();
conn.close();
const end = new Date();
Logger.log('Time elapsed: %sms for %s rows.', end - start, batch.length);
}