Thumbnail

Miscellaneous Utility Plugin

by Avatarptp

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

Requires ARC v6 (Updated 2/4/2021) Source Code

How to add the Miscellaneous Utility Plugin robot skill

  1. Load the most recent release of ARC (Get ARC).
  2. Press the Project tab from the top menu bar in ARC.
  3. Press Add Robot Skill from the button ribbon bar in ARC.
  4. Choose the Misc category tab.
  5. Press the Miscellaneous Utility Plugin icon to add the robot skill to your project.

Don't have a robot yet?

Follow the Getting Started Guide to build a robot and use the Miscellaneous Utility Plugin robot skill.

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)


ARC Pro

Upgrade to ARC Pro

Synthiam ARC Pro is a new tool that will help unleash your creativity with programming robots in just seconds!

#1  

Hello , thank you for this plugin but I am not able to install it in ARC. I have this error message. Can you help me please ?

User-inserted image

PRO
USA
#2   — Edited

I'll update the plugin to fix the error. *** EDITED *** UPDATED!

#3  

Hello ptp,

thank you for your update. That work. But do you have a tutorial for your plugin please ? Can i modify Data Source and Table parameter on the source code of this plugin ?

PRO
Synthiam
#4  

Hey kobotik, I haven't used this plugin but I might be able to steer you in a direction. Are you asking to send update commands to the database?

#5  

Hi DJ,  In fact, I would like to read and update a sql database with this plugin. Is it possible ?

PRO
USA
#6  

Hi Guys.

Sorry I missed posts: 3,4

Quote:

Can i modify Data Source and Table parameter on the source code of this plugin
You don't need to modify the source code, the plugin works with a custom connection, so you can customize and use it to access your database or excel files.

Quote:

I would like to read and update a sql database with this plugin. Is it possible
It's possible to read and update data sources. I've provided a few examples. I update the plugin details to highlight the examples.

#7  

yes good that's work thank you very much ptp and DJ for your help;)