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:
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.