Jump to content
Sign in to follow this  
psined1

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

Recommended Posts

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.

 

Share this post


Link to post
Share on other sites
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 by mlipok

Signature beginning:   Wondering who uses AutoIT and what it can be used for ?
* GHAPI UDF - modest begining - comunication with GitHub REST API *
ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button

Spoiler

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAP * WinSCP UDF * GHAPI UDF - modest begining - comunication with GitHub REST API *

My contribution to others projects or UDF based on  others projects: * _sql.au3 UDF  * POP3.au3 UDF *  RTF Printer - UDF * XML.au3 UDF * ADO.au3 UDF SMTP Mailer UDF *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Good coding practices in AutoIt * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * 

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

Signature last update: 2018-03-17

Share this post


Link to post
Share on other sites
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 by kylomas

Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites
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()

Share this post


Link to post
Share on other sites
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 by psined1

Share this post


Link to post
Share on other sites
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 by psined1

Share this post


Link to post
Share on other sites
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


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites
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...

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×