eWeek - June 7, 2010

labs@work

(I’m talking JavaScript here, not Java, which is very different.) But the base functionality is there through classes supporting connections, statements, prepared statements and result sets.

The API documentation is a bit weak: It shows the function prototypes and some quick descriptions, but not any examples.

For instance, I wanted to see some actual SQL in the JdbcStatement class, but there was none. I imagine there will be more documentation in the future, as well as more examples from the community.

The scripting system
is basically a JavaScript
wrapper around a set of
Java classes on the server.
How do I know that?
Because during testing, I
often received errors that
were obviously coming
from Java. One time, I
tried to pass a JavaScript
array into a function
and received the error
message “Cannot convert
NativeArray@78037b60
to java.lang.Object[][].”
Further, the JavaScript
classes that you interact
with seem to have a lot
of methods that aren’t
documented in the
API—which is normal when you
consider that JavaScript doesn’t have
much of a mechanism for public and
private members. There are ways to
hide members, thereby making them
private, but the spec itself doesn’t
support access levels.

However, this means you have a
whole slew of functions you’re free to
call—although you should be careful
if they’re not documented because
Google could easily change them at
any time. Again, how do I know this?
Because the script editor is object-
aware and will show a list of members
available to an object when you type
the object name followed by a dot.

Of course, JavaScript isn’t strongly
typed: You just declare variables with
var, and they can hold any type. This
means the code editor is doing some
sophisticated work to follow the chain
of calls. For example, if you type
var ss = SpreadsheetApp.
getActiveSpreadsheet();

the editor knows that the getActiveSpreadsheet method returns a Spreadsheet object—or, more accurately for JavaScript purists, an object that contains the members that define it as a Spreadsheet object.

The connection code

The code execution was a bit slow: It took about 4 seconds to connect, and then you can visibly see a row fill, then a very tiny pause, then the next row fill. Compare this to connecting remotely via the mysql command-line interface (from my local computer to the same remote mysql server), which connected instantly and retrieved the rows a bit more quickly.

It’s hard to say what the holdup is on Google’s end, but I would suggest doing some tests to see if the access is fast enough for your uses. If not, you’ll want to do some streamlining: for example, use stored procedures and views on the database end.

According to the official blog, “It’s now possible to call a script from any Website, so you’re able to build Web pages where users can submit entries that will be collected in a Google spreadsheet.” Unfortunately, that’s about all the information we have at this point. So far, the docs say nothing about this feature, but it sounds intriguing.

I’m speculating that there will be a JavaScript library that you’ll include on your

Website to gain access to the scripts, much the same way you can include Facebook scripts on your own site. We’ll have to wait and see on this one. The privacy and security issues should be interesting as well. ´

Jeff Cogswell can be contacted at jeff. cogswell@ziffdavisenterprise.com.

OPENING THE JDBC CONNECTION

function testJDBC() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var connstr = ‘jdbc:mysql:// www.example.com:3306/test’; // subst
your own IP/database name.
var conn = Jdbc.getConnection(connstr, ‘username’, ‘password’);
var stmt = conn.createStatement();
stmt.execute(‘select from names’);
var res = stmt.getResultSet();
var line = 2;
var stuff;
while ( res.next()) {
var fname = res.getString(‘fname’);
var lname = res.getString(‘lname’);
sheet.getRange(‘A’ + String(line) +’:B’ + String(line) ).setValues([
[fname,lname]]);

line++;

} conn.close();

} Because the documentation was a bit weak, I had to figure out how to get a JDBC connection going. But once I got it all figured out, it worked very well. For the programmers reading this, here’s the short code that opens the JDBC connection, executes some SQL and copies the resulting data into the active spreadsheet: