Re: [Help-glpk] SQL query as input table - MathProg

[glpk xypron]

Hello Noli,

> what is wrong with this script.
> 
> set S2, dimen 2;
> 
> table tab IN "ODBC" 'Driver=SQLITE3;Database=Otago_p.sqlite;'
> 'SELECT tblCroptype.Croptype, tblData.Age tblData.Area'
> 'FROM tblCroptype JOIN tblData ON tblData.Croptype =
> tblCroptype.Croptype':
> S2 <- [tblCroptype.Croptype, tblData.Age], Area ~ tblData.Area
The operator '.' is only supported for outputing properties of
variables and constraints.

In many database systems column names may contain spaces and other
special characters, e.g. in MySQL I can write:
CREATE TABLE tblCroptype (
  `Crop Type` TEXT(40),
  PRIMARY KEY( `Crop Type`(40) )
  );

I suggest Andrew should change the coding to allow fieldnames to be
enclosed in apostrophes, e.g.
S2 <- ['tblCroptype.Crop Type', 'tblData.Age'], Area ~ 'tblData.Area'

As workaround you can use alias names for the columns, see below.

A bug for multiline SQL statements in GLPK 4.44 has been reported,
please apply the corresponding patch which has been posted to this
list and is included in WinGLPK 4.44-1.

Best regards

Xypron


# SQL statements to create tables
CREATE TABLE tblCroptype (
  Croptype TEXT(40),
  PRIMARY KEY( Croptype(40) )
  );

INSERT INTO tblCropType ( Croptype ) VALUES
  ( "Strawberries" );

CREATE TABLE tblData (
  Croptype TEXT(40),
  Age INT,
  Area FLOAT,
  PRIMARY KEY( Croptype(40), Age)
  );

INSERT INTO tblData ( Croptype, Age, Area ) VALUES
  ( "Strawberries", 2, 31.6 ),
  ( "Strawberries", 1, 27.4 ); 

# Model
set S2, dimen 2;
param Area{S2};
table tab IN 'ODBC' 'DSN=glpk;UID=glpk;PWD=gnu'
'SELECT tblCroptype.Croptype AS croptype, tblData.Age AS age, tblData.Area AS 
Area'
'FROM tblCroptype JOIN tblData'
'ON tblData.Croptype = tblCroptype.Croptype;' :
S2 <- [croptype, age], Area;
display Area;
end;

# Output
Display statement at line 9
Area[Strawberries,1] = 27.4
Area[Strawberries,2] = 31.6


-- 
GMX DSL SOMMER-SPECIAL: Surf & Phone Flat 16.000 für nur 19,99 Euro/mtl.!*
http://portal.gmx.net/de/go/dsl