To connect to a database, just use revOpenDatabase function, like this:

 put revOpenDatabase("ODBC","DRIVER=SQL Server;SERVER=server_name;DATABASE=database_name;UID=username;PWD=password;Trusted_Connection=No",,,) into connID

It's recommended to store the connection ID (it's just an integer) in a custom property.

Then you can retrieve data with the revDataFromQuery function:

put "SELECT * from users ; " into tSQL
put revDataFromQuery(tab,return,connID,tSQL) into tRecords

notice that specifing TAB and return as delimeter, you'll obtain a TABBED grid (text) that you may use directly in table fields.

You can use any SQL command with the revExecuteSQL message:

put "INSERT into users (name,surname) VALUES ('Jack','Sparrow')" into tSQL
revExecuteSQL connID,tSQL

TIPS: the filter command removes empty lines, it can be handy when user may add too many empty lines in a table:

 filter mytext without empty

Remember to close connection with:

revCloseDatabase connID

Working with binariesEdit

In order to upload binaries, you need to use the variable in the revExecuteSQL.

The SQLStatement may contain one or more placeholders, which are sequential numbers prepended by a colon. The revExecuteSQL command substitutes the corresponding item in the variablesList for each of these placeholders. For example, if you have two variables called "valueX" and "valueY", you can use a SQLStatement that includes placeholders as follows:

revExecuteSQL myID, "insert into mytable values(:1,:2)", "valueX","valueY"

when using binaries, you have to insert the *b prefix in variable name; so if you variable containing a binary is "valueX", the correct code is:

revExecuteSQL myID, "insert into mytable values(:1)", "*bvalueX"

Since the revExecuteSQL command strips the binary marker "*b" and passes it to the database as binary data, rather than text data.

Community content is available under CC-BY-SA unless otherwise noted.