[Help-glpk] Re: Another problem when connecting to Access

[Xypron]

Hello Aly,


> In another question, is there a way in glpk that I can write general sql 
> statements (not when reading or writing tables)?. For instance, if I want to 
> create a copy of a table using the statement:
>
> SELECT * INTO Table_new
> FROM Table_old;
>
> or for instance if I want to delete all variable values before re-solving the 
> instance!
>
>
> How and where can I insert such sql statements in the model code?
>
>
> Thanks a lot man for all your help and support,
>
> Aly
>
>
>    

The current SQL support in the GMPL language only allows to issue SQL 
commands in "table IN" and "table OUT" statements.

In a "table IN" you can precede the (final) SELECT statement (or table 
name) by any number of preparatory SQL statements.

In a "table OUT" you can precede the last statement 
(INSERT/DELETE/UPDATE) which is iterated over the domain by any number 
of preparatory SQL statements.

glpk-4.44/examples/sql/sudoku_odbc.sql contains the following statement:

table ta {(i, j) in {i1 in 1..9} cross {i2 in 1..9}} OUT
  'iODBC' 'DSN=glpk;UID=glpk;PWD=gnu'
  'DELETE FROM sudoku_solution'
    'WHERE ID = ' & id & ';'
  'INSERT INTO sudoku_solution'
    '(ID, COL, LIN, VAL)'
    'VALUES(?, ?, ?, ?);' :
  id ~ ID, i ~ COL, j ~ LIN, (sum{k in 1..9} x[i,j,k] * k) ~ VAL;

The DELETE SQL statement assures that the following INSERT does not 
create duplicate records.

The same could have been done in the "table IN" statement instead:

table ti IN 'iODBC'
  'DSN=glpk;UID=glpk;PWD=gnu'
  'DELETE FROM sudoku_solution'
    'WHERE ID = ' & id & ';'
  'SELECT * FROM sudoku'
    'WHERE ID = ' & id & ';' :
  fields <- [COL, LIN], givens ~ VAL;

The preparatory SQL statements must end with a semicolon (';') as the 
last character of a string. SQL statements  may have be split into 
multiple strings, because GLPK has a maximum string length of 100 
characters.

If you want to completely separate the preparatory statement, you can 
use some dummy output statement like:

table prep {i in 1..1} OUT 'ODBC'
  'DSN=glpk;UID=glpk;PWD=gnu'
  # The preparatory SQL statement(s) follow
  'DELETE FROM sudoku_solution'
    'WHERE ID = ' & id & ';'
  # The next SQL statement is needed to satisfy the GMPL syntax.
  'SELECT ?;' : i;

Best regards

Xypron