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 binaries[]
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.