Scripts - Database commands

Using the MedCalc database commands you can connect to different SQL databases.

• SQL Server (Microsoft)

  SQL Server is a proprietary relational database management system (RDBMS) developed by Microsoft.

  learn.microsoft.com

• MySQL (Oracle)

  MySQL is an open-source relational database management system. It is most commonly used on webservers.

  www.mysql.com

• SQLite (SQLite Consortium)

  SQLite doesn't use a client-server architecture but stores the tables in a local file on the PC.

  www.sqlite.org

Connecting to the databases

There are different functions to connect to the 3 databases. These functions return a handle (a number that identifies the connection) that is used in subsequent database functions and queries.

• SQL Server

  handle=OpenSql(connectionstring);.

  Example:

  handle=OpenSql("Driver={SQL Server};Server=.\SQLEXPRESS;Database=laboratory;Trusted_Connection=True;");

  The know the connectionstring for your database, you will probably have to consult your institution's IT department.

• MySQL

  handle=OpenMySql(server,user,pasword,database);.

  Example:

  handle=OpenMySql("servername:3306","UserID","MyPassword","laboratory");

  The know the different settings for your database, you will probably also have to consult your institution's IT department.

• SQLite

  handle=CreateSQLite(filename); creates a local database with the given filename.

  handle=OpenSQLite(filename); opens an existing local database with the given filename.

Executing a database query

The function table=SQLQuery(handle,query); executes the query on the database identified with its handle, and stores its possible output in a table.

The query can contain placeholders (questions marks) which will be replaced by subsequent arguments of the SQLQuery function.

In the following example, MedCalc will replace the 3 question marks with the values 2, "Jones", and 'Margeret', when the query is executed.

SQLQuery(handle,"INSERT INTO Patients (PersonID,LastName,FirstName) VALUES (?,?,?);",2,"Jones",'Margaret');

This allows to easily use variables in the SQLQuery function.

In addition, MedCalc will 'escape' apostrophes in the arguments, so the query can be executed correctly, as illustrated in the following example.

id=2;
name="O'Neill";
firstname="Margaret";
SQLQuery(handle,"INSERT INTO Patients (PersonID,LastName,FirstName) VALUES (?,?,?);",id,name,firstname);

The same SQLQuery function is used for the 3 different databases.

Import a database table into the MedCalc spreadsheet

The function table=SQLImport(handle,query); executes the query on the database identified with its handle, and imports its possible output into the MedCalc spreadsheet. Its use is similar to SQLQuery.

Examples

SQLImport(handle,"SELECT * FROM Patients;");
SQLImport(handle,"SELECT * FROM Patients WHERE Age>?;",20);

The first example will import the complete Patients table. The second example only the cases with age >20.

Closing the database connection

One single common command is used to close the connection to a database: SQLClose(handle);.

Error messages

The function str=SQLError(); returns details on the latest database command.

Examples

SQL Server

handle=OpenSql("Driver={SQL Server};Server=.\SQLEXPRESS;Database=laboratory;Trusted_Connection=True;");
t=SQLQuery(handle,"SELECT COLUMN_NAME FROM information_schema.columns WHERE table_name = 'Persons'");
?t;
t=SQLQuery(handle,"SELECT * FROM Patients;");
?t;
SQLClose(handle);

SQLite

handle=CreateSQLite("E:\laboratory.db");
t=SQLQuery(handle,"CREATE TABLE Patients (PersonID int,LastName varchar(255),FirstName varchar(255));");
?SQLError();
SQLQuery(handle,"INSERT INTO Patients VALUES (1,'Smith','John');");
SQLQuery(handle,"INSERT INTO Patients (PersonID,LastName,FirstName) VALUES (2,'Jones','Margaret');");
t=SQLQuery(handle,"SELECT * FROM Patients;");
?t;
SQLClose(handle);

results in :

PersonID
LastName
FirstName
1
Smith
John
2
Jones
Margaret

See also

• Scripts
• Script commands