I'm trying to insert the spreadsheet data into a mysql database. The problem is that some cells in the 'CUSTOMER' column are empty and this causes the following error when trying to run the code:

"Incorrect integer value: '' for column 'CLIENTE' at row 1"

I'm trying to replace empty cells with 'null' value when preparing the query, but I'm not getting it.

I thank the help of all.

Thanks

Spreadsheet: enter image description here

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);
  } 

CodePudding user response：

As a guess:

if (!data[i][2] === '') { stmt.setString(3,data[i][2]) }
else { stmt.setNull(3,0) } // second number: 0 - null, 4 - integer, etc

How to use registerOutParameter in google script?