XS JavaScript application programming

The SQL Anywhere XS JavaScript driver can be used to connect to SQL Anywhere databases, issue SQL queries, and obtain result sets.

The SQL Anywhere XS JavaScript driver allows users to interact with the database from the JavaScript environment. The SQL Anywhere XS API is based on the SAP HANA XS JavaScript Database API provided by the SAP HANA XS engine. Drivers are available for various versions of Node.js.

The XS JavaScript driver also supports server-side applications written in JavaScript using SQL Anywhere external environment support.

Note

In addition to the XS JavaScript driver, a lightweight, minimally-featured Node.js driver is available that can handle small result sets quite well. Node.js application programming using this driver is described elsewhere in the documentation. The lightweight driver is perfect for simple web applications that need a quick and easy connection to the database server to retrieve small result sets. However, if your JavaScript application needs to handle large results, have greater control, or have access to a fuller application programming interface, then you should use the XS JavaScript driver.

Node.js must be installed on your computer and the folder containing the Node.js executable should be included in your PATH. Node.js software is available at nodejs.org Information published on non-SAP site .

In order for Node.js to locate the driver, make sure that the NODE_PATH environment variable includes the location of the XS JavaScript driver. The following is an example for Microsoft Windows:

SET NODE_PATH=%SQLANY17%\Node

Note On Mac OS X 10.11, set the SQLANY_API_DLL environment variable to the full path to libdbcapi_r.dylib.

The following illustrates a simple Node.js application that uses the XS JavaScript driver.

var sqla = require( 'sqlanywhere-xs' );
var cstr = { Server     : 'demo',
             UserID     : 'DBA',
             Password   : 'sql'
            };
var conn = sqla.createConnection( cstr );
conn.connect();
console.log( 'Connected' );

stmt = conn.prepareStatement( "SELECT * FROM Customers" );
stmt.execute();
var result = stmt.getResultSet();
while ( result.next() )
{
    console.log( result.getString(1) +
            " " + result.getString(3) +
            " " + result.getString(2) );
}

conn.disconnect();
console.log( 'Disconnected' );

This program connects to the sample database, executes a SQL SELECT statement, and displays only the first three columns of the result set, namely the ID, GivenName, and Surname of each customer. It then disconnects from the database.

Suppose this JavaScript code was stored in the file xs-sample.js. To run this program, open a command prompt and execute the following statement. Make sure that the NODE_PATH environment variable is set appropriately.

node xs-sample.js

The following JavaScript example illustrates the use of prepared statements and batches. It creates a database table and populates it with the first 100 positive integers.

var sqla = require( 'sqlanywhere-xs' );
var cstr = { Server     : 'demo',
             UserID     : 'DBA',
             Password   : 'sql'
            };
var conn = sqla.createConnection( cstr );
conn.connect();

conn.prepareStatement( "CREATE OR REPLACE TABLE mytable( col1 INT)" ).execute();

var stmt = conn.prepareStatement( "INSERT INTO mytable VALUES(?)" );
var BATCHSIZE = 100;
stmt.setBatchSize(BATCHSIZE);
for ( var i = 1; i <= BATCHSIZE; i++ )
{
    stmt.setInteger( 1, i );
    stmt.addBatch();
}
stmt.executeBatch();
stmt.close();
conn.commit();
conn.disconnect();

The following JavaScript example illustrates the use of prepared statements and exception handling. The getcustomer function returns a hash corresponding to the table row for the specified customer, or an error message.

function getcustomer( conn, customer_id )
{
    try 
    { 
        var query = 'SELECT * FROM Customers WHERE ID=?';
    
        var pstmt = conn.prepareStatement(query);
        pstmt.setInteger( 1, customer_id );
        var rs = pstmt.executeQuery();
        if ( rs.next() ) 
        {
            return(
            { 
                id          : rs.getInteger(1), 
                surname     : rs.getString(2), 
                givenname   : rs.getString(3), 
                street      : rs.getString(4), 
                city        : rs.getString(5), 
                state       : rs.getString(6), 
                country     : rs.getString(7), 
                postalcode  : rs.getString(8), 
                phone       : rs.getString(9),
                companyname : rs.getString(10)
            } );
        }
        else
        {
            return 'No customer with ID=' + customer_id;
        }
    }
    catch (ex) 
    { 
        return ex.message;
    }
    finally
    {
        if( pstmt ) 
        {
            pstmt.close();
        }
    }
}

var sqla = require( 'sqlanywhere-xs' );
var cstr = { Server     : 'demo',
             UserID     : 'DBA',
             Password   : 'sql'
            };
var conn = sqla.createConnection( cstr );
conn.connect();
for ( var i = 200; i < 225; i++ )
{
    console.log( getcustomer( conn, i ) );
}
conn.close();
conn.disconnect();

Note

Looking for API reference documentation? If you installed the documentation locally, use the Windows Start menu to access it (Microsoft Windows), or navigate to it under C:\Program Files\SQL Anywhere 17\Documentation.

You can also access SAP SQL Anywhere API reference documentation on the web at DocCommentXchange: http://dcx.sap.com Information published on SAP site