13 messages in com.mysql.lists.win32Re: Converting SQL from Access to MyS...
FromSent OnAttachments
Joelle Tegwen21 Oct 2005 10:11 
Leif Johnston21 Oct 2005 10:19 
Joelle Tegwen21 Oct 2005 10:37 
SGr...@unimin.com21 Oct 2005 10:41 
Daniel da Veiga21 Oct 2005 10:46 
SGr...@unimin.com21 Oct 2005 10:48 
Joelle Tegwen21 Oct 2005 10:58 
Joelle Tegwen21 Oct 2005 11:05 
SGr...@unimin.com21 Oct 2005 11:30 
Joelle Tegwen21 Oct 2005 12:33 
SGr...@unimin.com21 Oct 2005 12:55 
Joelle Tegwen21 Oct 2005 13:46 
jbon...@sola.com.au25 Oct 2005 15:44 
Subject:Re: Converting SQL from Access to MySQL 5.0 Using ASP interface
From:Joelle Tegwen (tegw@umn.edu)
Date:10/21/2005 01:46:05 PM
List:com.mysql.lists.win32

I appreciate you being thorough. I'm finding out there are lots of things I didn't even think of in the conversion. Like it never occurred to me to look deeper at escaping characters when I found out that '' and "" were accepted escapes for ' and ". I've adjusted my function - Thanks.

Nah, I'm to lazy to open and close the connection every time. :)

I didn't think there would be a problem, but it was worth a shot. I'll continue to look.

Thanks much. Joelle

Just to be thorough (It's one of those things I just need to check to cover all of the bases) you do not open and close the connection from within the loop do you? You don't sound like a person that would code that but I can't always tell from an email. You must use the same, continuous connection in order to keep the transaction alive. If it closes before you COMMIT, it will ROLLBACK for you to avoid messing anything up.

I would look next at your special character escaping, the single quote is properly escaped as \' not as ''. There are other characters that also need to be escaped: http://dev.mysql.com/doc/refman/4.1/en/string-syntax.html

That means if you wanted to store the response:

Plato wasn't the Greek that said "Eureka" and went running naked through town.

You would need to write the MySQL UPDATE SQL statement as:

UPDATE responses SET response='Plato wasn\'t the Greek that said \"Eureka\" and\nwent running naked through town.' WHERE wksheet_id = www AND student_id=xxx AND question=yyy

No, there is no problem with multiple logical terms in an UPDATE statement (or any other statement I can think of).

Shawn Green Database Administrator Unimin Corporation - Spruce Pine