psined1 Posted August 27, 2013 Share Posted August 27, 2013 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. Link to comment Share on other sites More sharing options...
mLipok Posted August 27, 2013 Share Posted August 27, 2013 (edited) 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§ion=boardrules Edited August 28, 2013 by mlipok Signature beginning:* Please remember: "AutoIt"..... * Wondering who uses AutoIt and what it can be used for ? * Forum Rules ** ADO.au3 UDF * POP3.au3 UDF * XML.au3 UDF * IE on Windows 11 * How to ask ChatGPT for AutoIt Code * 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 * ErrorLog.au3 UDF - A logging Library * Include Dependency Tree (Tool for analyzing script relations) * Show_Macro_Values.au3 * 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 * Dual Monitor resolution detection * * 2GUI on Dual Monitor System * _SciLexer.au3 UDF * SciTE - Lexer for console pane * 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 * OpenOffice/LibreOffice/XLS Related: WriterDemo.au3 * XLS/MDB from scratch with ADOX 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) * IE in TaskScheduler * IE Embedded Control Versioning (use IE9+ and HTML5 in a GUI) * PDF Related: * How to get reference to PDF object embeded in IE * IE on Windows 11 * 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 *I also encourage you to check awesome @trancexx code: * Create COM objects from modules without any demand on user to register anything. * Another COM object registering stuff * OnHungApp handler * Avoid "AutoIt Error" message box in unknown errors * HTML editor * winhttp.au3 related : * https://www.autoitscript.com/forum/topic/206771-winhttpau3-download-problem-youre-speaking-plain-http-to-an-ssl-enabled-server-port/ "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" , 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: 2023-04-24 Link to comment Share on other sites More sharing options...
kylomas Posted August 28, 2013 Share Posted August 28, 2013 (edited) 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 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 Link to comment Share on other sites More sharing options...
saudumm Posted August 28, 2013 Share Posted August 28, 2013 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() Link to comment Share on other sites More sharing options...
psined1 Posted August 28, 2013 Author Share Posted August 28, 2013 (edited) 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 Link to comment Share on other sites More sharing options...
Solution psined1 Posted August 28, 2013 Author Solution Share Posted August 28, 2013 (edited) 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 Link to comment Share on other sites More sharing options...
kylomas Posted August 28, 2013 Share Posted August 28, 2013 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 Stopexec 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 Link to comment Share on other sites More sharing options...
psined1 Posted August 29, 2013 Author Share Posted August 29, 2013 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... Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now