"\r" is interpreted as new line character when executing ADODB query

[psined1]

Hi All. When I execute a SQL command string using ADODB.Connection object I find that any "r" string is substituted by new line feed character before it gets sent to SQL server. Here's my code:

 Local $sql = "exec sp_Do_Something @usr='{0}'"

 $sql = StringReplace($sql, "{0}", "domainrandall")

 Dim $conn = ObjCreate("ADODB.Connection")
 $conn.Open($DSN)  ; <-- connection string goes here
 $conn.Execute($sql)
 $conn.close()
 

When I use SQL profiler the code being executed looks literally like this on the SQL server end:

exec sp_Do_Something @usr='domain

andall'

Note the new line field is fed instead of "r".

I know I could perhaps use ADODB.Connection object with parameteres and the whole nine yards. Before I change the code drastically, which otherwise works fine, I wanted to see if there is a simple hack you can recommend to prevent Autoit translate "r" into a line feed character.

 

[mLipok]

Can you post your Query ?

I do not understand what you want to achieve ?

It looks like you're inside a query want to change username and password.

Select the user and password you should do here:

 

$conn.Open($DSN)  ; <-- connection string goes here

btw.

please use the "A" button on text that is AutoIt Script

this help in reading posts

 

Welcome to the forum

remember read forum rules

http://www.autoitscript.com/forum/index.php?app=forums&module=extras&section=boardrules

Edited August 28, 2013 by mlipok

[kylomas]

psined1,

Run the following:

Local $sql = "exec sp_Do_Something @usr='{0}'"

$sql = StringReplace($sql, "{0}", "domain\randall")

;~ Dim $conn = ObjCreate("ADODB.Connection")
;~ $conn.Open($DSN) ; <-- connection string goes here
;~ $conn.Execute($sql)
;~ $conn.close()

ConsoleWrite($sql & @LF)

and note the output in the console.

Your problem is NOT in AutoIT.  You may need to escape this character sequence in some way.

Good Luck,

kylomas

Edited August 28, 2013 by kylomas

[saudumm]

Hi,

does it work if you escape the backslash () in your code like this:

Local $sql = "exec sp_Do_Something @usr='{0}'"

$sql = StringReplace($sql, "{0}", "domain\\randall")

Dim $conn = ObjCreate("ADODB.Connection")
$conn.Open($DSN)  ; <-- connection string goes here
$conn.Execute($sql)
$conn.close()

[psined1]

Hi, and thanks for responding. To answer some suggestions above...

The $DSN is a variable that is decalred and contains appropriate connection string elsewhere in the code. The SQL executes without error, so this is not my problem.

The ConsoleWrite($sql & @LF) reveals line break between "domain" and "andall".

The actual code does not contain a hard-coded "domainrandall" string. It actually uses a variable, the value for which comes from command arguments when the script is executed, like this:

$Usr = $CmdLine[1]
$sql = StringReplace($sql, "{0}", $Usr)

then the application is executed as follows:

c\:> myAutoitApp domain\randall

Hope this clarifies.

Could this be an issue in $CmdLine collection?

Appreciate everyone's help.

Edited August 28, 2013 by psined1

[psined1]

Hi again, it looks like I was able to fix this by the following:

$Usr = $CmdLine[1] 
$Usr = StringReplace($Usr, @CR, "\r")
$Usr = StringReplace($Usr, @LF, "\n") 
$sql = StringReplace($sql, "{0}", $Usr)

So this must definitely have something to do with AutoIt processing command line arguments.

Edited August 28, 2013 by psined1

[kylomas]

psined1,

The ConsoleWrite($sql & @LF) reveals line break between "domain" and "andall".

 

This results in

 

>"C:\Program Files (x86)\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.exe" /run /prod /ErrorStdOut /in "C:UsersADMIN010Desktoprr.au3" /UserParams   
+>16:35:37 Starting AutoIt3Wrapper v.2.1.0.33    Environment(Language:0409  Keyboard:00000409  OS:WIN_7/Service Pack 1  CPU:X64 OS:X64)
>Running AU3Check (1.54.22.0)  from:C:Program Files (x86)AutoIt3
+>16:35:37 AU3Check ended.rc:0
>Running:(3.3.8.1):C:Program Files (x86)AutoIt3autoit3.exe "C:UsersADMIN010Desktoprr.au3"   
--> Press Ctrl+Alt+F5 to Restart or Ctrl+Break to Stop
exec sp_Do_Something @usr='domainrandall'
+>16:35:38 AutoIT3.exe ended.rc:0
>Exit code: 0    Time: 1.282

 

on my system.

Glad you got it working but I'm not sure that I understand why it works (or had a problem to begin with).

kylomas

[psined1]

psined1,

 

This results in

 

on my system.

Glad you got it working but I'm not sure that I understand why it works (or had a problem to begin with).

kylomas

I think it has something to do with $CmdLine object rather than ADODB...