How to Use Direct SQL Queries

Not all operations on ODBC databases can be done using the read from table and write to table statements. More flexibility is offered using the intrinsic procedure DirectSQL. Please use the Reindeer Pairing example to experiment with this feature.

With that, the procedure below illustrates an alternative for write to table ... in insert mode:

Procedure pr_fillDatabase

This procedure will first create a connection string to the SQLite database, clean its previous values and then insert the current solve.

 1sp_loc_datasource
 2:=  SQLCreateConnectionString (
 3        DatabaseInterface              :  'odbc',
 4        DriverName                     :  "SQLite3 ODBC Driver", !Your local drive
 5        ServerName                     :  "",
 6        DatabaseName                   :  "pairing.db", !The path of your database
 7        UserId                         :  "",
 8        Password                       :  "",
 9        AdditionalConnectionParameters :  "");
10
11!delete all
12DirectSQL(sp_loc_datasource, "delete from possible_pairs;");
13
14!and fill again
15for (i_sols, i_left) | ep_variousRightPartners(i_sols, i_left) do
16    sp_loc_insertCommand
17    :=  FormatString("INSERT INTO possible_pairs (solution,left,right) values ('%e','%e','%e');",
18                    i_sols, i_left, ep_variousRightPartners(i_sols, i_left));
19
20    DirectSQL(sp_loc_datasource, sp_loc_insertCommand);
21endfor ;

Remarks:

  • line 12: Call to DirectSQL using an existing ODBC connection to delete previous information on the database.

  • line 18: To pass the elements (ep_variousRightPartners), just fill in the data using FormatString().

  • line 20: A SQL INSERT statement following the SQL syntax . possible_pairs is the name of the table of the Reindeer Pairing.

See also

  • Do not know which driver is available? Check this article.