Pages

Tuesday, 19 November 2019

Create an SQL Table in Apps Script

The following code combines Google Apps Script and SQL to create a Table in an existing database.

db.gs
To begin we need to establish a remote connection to the database via the 'JDBC Service' to which we pass a number of credentials.
var db = Jdbc.getConnection('jdbc:mysql://' + dbAddress + ':3306/' + dbName, dbUser, dbPassword);

createStudentsTable.gs
With the connection made we next need to create a statement for sending SQL code to the database.
var stmt = db.createStatement();
Then we can 'execute' the given SQL statement which creates a Table called students containing 4 columns and their datatypes.
var studentsTable = stmt.execute('CREATE TABLE students'
                                   + ' (StudentNo int, FirstName varchar(255), LastName varchar(255), Shoesize varchar(255));'
                                  );
To finish cleanly it is good practice to then 'close' any open connections we have to the database.
stmt.close();
db.close();

Create an SQL Table in Apps Script

No comments:

Post a comment