SQL

SQL

SQL class contains basic methods for communicating with SQL databases configured in the config. This class can make SELECT, UPDATE, INSERT and custom query's. Configuration is needed to make a DB connection.

Configuration

To enable acces to an SQL database, the connection must be defined in a file in the root of the project named 'connections.json'. Configuration structure:

{"Data": {"type": "sql", "connectionString": "Server=example.com;Database=ExampleName;UserId=sa;Password=test"}}

Index

functions:

• execute(connectionName,query)

examples:

• Query's

Functions

execute(connectionName,query)

SQL.execute takes a connectionName and a query, executes the query and returns the result if the query is a SELECT statement.

Variable	Description
connectionName	Name of a configured connection
query	The query to be executed

Returns: the result if the query is a SELECT statement.

Exceptions:

• InternalError: Thrown when no application can be found in application scope.

• Error: Thrown when an error has been found while executing the query.

Example:

var id = SQL.execute("NETDB", "SELECT * FROM users;");

Examples

Query's

This example expects to have the following connection in the configuration:

{"Data": {"type": "sql", "connectionString": "Server=example.com;Database=ExampleName;UserId=sa;Password=test"}}

We can INSERT a new user and UPDATE his values:

var db = "ExampleName";
var user = {name: "Hello World!", mail: "HelloWorld@example.com"};
var id = SQL.insert(db, "users", user);
user.name = "NewExample"
// updating our db user based on the id of insert
SQL.set(db, "users", id, user);

Now let's check if everything went fine:

console.log(SQL.get(db, "users")); //Prints all users

And to set the db back, we delete the row with a custom query:

var query = "DELETE FROM users WHERE id = " + id.toString() + ";";
SQL.execute(db, query);