Thumbnail

Miscellaneous Utility Plugin

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

+ How To Add This Control To Your Project (Click to Expand)
  1. Make sure you have the latest version of ARC installed.
  2. Select the Get button in this page to download the archive file.
  3. Double click the downloaded archive file to execute installer.
  4. The installer will add this control to ARC.
  5. Load ARC and press the Project -> Add Control button from the menu.
  6. Choose the Misc category tab.
  7. Press the Miscellaneous Utility Plugin icon to add the control to your project.

Manual

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)

PRO
Canada
#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!
PRO
Canada
#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?
PRO
Canada
#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.
PRO
Canada
#7  
yes good that's work thank you very much ptp and DJ for your help;)