469,307 Members | 2,211 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,307 developers. It's quick & easy.

ODBC mdbtool SQL - problem with ORDER BY

4
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??

Expand|Select|Wrap|Line Numbers
  1. my $dbh = DBI->connect('DBI:ODBC:Rotab'}) ;
  2. my $sql = qq/SELECT Ordernr, Kundnr, Kundnamn FROM Ordhuv WHERE Kundnamn LIKE '%link%' ORDER BY Ordernr DESC/;
  3. my $sth = $dbh->prepare($sql);
  4.  
/Fredrik
Aug 4 '07 #1
7 3598
numberwhun
3,503 Expert Mod 2GB
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
Aug 4 '07 #2
solen
4
Sorry for that :(, this is my first add.

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

still same problem.
Aug 4 '07 #3
numberwhun
3,503 Expert Mod 2GB
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
Aug 5 '07 #4
solen
4
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
Aug 5 '07 #5
numberwhun
3,503 Expert Mod 2GB
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
Aug 5 '07 #6
miller
1,089 Expert 1GB
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.

Expand|Select|Wrap|Line Numbers
  1. my $dbh = DBI->connect('DBI:ODBC:Rotab') or die "DB connect failed: $DBI::errstr";
  2. my $sth = $dbh->prepare(qq{SELECT Ordernr, Kundnr, Kundnamn FROM Ordhuv WHERE Kundnamn LIKE '%link%' ORDER BY Ordernr DESC});
  3. $sth->execute or die $dbh->errstr;
  4.  
- Miller
Aug 6 '07 #7
solen
4
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
Aug 8 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by test test | last post: by
6 posts views Thread by Andi Reisenhofer | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.