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" <[email protected]> > An: "glpk xypron" <[email protected]>, [email protected] > 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:[email protected]] > Sent: Sunday, 26 June 2011 4:46 PM > To: Alex Morelli; [email protected] > 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 _______________________________________________ Help-glpk mailing list [email protected] https://lists.gnu.org/mailman/listinfo/help-glpk
