0

In google app script, I use getstring() to retrieve the data from mysql to write the result into array, like the example by google help file. However, my table has 100,000 rows and 20 columns. It takes more than 10 mins to convert to an array. In the JDBC class, there is getarray(). I cannot find any example of how to use getarray to convert the JDBCResultSet To array. Here is the help in google https://developers.google.com/apps-script/reference/jdbc/jdbc-array#getArray() and https://developers.google.com/apps-script/guides/jdbc#read_from_the_database Can anyone help? Does the getarray() convert faster than getstring()? I have already found this post. But there is no answer yet. How to getArray from JdbcResultSet in google app script?

here is my code:

function temp1() {
  var conn = Jdbc.getConnection('jdbc:mysql://' + connectionName + '/' + db, user, userPwd)
  var stmt= conn.createStatement();
  var results = stmt.executeQuery('select * from abctable')
  myarray = []
  var myarray = results.getArray(1)
  Logger.log(myarray)
}

If I use google recommended method, and I just get one column with 100,000 rows, the following code takes about 4 minutes to convert the JdbcResultSet to an array to reach to the Logger.log row:

function temp2() {
  var conn = Jdbc.getConnection('jdbc:mysql://' + connectionName + '/' + db, user, userPwd)
  var stmt= conn.createStatement();
  var results = stmt.executeQuery('select first-column from abctable')
  myarray = []
  while (results.next()) {
     myarray.push(["'" + results.getString(1)])
  }
  Logger.log(myarray)
}

Help help please.

allexpiretoday
  • 21
  • 2
  • Please supply and example of your current code – Cooper Feb 17 '21 at 17:03
  • JDBC's `getArray` is for reading an array column, it is not _" to convert the JDBCResultSet To array"_, so it cannot do what you want it to do, unless you can convert your query to return a single two-dimensional array value. – Mark Rotteveel Feb 17 '21 at 17:07
  • Does single two-dimension means 100000 x 20 array? But the result from JDBC is a JDBCResultSet, how to convert the query to a array? – allexpiretoday Feb 17 '21 at 18:20
  • It is very rare for any MySQL user to ask for so many rows all at once. Please describe what your code will do with the large array; maybe we can suggest an alternative. – Rick James Feb 18 '21 at 02:29
  • My code will download a 100,000 x 20 table from mysql database and show it in google spreadsheet for further processing. – allexpiretoday Feb 18 '21 at 08:32
  • If you just want to "export" the data to a spreadsheet, maybe this question about how to get a csv file from mysql will help - https://stackoverflow.com/questions/356578/ - then you can just open the csv file in Sheets. Converting so much into an array or even a string will always take a long time. – iansedano Feb 18 '21 at 09:44

0 Answers0