CODEWITHSUNDEEP
mysqljdbcgoogle-apps-scriptgoogle-sheets
R_life_R
R_life_R

Reputation: 816

Insert specific range in google sheets to MYSQL

I have a simple table in a sheet (from A1 till B9)

try1    try0
try3    try3
try5    try5
try7    try7
try9    try9
try11   try11
try13   try13
try15   try15
try17   try17

I am using a script to insert the records to a mysql table.

function MysqlData() {
   var conn = Jdbc.getConnection('jdbc:mysql://mysite/myddb', 'user', 'pwd'); 
  var stmt = conn.createStatement();
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();

  for (var i = 0; i < data.length; i++) {
  var sql = "INSERT INTO test (field1, field2) VALUES ('" + data[i][0] + "'," + data[i][1] + ")";
  var count = stmt.executeUpdate(sql,1)
  }

  stmt.close();
  conn.close();
}

when executing this script on the page including the table, i get the error :

Unknown column 'try0' in 'field list'

What is wrong in my code?

Upvotes: 2

Views: 132

Answers (1)

TheMaster
TheMaster

Reputation: 50644

You quoted ('') value1, but missed value2:

var sql = "INSERT INTO test (field1, field2) VALUES ('" + data[i][0] + "','" + data[i][1] + "')";

However, directly sending strings opens you up to sql injections. Use parameterized statements:

  var sql = "INSERT INTO test (field1, field2) VALUES (?, ?)";
  var stmt = conn.prepareStatement(sql);
  for (var i = 0; i < data.length; i++) {
    stmt.setString(1, data[i][0]);
    stmt.setString(2, data[i][1]);
    stmt.addBatch();
  }
  stmt.executeBatch();

References:

Upvotes: 2

Related Questions