Creating dynamic Prepared Statements to insert into database

Question

I'm trying to write one row of data into a Google Cloud SQL database, using a prepared statement as described here:

https://developers.google.com/apps-script/guides/jdbc

I have the data stored in an object called valuesByTitle, which looks like this:

{NAME: 'fun event', LOCATION: '123 Main St.'}

The data is inserted into the SQL table if I write everything out like this:

var conn = getConnection(); // connects to db
var stmt = conn.prepareStatement('INSERT INTO events '
  + '(NAME, LOCATION) values (?, ?)');
stmt.setString(1, valuesByTitle['NAME']);
stmt.setString(2, valuesByTitle['LOCATION']);
stmt.execute();
}

However, I would like to automate the process, so I don't have to change the code each time the variables change.

I have two new variables, one for the column names and one for a set of place holders (e.g. "?") for the insert statement.

var columns = Object.keys(valuesByTitle);
var placeholders = columns.map(function(column) { 
return "?";
};

Using these, this prepared statement should automatically insert whatever is in the valuesByTitle object (assuming all the values are string values):

var conn = getConnection();  
var stmt = conn.prepareStatement("INSERT INTO events (" + columns.join(",") + ") VALUES (" + placeholders.join(",") + ")");
for (i = 0; i < columns.length; i++) {
stmt.setString(i+1, valuesByTitle[columns[i]]);  
}
stmt.execute();

For some reason, it's not inserting the data. Surprised not to find any examples either. Is the logic off or is it just not possible to do?

Thanks.

Answer 1

All your code is OK, you simply forgot to close the map() bracket i.e.

var columns = Object.keys(valuesByTitle);
  var placeholders = columns.map(function(column) { 
  return "?";
};

should be

var columns = Object.keys(valuesByTitle);
var placeholders = columns.map(function(column) { 
  return "?";
});

(note the ending bracket after return "?";})