ODBC mdbtool SQL - problem with ORDER BY

[solen]

Hello,

Debian with Perl, unixODBC.

I have a problem with "ORDER BY". An ordinary SQL workes fine and I can print it out to the webpage. But when I add the ORDER BY it stops working. Any Ideas??

my $dbh = DBI->connect('DBI:ODBC:Rotab'}) ;
my $sql = qq/SELECT Ordernr, Kundnr, Kundnamn FROM Ordhuv WHERE Kundnamn LIKE '%link%' ORDER BY Ordernr DESC/;
my $sth = $dbh->prepare($sql);
 

/Fredrik

[numberwhun]

First, you should terminate your code tags with /code instead of just the word code. The example on the right side of editor shows the syntax.

As for your issue, I don't have tons of experience with ODBC, but have you tried executing the SQL to see if it works as it should?

Regards,

Jeff

[solen]

Sorry for that :(, this is my first add.

Yes, I have executed it with
$sth->execute();

still same problem.

[numberwhun]

Actually, I was asking if you are sure that SQL statement works as expected. Can you take it out of your code, and execute it.

For instance, in MySQL, there is a MySQL interactive session which allows you to fully interact with the DB and execute commands. If they work there, they would work in your code.

Regards,

Jeff

[solen]

Well Jeff, the only way I know is to try to execute the SQL-statment with mdb-sql. But I see that this tool does not support ORDER BY command. The SELECT * FROM workes like charm with mdb-sql. I don't know how to continue debugging this error. All over internet people use ORDER BY with no problems...

/Fredrik

[numberwhun]

I have done some checking and as far as the DBI::ODBC piece of your code, I don't find terribly much information beyond that it is for working with an Access database. You may want to check any documentation that you have to see if you can actually do an ORDER BY in that statement.

Regards,

Jeff

[miller]

Hello Fredrik,

As Jeff has already pointed out, if you ever have problems with using a SQL statement under perl, then you should try the statement out under the prompt provided by most databases. It is possible that an ORDER BY isn't allowed by the specific database you're using. We can't know that one way or the other, so you'll just have to test.

Additionally, you should always include error checking in your connect and execute database operations. They are of course only needed during the process of development normally, but they should always be part of every command anyway. In this instance, it is possible that a specific warning concerning the usage of ORDER BY may have been queued, but you aren't reporting it.

my $dbh = DBI->connect('DBI:ODBC:Rotab') or die "DB connect failed: $DBI::errstr";
my $sth = $dbh->prepare(qq{SELECT Ordernr, Kundnr, Kundnamn FROM Ordhuv WHERE Kundnamn LIKE '%link%' ORDER BY Ordernr DESC});
$sth->execute or die $dbh->errstr;
 

- Miller

[solen]

Thanks for the quote Miller.

I have tested to use an error handler, but I never got it to work. I really need som nice example to look were I catch the error Flags. I have tested a few different but with bad results.

If you or someone have a nice sample code for catching errors, please send a link or the code.

Regards
Fredrik