|
|
|
The ctsql.js module presents three methods for interacting with your FairCom DB SQL engine.
ctsql.connect
Create a FairCom DB SQL session with a connection string and credentials.
ctsql.connect("connection string", "user ID", "password");
Example:
ctsql.connect("6597@localhost:ctreeSQL", "ADMIN", "ADMIN");
A proper connect string consists of port@hostname:databasename.
Note: Autocommit is enabled as default with this module. Database update transactions are automatically committed as they occur.
ctsql.query
Submit a FairCom DB SQL statement string.
ctsql.query("SELECT * FROM customer");
When successful, ctsql.query returns a SQL resultset as a JSON structure of values. This return has the following properties:
{
columnNames : an array of column names
rows : an array of returned data tuples
}
ctsql.query supports both immediate execution as well as parameterized queries. Use a "?" to indicate where a parameter should be passed in your query string. Parameters are passed as an array of comma-delimited values after the query string.
Example:
ctsql.query("SELECT * FROM customer WHERE customerNumber BETWEEN ? AND ?", [1001, 1010]);
For DDL statements, a JSON success structure is returned:
{
success: [ true | false ]
}
Example:
console.log ("Create table");
var result = ctsql.query("create table people_test (name CHAR(10), age INTEGER)");
console.log(result);
console.log ("Drop table");
result = ctsql.query("drop table people_test");
console.log(result);
============
Create table
{ success: true }
Drop table
{ success: true }
ctsql.end
Disconnect your FairCom DB SQL Session
ctsql.end();
Exceptions:
Three node.ctsql specific exceptions can be thrown:
These exception classes derive from a single base class and have the following properties:
{
name - name of exception class
data - JSON structure containing an error code and associated message
{
error - SQL numeric error
message - SQL error message description
}
stack - current stack trace of exception location
}
Example:
try
{
ctsql.connect("connection string", "user ID", "password");
}
catch (e)
{
console.log(e.name);
console.log("Caught exception: " + e.data.error + " " + e.data.message);
if (e.stack)
{
console.log('\nStacktrace:')
console.log('====================')
console.log(e.stack);
}
if (e instanceof ctsql.ctsqlConnectError)
//.. handle connection error
else {
//throw exception to outer caller
throw e;
}
}
Example of working code:
Now you are ready to write working code. Here is an easy example to get started:
const ctsql = require('ctsql');
ctsql.connect("6597@localhost:ctreeSQL", "admin", "ADMIN");
try
{
var result = ctsql.query("select * FROM customer");
console.log(result.columnNames);
result.rows.forEach( function (value) {
console.log([value]);
});
}
catch (e)
{
console.log("oops.. I caught an exception " + e.toString() );
}
Example with named columns:
const ctsql = require('./ctsql');
ctsql.connect("6597@localhost:ctreeSQL", "admin", "ADMIN");
var result;
try
{
result = ctsql.query("SELECT customerNumber AS id,
customerName AS name
FROM customer
ORDER BY customerName");
}
catch (e)
{
console.log("oops.. I caught an exception: " + e.toString());
}
console.log(result.columnNames);
result.rows.forEach( function (value) {
console.log(value.id);
console.log(value.name);
});
Example DDL usage:
const ctsql = require('ctsql');
ctsql.connect("6597@localhost:ctreeSQL", "admin", "ADMIN");
try
{
var result = ctsql.query("create table customer (
customerNumber char(4),
customerZipcode char(5),
customerState char(2),
customerRating integer,
customerDate date,
customerAddress, char(47)
customerName char(47)
);
}
catch (e)
{
console.log("oops.. I caught an exception: " + e.data.message() );
}
console.log(result.success);
Example parameterized query:
const ctsql = require('ctsql');
ctsql.connect("6597@localhost:ctreeSQL", "admin", "ADMIN");
var result = ctsql.query("select * from admin.syscolumns where id > ?", [5]);
console.log(result.columnNames);
for( row of result.rows)
console.log(row);
ctsql.end();
