Re: [Help-glpk] ODBC Connection to MS-Access - WHERE Clause Syntaxproblems

[Alex Morelli]

Xypron,

Thanks very much.  My use of your instructions have so far have proved
partially successful.

I am using Windows XP Professional Version 2002 Service Pack 3.
Microsoft Access 2003 SP2 (Part of Microsoft Office Professional Edition
2003)
GLPK 4.45
GUSEK 0.2.12

I had spent a very long time trying to get the syntax proposed earlier
to work and was not successful (nor willing to say so again because I
thought that I was being a bit too much of a novice).  Eventually I
resorted to trying the WHERE clause using the underling MS Access Table
(using SELECT DISTINCT) rather that the MS Access query (i.e.
qProductsUnique) that I had at the beginning.  This was successful and I
felt then that the problem was that the syntax required was different
for the MS Access Table/Query being a Query rather than a Table.  As you
have kindly suggested, the syntax obviously needs to include the ";"
where you indicate.

I have also run a few tests and it seems to me that the absence of a ";"
for a Table does not cause any problem.  However the inclusion of a ";'
for a Table also works the same.  If you think that is also true I would
suggest that the recommendations to users is to include the ";" every
time.

Lastly, my actual objective was to exclude records that commenced with
"_".  This is not, as it turns out, the ideal character to commence
strings for use in SQL because it behaves as an 'any character'
wildcard.  One other difference to Access queries is the 'any character'
SQL wildcard is '%'.  All this stuff threw me - and took a while to
discover (if you haven't guessed - I'm not a programmer - engineer).

Using the syntax that you provided the following was partially
successful for my original requirement where I used:

set prods dimen 3;
param qpers{(i,j,k) in prods};

table products_table IN "ODBC" 
        'DSN=glpk_Sales' 
        'SELECT Product, MaterialDesc as pdesc, [Manuf Code] as prodman,
QPer' 
        'FROM qProductsUnique'
        'WHERE [Manuf Code] Like ''[_]%'';':
        prods <- [Product, pdesc, prodman], qpers~QPer;

Enclosing the "_" character in [] and continuing to use the ";" as
suggested.  The display statement returned the records that I expected.

I do now though have a problem that GLPK fails (glpsol.exe).  This seems
strange and I wonder if I am making a different mistake.  Using the
syntax suggested the display statement lists that records that I expect.
But after completing the task, glpsol fails.  The exit code is:

 >Exit code: -1073741819    Time: 5.234

I ran this with the model being only the following:

set prods dimen 3; #    p  : Products Sold that are manufactured
param qpers{(i,j,k) in prods};

table products_table IN "ODBC" 
        'DSN=glpk_Sales' 
        'SELECT Product, MaterialDesc as pdesc, [Manuf Code] as prodman,
QPer' 
        'FROM qProductsUnique'
        'WHERE [Manuf Code] Like ''[_]%'';':
        prods <- [Product, pdesc, prodman], qpers~QPer;

display prods;

end;

Yes there is a CR/LF after the end; statement.

My full model includes many other data selection statements (all from MS
Access).  I have run the full model with the data selection including
the use of the WHERE clause on a MS Access QUERY table and without it.
Every time the model includes the MS Access QUERY table, the program
fails.

So, for the moment I will continue with the MS Access TABLE table
selection process.

I hope that this makes some sense to you.


Regards, Alex 


-----Original Message-----
From: glpk xypron [mailto:address@hidden 
Sent: Sunday, 26 June 2011 4:46 PM
To: Alex Morelli; address@hidden
Subject: Re: [Help-glpk] ODBC Connection to MS-Access - WHERE Clause
Syntaxproblems

Hello Alex,

> 07002:1:-3010:[Microsoft][ODBC Microsoft Access Driver] Too few
> parameters. Expected 1.
The statement
SELECT Product, MaterialDesc as pdesc, [Manuf Code] as prodman,
QPer FROM qProductsUnique  WHERE qProductsUnique.[Manuf Code]="0012LBLK"
is legal in Access 2007 when executed via the "SQL-View" of a
query.

I was able to reproduce the problem for:
Windows 7 32bit
GLPK 4.45
Access 2007 (Microsoft Office Proffesional Plus)

Access expects strings to be enclosed in  apostrophes when called
via ODBC.

Please write the table statement like this:

table products_table IN "ODBC"
        'DSN=glpk_Sales'
        'SELECT Product, MaterialDesc as pdesc,'
        '[Manuf Code] as prodman, QPer'
        'FROM qProductsUnique '
        'WHERE [Manuf Code] = ''0012LBLK'';' 
        :
        psold <- [Product, pdesc, prodman], qper~QPer; 

I now have added a remark in the Wikibook:
http://en.wikibooks.org/wiki/GLPK/ODBC#Microsoft_Access

> I don't know what the D means after [0012LBLK].
The D should not be displayed.

Could you please, specify which version of Windows, GLPK,
and MS Access you are using.

Best regards

Xypron

-------- Original-Nachricht --------
> Datum: Sun, 26 Jun 2011 04:29:47 +1000
> Betreff: [Help-glpk] ODBC Connection to MS-Access - WHERE Clause
Syntax  problems

> Hello,
> 
> I am using the following:
> 
> set psold dimen 3;
> param qper{(i,j,k) in psold};
> 
> table products_table IN "ODBC"
>       'DSN=glpk_Sales'
>       'SELECT Product, MaterialDesc as pdesc, [Manuf Code] as prodman,
> QPer'
>       'FROM qProductsUnique '
>       'WHERE qProductsUnique.[Manuf Code]="0012LBLK"':
>       psold <- [Product, pdesc, prodman], qper~QPer;
> 
> display psold;
> ------------------------------------------
> qProductsUnique is a MS Access Query that returns unique records.  The
> data (0012LBLK) exists in the query.
> 
> I have tried many different syntax constructions for the WHERE clause
> and none work.  Removing the WHERE clause works fine, which suggests
(to
> me the novice) that all of the field names are correctly specified.
> 
> For the above WHERE clause syntax the following error is received:
> 
> =====
> The driver reported the following diagnostics whilst running
> SQLExecDirect
> 42000:1:-3100:[Microsoft][ODBC Microsoft Access Driver] Syntax error
> (missing operator) in query expression 'qProductsUnique.[Manuf
> Code]=[0012LBLK]D'.
> Yorke.mod:155: error on opening table products_table
> =====
> I don't know what the D means after [0012LBLK].
> I really want to restrict the records to the following
>       'WHERE qProductsUnique.[Manuf Code] Not Like "_*"':
> But that would not work also - and that is why I tried something that
I
> thought was simple.
> I am not a programmer and do not understand the ODBC / SQL syntax.  I
> have spent a long time researching these errors and found nothing that
I
> believe will help.
> I have placed a space at the end of the FROM clause - I would
appreciate
> if someone would also confirm if that is necessary.  When I remove the
> space at the end of the FROM clause I get a different error message --
> =====
> The driver reported the following diagnostics whilst running
> SQLExecDirect
> 07002:1:-3010:[Microsoft][ODBC Microsoft Access Driver] Too few
> parameters. Expected 1.
> Yorke.mod:155: error on opening table products_table
> =====
> I would appreciate any assistance.
> 
> Regards, Alex 

-- 
Empfehlen Sie GMX DSL Ihren Freunden und Bekannten und wir
belohnen Sie mit bis zu 50,- Euro! https://freundschaftswerbung.gmx.de