?
Solved

Dreamweaver/MySQL/MyODBC sql LIMIT error

Posted on 2003-03-19
5
Medium Priority
?
603 Views
Last Modified: 2008-02-01
This is killing me, and I imagine it is something simple.

I am working with Dreamweaver, a hosted MySql db, and connecting through MyODBC on my local machine.  With the MySQL Control Center 0.8.10-beta, I have no issue testing the following sql statement, and it returns exactly as it should.

SELECT *
FROM `month_bill_info`
order by bill_id desc
limit 0,1

Simple enough, right?

Well, when I am creating a recordset in Dreamweaver, use the Advanced option, and enter in the exact same SQL, it just doesn't seem to want to go through.  Does this error message mean anything to anyone?

"[TCX][MyODBC] You have an error in your SQL syntax near 'limit 100' line 4"

Since it works with the MySQL interface, I would point to Dreamweaver/MyODBC, but this seems like a pretty simple query that wouldn't cause a problem.  Any option I didn't check eith MyODBC or dreamweaver quirk?

I can actually just force the sql into the recordset anyway, upload the files to the host, and it will work on the site, but it does bother me about not working locally.

Any help would be apprecaited...  Thanks.
0
Comment
Question by:keeper3
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 15

Expert Comment

by:VGR
ID: 8168895
first of all, you simply CAN NOT have "error near 'limit 100'" if you use a query with "limit 0,1" ;-))))))))))

so first check that the query is really the one you want.
Then I would recommend not to use "limit 0,1" but "limit 1"

Lastly, you are NOT using "mySql interface" by using mySql control center (what's that?!?). You should use the MySql CONSOLE (execute "mysql --user=root --password=yourpassword" at the "DOS" level) to test your queries before putting them in yuor scripts.

Good luck.
0
 

Author Comment

by:keeper3
ID: 8169114
MySQL Control Center is a just a GUI for managing MySQL db's and data for those of us who prefer such a thing...

http://www.mysql.com/products/mysqlcc/

Odd thing is, the "limit 100" doesn't seem to be referring to the "limit" statement I am typing, at least not directly.  

If I enter the following erroneous sql...

SELECT *
FROM `month_bill_info`
ORDER BY bill_id desc
limpt 0,1

where I throw in a "p" instead of the 2nd "i" in "limit", I get the following error message:

"[TCX][MyODBC] You have an error in your SQL syntax near 'limpt 0,1 limit 100' line 4"

So basically the same message, but with my incorrect sql statement appended.

As for trying "limit 1", it gives me the same error message.
0
 
LVL 15

Expert Comment

by:VGR
ID: 8169231
THEN the cause of the error is EVIDENT : you GUI tool add itself a "limit 100" to your query !!! (to limit the results and compute time probably)

so ***of course*** you CAN'T specify a limit yourself, because "SELECT ... LIMIT 0,1 LIMIT 100;" is obviously wrong.

Hence the error.

Drop that GUI to the trash and use a real "clean" tool that doesn't add itself things to your queries :D
0
 
LVL 15

Accepted Solution

by:
VGR earned 200 total points
ID: 8169237
demonstration :

mysql> select * from activite limit 0,1;
+--------+-------------+----------+------------------------------------------------------------------------------+---------------------+--------+
| id     | ip          | pseudo   | action                                                                       | heure               | niveau |
+--------+-------------+----------+------------------------------------------------------------------------------+---------------------+--------+
| 349272 | 81.57.193.8 | visiteur | UA = Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 5.0; Crazy Browser 1.0.5) | 2003-02-27 12:48:33 |      4 |
+--------+-------------+----------+------------------------------------------------------------------------------+---------------------+--------+
1 row in set (0.01 sec)

mysql> select * from activite limit 0,1 limit 100;
ERROR 1064: You have an error in your SQL syntax near 'limit 100' at line 1
mysql>
0
 

Author Comment

by:keeper3
ID: 8169294
Got it, makes sense.  Problem is the GUI trash is Dreamweaver, which I say is quite a helpful web development tool.  I'll see if I can find an option somewhere in it on limiting rows.  Appreciate it.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question