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

[glpk xypron]

Hello Alex,


 >Exit code: -1073741819    Time: 5.234
This should not occur.

Are you able to provide to me (not to the list) an example, that
reproduces the error?

Best regards

Xypron

-------- Original-Nachricht --------
> Datum: Mon, 27 Jun 2011 03:34:03 +1000
> Von: "Alex Morelli" <address@hidden>
> An: "glpk xypron" <address@hidden>, address@hidden
> Betreff: RE: [Help-glpk] ODBC Connection to MS-Access - WHERE Clause 
> Syntaxproblems

> 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

-- 
NEU: FreePhone - kostenlos mobil telefonieren!                  
Jetzt informieren: http://www.gmx.net/de/go/freephone