JavaScriptSQL (JSSQL)

JSSQL in action

In this section we can see how JSSQL works through a more interactive web application and lern about the functionalitys of the SQL interpreter.

 

A test console

To test SQL queries on the JSSQL engine I developed this little web application: JSSQL console

In this console you can select a JSBD database (these databases were generated with the JSBD_Conversor class). We can also see in the database the tables, fields and data types, enter SQL statements, execute it, view results and some other statistics and debug parameters.

I hope that the use of the console will be intuitive, and I will not repair in explain how to use it , the important thing here is seen as the SQL syntax that uses the JSSQL engine and its basic functionality.


Attention: the JSSQL console is just a test application, JSSQL can be integrated into any Web application and is a tool for developers to access to a set of data using SQL and JavaScript.



Some test queries

Within the console select the database neptuno (I hope that is familiar to you), you can see tables, fields and data types of the database.

Worth mentioning that JSSQL is running in your browser, offline, there is no database server or interpreter in the server side, just the HTML page and JavaScript code running on your machine.

Here are some queries that let you see some characteristics ofJSSQL:

(To run the sentences simply copy and paste them into the console and press Run or push the 'Test' button below the query)


Use of the WHERE with JavaScript functions: 

 Select NombreProducto, CantidadPorUnidad, UnidadesEnExistencia
 from productos
 where IdCategoria==2 && NombreProducto.match(/salsa/i)!=null

Note that operators that use JSSQL are the Javascript operators (==, <=,> =, <,>,! =, &&, | |) And also can use any JavaScript function ( match , length , etc..), you must keep in mind that the result of the WHERE must be true or false


Using aliases in the SELECT and the FROM : 

 Select p.NombreProducto as NOM, p.CantidadPorUnidad as CANT, p.UnidadesEnExistencia as STOCK
 from productos p
 where p.IdCategoria==2 && NombreProducto.match(/salsa/i)!=null

We can give an alias to a field or a table.


Joining tables: 

select prov.NombreCompania as Proveedor,  prod.NombreProducto as Producto,
    cat.NombreCategoria as Tipo
 from productos prod, categorias cat, proveedores prov
 where 
    prod.IdCategoria == cat.IdCategoria &&
    prod.IdProveedor == prov.IdProveedor

To join the tables put the fields that connect the tables in the WHERE clause (for example: prod.IdCategoria == cat.IdCategoria), this makes a filter on the Cartesian product.


Sorting the results: 

select NombreCompania, NombreContacto, CargoContacto
   from clientes order by 3, 2 desc

In this case the result is sorting by CargoContacto (asccending) and NombreContacto in descending order.

You can only reference fields in the ORDER BY with the numbers of the fields listed in the SELECT clause.

To see more examples see the Documentation section

Using the engine JSSQL in three easy steps:

First, we include the engine JSSQL 

    <SCRIPT LANGUAGE="JavaScript1.2" type="text/javascript" SRC="JSSQL.js"></SCRIPT>

We load the database: 

 //Creation of the Engine JSSQL
 var myJSSQL= new JSSQL();
 //Opening of the  'neptuno' database
 if (!myJSSQL.Open('neptuno'))
 {
    //if it fails displays an error message
    alert(myJSSQL.GetError());
 }

We execute the query and print the result 

 //SQL query
 var sql="select NombreCompania as comp, NombreContacto as cont,
 CargoContacto as cargo from clientes order by 3, 2 desc" ;
 //query execute
 var res=myJSSQL.Query(sql)

 if(res===false)
 {//if it fails displays an error message
    alert( myJSSQL.GetError() );
 }
 else
 {
    //result printing
    document.write("<table border='1'>");
    document.write("<tr><th>COMPANIA</th>")
    document.write("<th>CONTACTO</th><th>CARGO</th></tr>");
    for (i=0;i<res.length;i++){
        document.write(" <tr><td>");
        document.write(res[i]["comp"]);
        document.write(" </td><td>");
        document.write(res[i]["cont"]);
        document.write(" </td><td>");
        document.write(res[i]["cargo"]);
        document.write("</td></tr>");
     }
    document.write("</table>");
 }

Here is the result: See it