Miscellaneous Utility Plugin

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:

Code:

$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 :

Code:

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

ODBC excel 97/2003 Query:

Code:

$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

Code:

$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://www.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.

Code:

$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

Code:

$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

Code:

$start_ticks = Ticks()

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

Code:

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