RE: [Help-glpk] RE: Error when using "table"

[Meketon, Marc]

Once again, thanks.

I just tried the below - a slight modification of your suggestion - and it 
works well to delete all the records from the table.

table result2 {x in 1..1} OUT 'ODBC'
  'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=' & DB_LOCATION & ';'
  'DELETE FROM fc_ForecastEmpties WHERE ? = ?' :
  x,x;

I added a wiki entry for this.  When you get a chance, could you look at it for 
correctness?
http://en.wikibooks.org/w/index.php?title=GLPK/ODBC&stable=0#Executing_a_series_of_SQL_statements_that_do_not_write_records


-----Original Message-----
From: glpk xypron [mailto:address@hidden
Sent: Tuesday, February 15, 2011 3:11 PM
To: Meketon, Marc; address@hidden
Subject: Re: [Help-glpk] RE: Error when using "table"

Hello Marc,

> Question 2:  Just to play it safe, I tried to create a fourth "table"
> statement with the DELETE FROM sql in it, and run that first at the beginning.
> Except that it does not work.  Specifically I tried (after finding
> something that syntactically works):
>
>     table result2 {x in EMPTY_TYPES : x=""} # this is the null set, no
> string in EMPTY_TYPES is blank
>         OUT 'ODBC'
>       'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=' &
> DB_LOCATION & ';'
>       'DELETE FROM fc_ForecastEmpties;' : 1~ID; But it did not delete
> the records, and the GLPSOL output says:
>
>     Writing result2...
>     Connected to ACCESS 04.00.0000 - C:\Forecast.mdb
>     INSERT INTO DELETE FROM tbl_result; ( ID ) VALUES ( ? )

glpk-4.45\doc\gmpl.pdf teaches:
"For OUT-table the last SQL statement can contain one or multiple ques- tion 
marks. If it contains a question mark it is considered a template for the write 
routine. Otherwise the string is considered a table name and an INSERT template 
is automatically generated."

As you used an empty set in the domain the syntactically wrong INSERT was never 
executed. That is why Access did not return an error.

A reasonable use of DELETE might be:

table result2 {x in {1,2,3}} OUT 'ODBC'
  'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=' & DB_LOCATION & ';'
  'DELETE FROM fc_ForecastEmpties WHERE ID = ?' :
  x;

Best regards

Xypron

--
GMX DSL Doppel-Flat ab 19,99 Euro/mtl.! Jetzt mit gratis Handy-Flat! 
http://portal.gmx.net/de/go/dsl
----------------------------------------------------------------------------
This e-mail and any attachments may be confidential or legally privileged.  If 
you received this message in error or are not the intended recipient, you 
should destroy the e-mail message and any attachments or copies, and you are 
prohibited from retaining, distributing, disclosing or using any information 
contained herein.  Please inform us of the erroneous delivery by return e-mail.

Thank you for your cooperation.
----------------------------------------------------------------------------


This e-mail and any attachments may be confidential or legally privileged. If 
you received this message in error or are not the intended recipient, you 
should destroy the e-mail message and any attachments or copies, and you are 
prohibited from retaining, distributing, disclosing or using any information 
contained herein.  Please inform us of the erroneous delivery by return e-mail. 
Thank you for your cooperation.