How to use the Miscellaneous Utility Plugin robot skill

This skill contains a variety of utilities for database connectivity and more.

Miscellaneous Utility Plugin

source code: https://github.com/ppedro74/ezrobot-playground/tree/master/plugins/MiscUtilityPlugin version 6: ARC update

version 5: ARC update

version 4 : Added: DataQuery function Syntax: DataQuery (connection, query, [limit], [output variable], [query argument 0], [query argument 1], [query argument n]) => Int32 Connection: Provider Name and connection string. Query: SQL Query Limit: Max Number of records to return. Default 100 Output Variable: Name of Array Variable. Default is $data_ . $data_0 array for column 0 values, $data_1 array for column 1 values, etc. Query Argument 0: Value for query Parameter @0, @1, etc. Returns: Number Of Rows Some Examples:

Excel File (Note Excel 97/2003 format) Excel1.zip

SQL 2012 server Query:

$connection="System.Data.SqlClient|Integrated Security=SSPI;Data Source=.\SQL2012;Initial Catalog=Test1;"
$sql="SELECT * FROM table1;"
$totalRows=DataQuery($connection, $sql)
print($totalRows)

ODBC User DSN MyDSN1 Query :

$connection="System.Data.Odbc|DSN=MyDSN1;"
$sql="SELECT * FROM [Sheet1$];"
$totalRows=DataQuery($connection, $sql, 15)
print($totalRows)

ODBC excel 97/2003 Query:

$connection="System.Data.Odbc|Driver={Microsoft Excel Driver (*.xls)};DBQ=C:\Users\ptp\Desktop\Excel1.xls;"
$sql="SELECT * FROM [Sheet1$];"
$totalRows=DataQuery($connection, $sql, 15)
print($totalRows)

OLEDB excel 97/2003 Query: Paging the results, using a query parameter @0 for $LastId

$connection="System.Data.OleDb|Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Users\ptp\Desktop\Excel1.xls;Extended Properties='Excel 8.0;HDR=YES;';"$sql="SELECT [Id],[Int1],[Date1],[Text1],[DateTime1] FROM [Sheet1$] WHERE [Id]>@0;"

$lastId=0
:loop

Print("Start Id=" + $LastId)
$totalRows=DataQuery($connection, $sql, 10, "$myData", $lastId)
Print("TotalRows=" + $TotalRows)

IF ($totalRows>0)
Print("Rx : Id | Int1 | Date1 | Text1 | DateTime1")
REPEAT ($rx, 0, $totalRows-1, 1)
Print($rx + " : " + $myData0[$rx] + " | " + $myData1[$rx] + " | " + $myData2[$rx] + " | " + $myData3[$rx] + " | " + $myData4[$rx])
ENDREPEAT
$lastId=$myData0[$totalRows-1]

print("LastId=" + $LastId)
Goto(loop)
ENDIF

version 3: Added: DateTimeTicks(), DateTimeTicksToMilliseconds() For testing purposes. thread: https://synthiam.com/Community/Forum/Thread?threadId=11810

version 2:

Changes:

Function Ticks : Description: returns a long integer representing the tick counter value of the underlying timer mechanism. Note 1: Should be used to time tracking (deltas).

Note 2: Use TicksToMilliseconds to convert delta ticks to milliseconds.

$start_ticks=Ticks()
... Do something ...
$end_ticks=Ticks()
$ms = TicksToMilliseconds($end_ticks - $start_ticks)

version 1:

Custom Function: Millis Description: returns the number of milliseconds elapsed since January 1, 1970 00:00:00 UTC Syntax: Millis () => Int64

$start_ms = Millis()

Custom Function: Ticks Description: Returns the number of ticks elapsed since January 1, 1 00:00:00 Gregorian Calendar UTC Syntax: Ticks () => Int64

$start_ticks = Ticks()

Custom Function: TicksToMilliseconds Description: Convert ticks to milliseconds Syntax: TicksToMilliseconds () => Double

$start_ticks=Ticks()
$ms = TicksToMilliseconds($start_ticks)