ARC Pro

Upgrade to ARC Pro

Experience early access to the latest features and updates. You'll have everything that is needed to unleash your robot's potential.

Thumbnail

Miscellaneous Utility Plugin

by Avatarptp

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

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:

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)

ARC Pro

Upgrade to ARC Pro

Synthiam ARC Pro is a cool 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;)