Database Fundamentals

db conn query{}: Reading from a database

Contents

To read from a database you should use conn:query{}.

To write to a database you should use conn:execute{}.

How It Works

To read from a database, use a conn:query{} method call.

This allows you to execute an ad hoc SQL SELECT statement on a database (a count of the rows returned is shown for information):

As you can see, the format of the data returned is a standard node tree, which means that it’s very easy to manipulate and map data coming back from the query.

conn:query{} actually executes SELECT statements when you alter the Editor script or change the sample data. It assumes that the SELECT statements do not alter the state of the database and can be safely run to power the Translator’s annotation and auto-completion capabilities. For anything which does alter the database, use conn:execute{} or conn:merge{}.

The next screen shot shows what to expect when you have multiple rows in the database, in this case 208:
If you click the Result Set icon, you will see something like this:
As you can see it is all very easy to deal with.

Return Values

Both conn:execute{} and conn:query{} return a pair of values:

  1. The last recordset selected by the SQL statement (or nil if the statement resulted in no recordsets)
  2. The array of all recordsets resulting from the SQL statement (if no recordsets were produced, the array will be empty)

This is covered in detail in the Return Values section of the the next page db conn execute{} – Writing to a Database.

Error Handling

Both conn:execute{} and conn:query{} throw errors if a database error is encountered. The errors are regular Lua errors and will cause the execution of the script to halt, unless they are caught by exercising Lua error handling mechanisms.

This is covered in detail in the Error Handling section of the the next page “db conn execute{}: Writing to a Database”.

Sample Code

local conn = db.connect{
   api=db.SQL_SERVER, 
   name='Test', 
   user='root', 
   password='password', 
   live = true
}

function main() 
   findPatient(4525285)
   allPatients()
end

function findPatient(Id)
   local R = conn:query{
      sql="SELECT * FROM Patient WHERE id = '"..Id.."'"
   }
   trace(#R)   -- count of Patient rows returned
   trace(R[1]) -- first row returned

end

function allPatients()
   local R = conn:query{sql="SELECT * FROM Patient"}
   trace(#R)   -- count of Patient rows returned
   trace(R[1]) -- first row returned

end

What’s Next?

We have shown you how to read data from a database. The next step is to add code to map the data into another format, such as HL7, XML or JSON.

See the following links for more information about working with databases:

Please contact support at support@interfaceware.com if you need more help.

Continue: db conn execute{}: Writing to a Database