rudi Posted May 8, 2019 Posted May 8, 2019 Hi. For retrieving the data on the Server itself this command is working fine, but I'd like to do the query over LAN: Tool, included in the MS SQL Managment Studio: C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\bcp.exe I've searched and found several approaches (_SQL.UDF, MSSQL.UDF, the sqlite stuff), but are not really familiar with SQL. What direction is best to take to do this query from a remote WS with sa auth against a mssql Server? bcp.exe "SELECT max([FI_FileIterationID]) from [MuM_PSJobserver].[dbo].[FileIterationBuffer]" queryout "c:\temp\LastIterationID-JobSrv.TXT" -d -Sgoe-vault01\autodeskvault -Usa -PMyPlainTextPassword -c -C1252 The Server Hostname is "Goe-Vault01" The Database is "AutodeskVault" Earth is flat, pigs can fly, and Nuclear Power is SAFE!
Nine Posted May 8, 2019 Posted May 8, 2019 I would recommend this : “They did not know it was impossible, so they did it” ― Mark Twain Reveal hidden contents Block all input without UAC Save/Retrieve Images to/from Text Monitor Management (VCP commands) Tool to search in text (au3) files Date Range Picker Virtual Desktop Manager Sudoku Game 2020 Overlapped Named Pipe IPC HotString 2.0 - Hot keys with string x64 Bitwise Operations Multi-keyboards HotKeySet Recursive Array Display Fast and simple WCD IPC Multiple Folders Selector Printer Manager GIF Animation (cached) Debug Messages Monitor UDF Screen Scraping Round Corner GUI UDF Multi-Threading Made Easy Interface Object based on Tag
mLipok Posted May 8, 2019 Posted May 8, 2019 .... _ADO_Execute("SELECT max([FI_FileIterationID]) from [MuM_PSJobserver].[dbo].[FileIterationBuffer]") ..... 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: Reveal hidden contents 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
rudi Posted May 9, 2019 Author Posted May 9, 2019 Thanks to both for your reply. In the ADO_EXAMPLE.au3 I found _Example_4_MSSQL(). I try and failed to use it with this modifications: expandcollapse popup#include "H:\DATEN\PRIVATE\SYSOP\NETZ\Batch\autoit3\Snippets\ADO-2.1.15-BETA\ADO.au3" $Server="Goe-Vault01" $Database="GoeVault01\AUTODESKVAULT" $User="sa" $Pass="MyPlainTextPassword" ; Jobserver $Query="SELECT max([FI_FileIterationID]) from [MuM_PSJobserver].[dbo].[FileIterationBuffer]" $LastIterationJobSrv=MSSQL_Query($Server,$Database,$User,$Pass,$Query) ; Vault $Query="SELECT max([FileIterationID]) from [Vault].[dbo].[FileIteration]" $LastIterationVault=MSSQL_Query($Server,$Database,$User,$Pass,$Query) Func MSSQL_Query($sServer, $sDatabase, $sUser, $sPassword, $sQUERY) ; Create connection object Local $oConnection = _ADO_Connection_Create() ; Open connection with $sConnectionString _ADO_Connection_OpenMSSQL($oConnection, $sServer, $sDatabase, $sUser, $sPassword, 'YourAppName', @ComputerName & '_' & 'YourProgram_UID') If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE) ; Executing some query directly to Array of Arrays (instead to $oRecordset) Local $aRecordset = _ADO_Execute($oConnection, $sQUERY, True) ; Clean Up _ADO_Connection_Close($oConnection) $oConnection = Null ; Display Array Content with column names as headers _ADO_Recordset_Display($aRecordset, 'Recordset content') EndFunc Maybe because I didn't use the correct 'YourAppName' and 'YourProgram_UID', what values might be appropriate? >"C:\Program Files (x86)\AutoIt3\SciTE\..\AutoIt3.exe" "C:\Program Files (x86)\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.au3" /run /prod /ErrorStdOut /in "H:\DATEN\PRIVATE\SYSOP\NETZ\Batch\autoit3\Snippets\ADO-2.1.15-BETA\LastIterationID_Jobserver_Vault_Query.au3" /UserParams +>12:19:57 Starting AutoIt3Wrapper v.17.224.935.0 SciTE v.3.7.3.0 Keyboard:00000407 OS:WIN_7/Service Pack 1 CPU:X64 OS:X64 Environment(Language:0407) CodePage:0 utf8.auto.check:4 +> SciTEDir => C:\Program Files (x86)\AutoIt3\SciTE UserDir => C:\Users\admin\AppData\Local\AutoIt v3\SciTE\AutoIt3Wrapper SCITE_USERHOME => C:\Users\admin\AppData\Local\AutoIt v3\SciTE >Running AU3Check (3.3.14.5) from:C:\Program Files (x86)\AutoIt3 input:H:\DATEN\PRIVATE\SYSOP\NETZ\Batch\autoit3\Snippets\ADO-2.1.15-BETA\LastIterationID_Jobserver_Vault_Query.au3 "H:\DATEN\PRIVATE\SYSOP\NETZ\Batch\autoit3\Snippets\ADO-2.1.15-BETA\ADO.au3"(381,89) : error: _ArrayDisplay() called with wrong number of args. _ArrayDisplay($aSelect, $sTitle, "", 0, '|', $sArrayHeader, Default, $iAlternateColors) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^ "C:\Program Files (x86)\AutoIt3\Include\Array.au3"(480,176) : REF: definition of _ArrayDisplay(). Func _ArrayDisplay(Const ByRef $aArray, $sTitle = Default, $sArrayRange = Default, $iFlags = Default, $vUser_Separator = Default, $sHeader = Default, $iMax_ColWidth = Default) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^ "H:\DATEN\PRIVATE\SYSOP\NETZ\Batch\autoit3\Snippets\ADO-2.1.15-BETA\ADO.au3"(385,90) : error: _ArrayDisplay() called with wrong number of args. _ArrayDisplay($aRecordset, $sTitle, "", 0, Default, Default, Default, $iAlternateColors) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^ "C:\Program Files (x86)\AutoIt3\Include\Array.au3"(480,176) : REF: definition of _ArrayDisplay(). Func _ArrayDisplay(Const ByRef $aArray, $sTitle = Default, $sArrayRange = Default, $iFlags = Default, $vUser_Separator = Default, $sHeader = Default, $iMax_ColWidth = Default) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^ H:\DATEN\PRIVATE\SYSOP\NETZ\Batch\autoit3\Snippets\ADO-2.1.15-BETA\LastIterationID_Jobserver_Vault_Query.au3 - 2 error(s), 0 warning(s) !>12:19:57 AU3Check ended. Press F4 to jump to next error.rc:2 +>12:19:57 AutoIt3Wrapper Finished. >Exit code: 2 Time: 0.9177 Earth is flat, pigs can fly, and Nuclear Power is SAFE!
rudi Posted May 13, 2019 Author Posted May 13, 2019 (edited) Hi again. I had to modify the ADO.AU3 UDF this way to get rid of this two Errors: Then this script ... expandcollapse popup#include <Debug.au3> #include "h:\daten\private\sysop\netz\batch\autoit3\snippets\ADO-2.1.15-BETA\ado.au3" #Tidy_Parameters=/sort_funcs /reel #AutoIt3Wrapper_Run_AU3Check=Y #AutoIt3Wrapper_Au3Check_Parameters=-d -w 1 -w 2 -w 3 -w- 4 -w 5 -w 6 -w 7 #AutoIt3Wrapper_Run_Au3Stripper=Y #Au3Stripper_Parameters=/RM #include <Array.au3> #include <MsgBoxConstants.au3> #include <AutoItConstants.au3> Local $sDatabase = 'MuM_PSJobserver' ; change this string to YourDatabaseName Local $sServer = 'goe-vault01\AUTODESKVAULT' ; change this string to YourServerLocation Local $sUser = 'sa' ; change this string to YourUserName Local $sPassword = 'MyPlainTextPasswOrD' ; change this string to YourPassword Local $Query="SELECT max([FI_FileIterationID]) from [MuM_PSJobserver].[dbo].[FileIterationBuffer]" _MSSQL_Query($sServer, $sDatabase, $sUser, $sPassword, $Query) Local $QueryTop10="SELECT TOP (10) [FI_FileIterationID] from [MuM_PSJobserver].[dbo].[FileIterationBuffer]" _MSSQL_Query($sServer, $sDatabase, $sUser, $sPassword, $QueryTop10) Func _MSSQL_Query($sServer, $sDatabase, $sUser, $sPassword, $sQUERY) ; Create connection object Local $oConnection = _ADO_Connection_Create() ; Open connection with $sConnectionString _ADO_Connection_OpenMSSQL($oConnection, $sServer, $sDatabase, $sUser, $sPassword) ; not specified: , 'YourAppName', @ComputerName & '_' & 'YourProgram_UID') If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE) ; Executing some query directly to Array of Arrays (instead to $oRecordset) Local $aRecordset = _ADO_Execute($oConnection, $sQUERY, True) ; Clean Up _ADO_Connection_Close($oConnection) $oConnection = Null _DebugArrayDisplay($aRecordset,UBound($aRecordset)) for $i = 0 to UBound($aRecordset) - 1 if IsArray($aRecordset[$i]) Then _DebugArrayDisplay($aRecordset[$i],"Array Element " & $i) Else MsgBox(0,"Array Element " & $i,$aRecordset[$i]) EndIf Next ; Display Array Content with column names as headers _ADO_Recordset_Display($aRecordset, 'Recordset content') EndFunc ;==>_Example_4_MSSQL Func _ErrDescription($sDescription = Default) Local Static $sDescription_static = '' If $sDescription <> Default Then $sDescription_static = $sDescription Return $sDescription_static EndFunc ;==>_ErrDescription Func _ErrFunc($oError) ConsoleWrite( _ @ScriptName & " (" & $oError.scriptline & ") : ==> COM Error intercepted !" & _ "$oError.description is: " & @TAB & $oError.description & @CRLF & _ "$oError.windescription: " & @TAB & $oError.windescription & @CRLF & _ "$oError.number is: " & @TAB & Hex($oError.number, 8) & @CRLF & _ "$oError.lastdllerror is: " & @TAB & $oError.lastdllerror & @CRLF & _ "$oError.scriptline is: " & @TAB & $oError.scriptline & @CRLF & _ "$oError.source is : " & @TAB & $oError.source & @CRLF & _ "$oError.helpfile is: " & @TAB & $oError.helpfile & @CRLF & _ "$oError.helpcontext is: " & @TAB & $oError.helpcontext & @CRLF _ ) _ErrDescription($oError.description) ; store description to use it outsided UDF in your own function EndFunc ;==>_ErrFunc ... is working, but I don't get all of its Output: For the first query, that is "asking" for just the max value, the results are this: for the 2nd query, that is "asking" for the top ten values, the result is that one: Questions: What do I miss to receive the column Name as element 2 (Row 1) in the original result Array, when doing a query just for the (single) max value? Why do I have to "comment out" parts of the line ... _ArrayDisplay($aSelect, $sTitle, "", 0, '|', $sArrayHeader, Default, $iAlternateColors) ... to ... _ArrayDisplay($aSelect, $sTitle, "", 0, '|', $sArrayHeader); , Default, $iAlternateColors) ... do I specify wrong Parameters? CU, Rudi. Edited May 13, 2019 by rudi Earth is flat, pigs can fly, and Nuclear Power is SAFE!
rudi Posted April 14, 2022 Author Posted April 14, 2022 (edited) Hi, after quite some time I used a workaround with SSMS's BCP.EXE (output to txt file, then read that output. Quite ugly approach) bcp.exe "SELECT max([FI_FileIterationID]) from [MuM_PSJobserver].[dbo].[FileIterationBuffer]" queryout "c:\full\path\bcp-output.txt" -d -Sgoe-vault03\autodeskvault -Usa -PPlainTextPasswordHere -c -C1252 due to updates for our Vault Server I got in touch again with this issue, to directly place a SQL query on our SQL server: @ChrisL @mLipok thanks for your ADO.au3, I noticed, that there is a new beta version: in 2019 I used ADO-2.1.15-BETA now there is ADO 2.1.19 BETA Could someone please point me in the right direction to SELECT a single result value, using these specs? This script is giving me no results, just these errors. ; from example script ADO_EXAMPLE.au3, no output at all Func _Example_MSSQL_SQLServerAuthorization() Local $sDriver = 'SQL Server' Local $sDatabase = 'autodeskvault' ; change this string to YourDatabaseName Local $sServer = 'goe-vault03\autodeskvault' ; change this string to YourServerLocation Local $sUser = 'sa' ; change this string to YourUserName Local $sPassword = 'PlainTextPasswordHere' ; change this string to YourPassword Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';' Local $sMySelect='SELECT max([FI_FileIterationID]) from [MuM_PSJobserver].[dbo].[FileIterationBuffer]' _Example_1_RecordsetToConsole($sConnectionString, $sMySelect) If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE) _Example_2_RecordsetDisplay($sConnectionString, $sMySelect) If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE) _Example_3_ConnectionProperties($sConnectionString) If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE) _Example_4_MSSQL_SQLAuth($sServer, $sDatabase, $sUser, $sPassword,$sMySelect) EndFunc ;==>_Example_MSSQL >"C:\Program Files (x86)\AutoIt3\SciTE\..\AutoIt3.exe" "C:\Program Files (x86)\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.au3" /run /prod /ErrorStdOut /in "H:\DATEN\PRIVATE\SYSOP\NETZ\Batch\autoit3\Snippets\ADO 2.1.19 BETA\ADO_EXAMPLE.au3" /UserParams +>16:58:10 Starting AutoIt3Wrapper v.18.708.1148.0 SciTE v.4.1.0.0 Keyboard:00000407 OS:WIN_10/ CPU:X64 OS:X64 Environment(Language:0407) CodePage:0 utf8.auto.check:4 +> SciTEDir => C:\Program Files (x86)\AutoIt3\SciTE UserDir => C:\Users\admin.AD\AppData\Local\AutoIt v3\SciTE\AutoIt3Wrapper SCITE_USERHOME => C:\Users\admin.AD\AppData\Local\AutoIt v3\SciTE >Running AU3Check (3.3.14.5) params:-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6 -w 7 from:C:\Program Files (x86)\AutoIt3 input:H:\DATEN\PRIVATE\SYSOP\NETZ\Batch\autoit3\Snippets\ADO 2.1.19 BETA\ADO_EXAMPLE.au3 +>16:58:11 AU3Check ended.rc:0 >Running:(3.3.14.5):C:\Program Files (x86)\AutoIt3\autoit3.exe "H:\DATEN\PRIVATE\SYSOP\NETZ\Batch\autoit3\Snippets\ADO 2.1.19 BETA\ADO_EXAMPLE.au3" --> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop ! ---> @error=4 @extended=-2147352567 : _Example_MSSQL_SQLServerAuthorization() @@ Debug(69) : $Result = -1 >Error code: 4 +>16:58:11 AutoIt3.exe ended.rc:0 +>16:58:11 AutoIt3Wrapper Finished. >Exit code: 0 Time: 1.158 Edited April 14, 2022 by rudi Earth is flat, pigs can fly, and Nuclear Power is SAFE!
mLipok Posted April 14, 2022 Posted April 14, 2022 (edited) On 4/14/2022 at 2:55 PM, rudi said: ! ---> @error=4 @extended=-2147352567 : _Example_MSSQL_SQLServerAuthorization() Expand DISP_E_EXCEPTION 0x80020009 Exception occurred. Focus on: Local $sDatabase = 'autodeskvault' ; change this string to YourDatabaseName I think this should be 'MuM_PSJobserver' btw. Please show to us COMError handler message. Edited April 14, 2022 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: Reveal hidden contents 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
mLipok Posted April 14, 2022 Posted April 14, 2022 Also take a look here: https://answers.microsoft.com/en-us/msoffice/forum/all/run-time-error-2147352567-the-value-you-entered/e676d41b-5ffb-49ad-8b65-5571f5dd17dc 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: Reveal hidden contents 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
rudi Posted April 14, 2022 Author Posted April 14, 2022 @mLipok Thanks for your reply. You are absolutely right 👏 This is now working fine: expandcollapse popup#include "h:\daten\private\sysop\netz\batch\autoit3\snippets\ADO-2.1.15-BETA\ado.au3" $OpenFileIterations = OpenFileIterationCount() $Err = @error $Icon = @extended ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $OpenFileIterations = ' & $OpenFileIterations & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console If $Err Then MsgBox($MB_ICONERROR, "Error", "Could not get the Vault Server and Job Server FileiterationIDs!" & @CRLF & _ "SQL Query or ADO error.") Else MsgBox($Icon, "Jobserver Check", $OpenFileIterations & " Vault File Iterations waiting to be processed.") EndIf Func OpenFileIterationCount() ; Success: <int> = difference between Vault and Jobserver: Number of File Iterations not processed so far ; Failure: false Local $sDriver = 'SQL Server' Local $sDatabase = 'MuM_PSJobserver' ; change this string to YourDatabaseName Local $sServer = 'goe-vault03\autodeskvault' ; change this string to YourServerLocation Local $sUser = 'sa' ; change this string to YourUserName Local $sPassword = 'PlainTextPasswordHere' ; change this string to YourPassword Local $sConnectionString = 'DRIVER={' & $sDriver & '};SERVER=' & $sServer & ';DATABASE=' & $sDatabase & ';UID=' & $sUser & ';PWD=' & $sPassword & ';' Local $sMySelect = 'SELECT max([FI_FileIterationID]) from [MuM_PSJobserver].[dbo].[FileIterationBuffer]' Local $VaultFI Local $JobSrvFI $JobSrvFI = SelectOneMaxValue($sConnectionString, $sMySelect) $err = @error ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $JobSrvFI = ' & $JobSrvFI & @CRLF & '>Error code: ' & $err & @CRLF) ;### Debug Console If $err Then Return False $sDatabase = "Vault" $sMySelect = "SELECT max([FileIterationID]) from [Vault].[dbo].[FileIteration]" $VaultFI = SelectOneMaxValue($sConnectionString, $sMySelect) $err = @error ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $VaultFI = ' & $VaultFI & @CRLF & '>Error code: ' & $err & @CRLF) ;### Debug Console If $err Then Return False $OpenIterations = $VaultFI - $JobSrvFI Switch $OpenIterations Case 0 To 5 Return SetError(0, $MB_ICONINFORMATION, $OpenIterations) Case 6 To 15 Return SetError(0, $MB_ICONWARNING, $OpenIterations) Case 15 To 25 Return SetError(0, $MB_ICONERROR, $OpenIterations) Case Else Return SetError(1, $MB_ICONERROR, $OpenIterations) EndSwitch EndFunc ;==>OpenFileIterationCount Func SelectOneMaxValue($sConnectionString, $sQUERY) ; Create connection object Local $oConnection = _ADO_Connection_Create() ; Open connection with $sConnectionString _ADO_Connection_OpenConString($oConnection, $sConnectionString) If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE) ; Executing some query Local $oRecordset = _ADO_Execute($oConnection, $sQUERY) If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE) ; Get recordset to array of arrays (Conent and ColumnNames) Local $aRecordsetAsArray = _ADO_Recordset_ToArray($oRecordset, False) If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE) ; Get inner array - only conent of Recordset Local $aRecordsetContent = _ADO_RecordsetArray_GetContent($aRecordsetAsArray) If @error Then Return SetError(@error, @extended, $ADO_RET_FAILURE) ; Clean Up $oRecordset = Null _ADO_Connection_Close($oConnection) $oConnection = Null If IsArray($aRecordsetContent) Then If ((UBound($aRecordsetContent, $UBOUND_DIMENSIONS) = 2) And (UBound($aRecordsetContent, $UBOUND_COLUMNS) = 1) And (UBound($aRecordsetContent, $UBOUND_ROWS) = 1)) Then ; Okey: 2D Array, one row, one collumn, valid result Return $aRecordsetContent[0][0] Else Return SetError(1, 0, $aRecordsetContent) EndIf Else Return SetError(1, 1, $aRecordsetContent) EndIf EndFunc ;==>SelectOneMaxValue Earth is flat, pigs can fly, and Nuclear Power is SAFE!
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