0

I am working on an integration between a form on my Joomla webpage to Google sheets.

I am importing the data from the mySQL database using Google Apps Script.

I am using this script to import the data from my online form:

var server = 'SERVER';
var port = PORT;
var dbName = 'DATABASE';
var username = 'USERNAME';
var password = 'PASSWORD';
var url = 'jdbc:mysql://'+server+':'+port+'/'+dbName;

function readData() {
  var conn = Jdbc.getConnection(url, username, password);
  var stmt = conn.createStatement();
  var results = stmt.executeQuery('SELECT * FROM XXXX_facileforms_records');
  var metaData=results.getMetaData();
  var numCols = metaData.getColumnCount();
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getSheetByName('Import Bug Reports');
  sheet.clearContents();
  var arr=[];
  for (var col = 0; col < numCols; col++) {
    arr.push(metaData.getColumnName(col + 1));
  }
  sheet.appendRow(arr);
while (results.next()) {
  arr=[];
  for (var col = 0; col < numCols; col++) {
    console.log (col+1)
    if (results.getString(col + 1)==0||'0000-00-00 00:00:00') continue // java.sql.Timestamp == '0000-00-00 00:00:00'
    else {arr.push(results.getString(col + 1));
    console.log (col+1)}
  }
  sheet.appendRow(arr);
}
results.close();
stmt.close();
}

I get the error: Exception: Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp

I have to admit that I have very limited knowledge of SQL, so I tried making a workaround where the script simply skips the affected values (I am pretty sure they are not relevant to the user form input I need).

However, the continue statement doesn't seem to work as intended. Anyone with an idea how to work around this error?

I have looked at this question: Google Apps Script, JDBC and MySQL does not work with Date 0000-00-00 but really can't understand how this should be implemented in the script? (I plan to setup the script to run automatically every hour or so to always give me the latest data).

Akina
  • 39,301
  • 5
  • 14
  • 25
Lars Ejaas
  • 153
  • 9
  • use some predefined value which is valid (from the distant past or the distant future). – Akina Mar 14 '20 at 10:57
  • Thanks Akine. However I have a hard time understanding how that would be implemented in the code? Should I alter the Var results somehow? – Lars Ejaas Mar 14 '20 at 11:20
  • *I have a hard time understanding how that would be implemented in the code?* Simply replace `'0000-00-00 00:00:00'` with some literal valid for datetime (use MINVALUE or MAXVALUE for the datatype). – Akina Mar 14 '20 at 11:47

0 Answers0