CODEWITHSUNDEEP
mysqlgoogle-apps-script
RebeccaLourdes
RebeccaLourdes

Reputation: 25

How do I insert data using Google App Script to External Database : MySQL without using Google SpreadSheet

I have created a Web App using Google App Script and now I am trying to insert values of my Web App Form to external database. Most of the online guides are showing spreadsheet to external database connections. Is there any direct way to connect to External MySQL to insert values or perform select statements directly? In short; Google Web App(Google App Script) to MySQL(External database)

I expect a direct connection from Web App to External database without using Google Spreadsheet as medium. The reason I try to avoid Google Spreadsheet because connection to Google Spreadsheet is very slow. If there must be a medium in between Google Web App and MySQL, what is the fastest method? For C# to MySQL and vice versa, I always use DataSet/DataTable/LinkedList.

Upvotes: 0

Views: 1871

Answers (3)

RebeccaLourdes
RebeccaLourdes

Reputation: 25

This is to Insert into Database directly. Learnt difference of execute here

var insertStmnt = stmt.executeUpdate('INSERT INTO <tbl name> (<column name>) VALUES ("<value in string>")');

Upvotes: 0

RebeccaLourdes
RebeccaLourdes

Reputation: 25

Thank you very much Дмитро Булах. Managed to get it as per your advice. Below is the example to directly connect to the External Database without using Spreadsheet as medium.

function fnMySql() { 
  
  var conn = Jdbc.getConnection('jdbc:mysql://***.net:<port number>/<db name>', '<db username>', '<db pswd>');

  var stmt = conn.createStatement();
  var start = new Date(); // Get script starting time
  
  var rs = stmt.executeQuery('SELECT <tbl column name A>, <tbl column name B> FROM <tbl name> BY 1 LIMIT 1000'); // It sets the limit of the maximum number of rows in a ResultSet object
  
  var row = 0;
  var getCount = rs.getMetaData().getColumnCount();
  
  for (var i = 0; i < getCount; i++){  
    
    Logger.log(rs.getMetaData().getColumnName(i+1));
  }  
  
  var row = 1; 
  while (rs.next()) {
    for (var col = 0; col < rs.getMetaData().getColumnCount(); col++) { 
      
      Logger.log(rs.getString(col + 1));
    }
    row++;
  }
  
  rs.close();
  stmt.close();
  conn.close();
  
}

Upvotes: 0

Дмитро Булах
Дмитро Булах

Reputation: 3847

you can accomplish this using JDBC Service

Upvotes: 3

Related Questions