Zugg Software :: View topic - including a linefeed char in an Sql statement

Beginner Joined: 19 Feb 2005 Posts: 12

Hi again :)

You all have been so very helpful, I have returned yet again with another question. I am building Sql statements inside zMUD and then passing them on to my database via an ADO object.

This is all fine and good until I wish to, say insert a new record that contains text fields that might include the ascii char for a linefeed (character 10 of the ascii set). When I do this it "breaks" the syntax of the statement the same as if I had actually hit return on my keyboard at the point of the linefeed character.

Anyone know how to escape this character so that it won't break SQL syntax while being passed to the database?

Thanks!

Enchanter Joined: 05 Mar 2003 Posts: 593 Location: Canada

use the %char command
%char(10)

Beginner Joined: 19 Feb 2005 Posts: 12

I do use the %char(10) command to insert linefeed characters into the SQL statement as I build it -- or more specifically I use %char() to build a variable of a field which in turn is used to build the SQL statement. But, as I said when it comes time to pass on the @Sql variable to the ADO object things go bad.

Enchanter Joined: 05 Mar 2003 Posts: 593 Location: Canada

show us some code and examples,also this may be a case for the %expand function

Beginner Joined: 19 Feb 2005 Posts: 12

@object_desc contains:

Under a simple hard brown crust, this roll has a moist, chewy centre filled with#flecks of fruits and vegetables.

*note the "#" above actually is my silly representation for the linefeed character. inside my zmud trigger editor screen widget, it looks like an empty square.

Later, I insert this variable into the much longer @Sql variable. Once the @Sql variable is built, I create a new record in the database.

Enchanter Joined: 05 Mar 2003 Posts: 593 Location: Canada

You were a little stingy with the code so I guessed Wink

Beginner Joined: 19 Feb 2005 Posts: 12

Hrm. I ran your code on my machine, and it behaves exactly as you say, but that's not the behavior I want. Yes the SQL variable that is sent to the execute method looks like you say, to whit:
INSERT INTO TheTable (TheField) VALUES('@object_desc')

BUT, the behavior I desire is not that the literal "@object_desc" is pasted inside the SQL, but that the CONTENTS of @object_string are pasted inside the SQL variable such that the final product looks like:

INSERT INTO TheTable (TheField) VALUES('{Troubleshooting: If you have trouble connecting to a MUD and get the error message "can’t lookup address",
then you probably have an incorrect nameserver setup. }')

Of course, with the above, perhaps you can see why the newline character is causing a problem: it's not see the closing single quote and parentheses because those exist on a newline. The specific error that I get is:

Error parsing command:
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

Beginner Joined: 19 Feb 2005 Posts: 12

Furthermore, if I make the equivalent change to my code that you have in yours (that is, surround the @object_desc with quotes):

Wizard Joined: 15 Jan 2002 Posts: 1644 Location: USA

Well first things first if there is a ' in the string it needs to be quoted out for valid SQL, second you prolly want CR (carriage return %char(13)) and not LF and are you saying the @OBJ desc already has newlines in it? That you don't want the newlines? or what Im still not sure what your trying to do.
IF @obj_desc looks like this in the settings editor

Obj_desc=
This is line one
This is line two mm'kay

Then you need this
#VAR sql {INSERT INTO `table` (`field`) VALUES ('%replace(@obj_desc,"'","''")')}

However if it looks like this
obj_desc=
This is line one;this is line two

then you need this
#VAR sql {INSERT INTO `table` (`field`) VALUES ('%replace(%replace(@obj_desc,"'","''"),";",%char(13))')}

Until I know exactly what your trying to do I can't really help you and its hard to tell from your post :p
Ok I think I might understand what your getting at but prolly not......
//For breaks
#VAR sql {INSERT INTO `table` (`field`) VALUES ('%replace(%replace(@obj_desc,"'","''"),"%char(10)",%char(13))')}
//remove LFs
#VAR sql {INSERT INTO `table` (`field`) VALUES ('%replace(%replace(@obj_desc,"'","''"),"%char(10)"," ")')}

Enchanter Joined: 05 Mar 2003 Posts: 593 Location: Canada

for me the value of @object_desc is passed into the field
its obvious your building your strings differently from mine.
also the error you're getting says nothing about newlines to indicate
that newlines are the source of your woes.

Beginner Joined: 19 Feb 2005 Posts: 12

Hi nexela. I do preface each single quote with another single quote already. I realize you can't tell that from the code posted, but when I build the @object_desc from a trigger I use the %replace to accomplish this.

I tried using %char(13) instead of %char(10) as you suggested with the same results.

I don't understand what part of my explanation is unclear, frankly. Try thinking of it this way, it seems to be as if I was literally typing in the SQL statement at the keyboard to send to the execute method. The execute method won't parse my input until I hit "enter" on my keyboard, but as soon as I do, then (amongst many other things) it checks for correct syntax. My problem is that as part of my input string I need to send a carriage return/linefeed/whatever and so this prematurely causes me to "hit enter" and thus the execute method parses only the part of the SQL statement that preceeds the %char(13) or %char(10).

Is that any clearer?

Wizard Joined: 15 Jan 2002 Posts: 1644 Location: USA

I was able to pass multi lined Variables into a Mysql text field without a problem and without it breaking prematurly. As dharkeal suggested try posting the complete script and we can see where you are going wrong.

Beginner Joined: 19 Feb 2005 Posts: 12

Woohooooo!! Success! Thank you very much, Darkhael, your suggestions/code got me thinking and I retried your idea with success this time. Here is what I did.

Old code:

Enchanter Joined: 05 Mar 2003 Posts: 593 Location: Canada

I'm pleased it worked out for you.
[sermon]As brevity really isnt a virtue in situations like this
even if you think you know what the problem is, you may be wrong
if you post all your code and its irrelevant, nothings hurt by it,
but otherwise it means a lot of people get to examine the code
and maybe catch, something you and most others may miss.
cutting and pasting code isnt really labour intensive
and often results in faster resolution.
[/sermon]
Wink

sorry I had to!
Anyways in answer to your follow up:

Beginner Joined: 19 Feb 2005 Posts: 12

Thanks, again Dharkael. You rock. And, yeah, generally speaking I'm in full agreement with your sermon so, worry not, it doesn't fall on deaf ears. Had I not been able to solve the problem I would have spammed you with gajillions of lines of concat statements :P