@ptp,
Ok my friend where did I go wrong. (besides trying this in the first place)
Here is my code. When I debug from VisualStudio, I get unknown function.
I did add my plugin to my project and see the blank form.
I get no errors when I build the project.

Code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Microsoft.Data.Odbc;

namespace DSNConnectQuery
{
    public partial class FormMain :EZ_Builder.UCForms.FormPluginMaster {

        public FormMain() {

            InitializeComponent();
        }
        private void FormMain_Load(object sender, EventArgs e)
        {

            // Intercept all unknown functions called from any  EZ-Script globally.
            // If a function is called that doesn't exist in the  EZ-Script library, this event will execute
            ExpressionEvaluation.FunctionEval.AdditionalFunctionEvent += FunctionEval_AdditionalFunctionEvent;
        }

        private void FormMain_FormClosing(object sender, FormClosingEventArgs e)
        {

            // Disconnect from the function event
            ExpressionEvaluation.FunctionEval.AdditionalFunctionEvent -= FunctionEval_AdditionalFunctionEvent;
        }

        /// 
        /// This is executed when a function is specified in any ez-scripting that isn't a native function.
        /// You can check to see if the function that was called is your function.
        /// If it is, do something and return something.
        /// If you don't return something, a default value of TRUE is returned.
        /// If you throw an exception, the  EZ-Script control will receive the exception and present the error to the user.
        /// 
        private void FunctionEval_AdditionalFunctionEvent(object sender, ExpressionEvaluation.AdditionalFunctionEventArgs e)
        {

            // Check if the function is our function (DSNQuery)
            if (e.Name.Equals("DSNQuery", StringComparison.InvariantCultureIgnoreCase))
            {

                // Check if the correct number of parameters were passed to this function
                if (e.Parameters.Length != 2)
                    throw new Exception("Expects 2 parameters. Usage: DSNQuery(''QueryString'',''DSNname''");

                // Do something
                string myDSN = Convert.ToString(e.Parameters[1]);
                string myQuery = Convert.ToString(e.Parameters[0]);
               
                using (var cn = new OdbcConnection("dsn=" + myDSN))
                {
                    Array[] meta = new Array[10];
                    Array[] myArray = new Array[10];
                    bool read;
                    OdbcCommand command = new OdbcCommand(myQuery, cn);
                    cn.Open();
                    OdbcDataReader reader = command.ExecuteReader();
                    if (reader.Read() == true)
                    {
                        do
                        {
                            int NumberOfCollumns = reader.GetValues(meta);
                            for (int i = 0; i < NumberOfCollumns; i++)
                                myArray[i] = meta[i];
                                read = reader.Read();
                        }
                        while (read == true);
                    }
                    reader.Close();
                    cn.Close();
                    e.ReturnValue = myArray;
                    
                }
                    }
            return;

        }
            }
        }

Didn’t add the event to the ezscript engine. Most likely because form main load and form main closing aren’t actually assigned to their events.

Select the form main. In the event Browser select those events to assign to those methods.

You are going to have to give me more then that.
I see FormMain_Load and FormMain_FormClosing but do not understand what you want me to do with them.
Sorry this C# is all new to me yet.

@rz90208:
you are progressing in the right direction, almost there.

Opening Visual Studio, I'll code the missing logic, give me a couple minutes.

@rz90208:
I added a new function QueryData to the Miscellaneous Utility Plugin:
https://synthiam.com/redirect/legacy?table=plugin&id=293

Source Code:
https://github.com/ppedro74/ezrobot-playground/blob/master/plugins/MiscUtilityPlugin/MiscUtilityPlugin/MainForm.cs

I'm running out time, so minimal documentation, and basic tests no bullet proof code.

**Copied from the plugin page***

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

Try it and let me know the feedback.

Ptp’s got you covered.

As for learning events, google tutorials are great. If you look at the code which you’ve copy and pasted from my tutorial, there’s a formmain_load and formmain_closing

Those aren’t being executed because they’re not connected to anything. Events in object oriented programming are executed when something else executed them. In those case, by their names, loading the form and closing the form.

You need to tell the form to execute those functions when the form loads and when the form is closing.

Do that in the events list of the properties of the form. Locate the form loading and form closing options and select the appropriate methods from the drop down.

Look at this video. It’s terriblr quality but he shows exactly how to find the events for a form: https://youtu.be/LbgO1lZ6WRI

All graphic objects have events. You can explore what events each object has. For example a button has an OnClick event for when it’s pressed

@DJ,
Thank you DJ that now makes sense. I will look when I get home this evening.

@ptp,
Thank you so much. I will study the code and add to my plugin project as a learning experience.

I will post how it all goes on Thursday after the site comes back up.

RichardZ

@ptp,
I tried your plugin and all I can get is the number of hits the query received not the actual data.

Code:

$Connection = "System.Data.Odbc|DSN=EZBrain;"
$sql = "Select MemoryName from MemoryAtom where MemoryType = 1"
$Results = DataQuery($Connection, $sql)
print($Results)


Start
> 97
Done (00:00:00.1249936)

Check the last example.

The function returns the number rows.

Your data is in the array variable $data_0

Add the variable monitor control and you will see the data there.

AWESOME!
Thank you
I looked at all examples but that one was using a different data provider so I just looked over that.

This is great ptp, thank you so much.
when I complete the database brain I owe you a copy.

RichardZ

@ptp,

When trying to insert a record using

$sql = "INSERT INTO MemAtom (MemType, MemData) VALUES (7, '" + $varSTR1 + "')"

$varSTR1 equals one of the 2 lines.

The first line inserts correctly, the second gives me an error Syntax error (missing operator).

1) All the men of thy confederacy have brought thee even to the border: the men that were at peace with thee have deceived thee, and prevailed against thee, that they eat thy bread have laid a wound under thee: there is none understanding in him.

2) All the Nethinims, and the children of Solomon's servants, were three hundred ninety and two.

I wrote a VBScript to do the same thing and get the same result that tells me it is not a bug but something
with my insert statement but I can not figure it out.

I can paste the line into the database just fine. 

Any guess as to what my issue is? with the insert statement I mean.

RichardZ

@rz90208:

SQL Strings use single quotes. If your $varSTR1 contains a single quote will break the query.

That is the reason why I've implemented parameterized queries, at least for strings and numbers it will work (ez-script supported types).

using a parameterized query parameter @0:

Code:

$sql="INSERT INTO Table1([Str1]) VALUES(@0);"

$string1="Test is a test"
DataQuery($connection, $sql, 10, "$myData", $string1)

$string2="For 'single quotes' you will need parameterized queries."
DataQuery($connection, $sql, 10, "$myData", $string2)

PS: The forum is still in beta mode and is crippled i know the things will be fixed soon or later, for faster responses drop me an email : tiago.private AT gmail.com.

@ptp

That worked.
I edited my source document and changed all ' to nothing
saved the doc and all imported without error.

Not the best solution but will work. I don't think the robot will speak the word any different

Servent's or Servents


Thank you again

@PTP
is this addon still working?
I get an error when trying to use in.

Code:

$Connection = "System.Data.Odbc|DSN=EZBrain;"
$sql = "Select MemoryName from MemoryAtom where MemoryType = 1"
$Results = DataQuery($Connection, $sql)
print($Results)

Start
> Error on line 3: Unknown function: DataQuery.
Done (00:00:00.0048909)

closed ARC, reopened, started a new project and tried to add the Misc. Utility and I get the does this plugin need an update error message.

It needs to be updated to ARC! I'll do it later tonight.

Thank you so much. My robot is speechless.

I can't use synthiam's backoffice to update the plugin.

you can download the plugin:
be5678f2-3db9-4c29-9aad-5d0f67227b9e.ezplugin.zip

remove ".zip" extension and then double click to install.

Thank you ptp I have it reinstalled and it works as expected.
Now if I can only remember how to phrase the Select statement correctly for MS Access.

RichardZ

As I said before it is working great, except I have run in to a snag. When a new query is run, it does not clear the data from the previous query.
So if my query returns less data then the previous query I have garbage left in the array.
doing a ClearVariables() before each query would work if I didn't mind losing all my other variable data, which is not an option. 
My current workaround is to always look at the reported Array size and just ignore the garbage data.