That is a neat goal! I took the lazy way and just added a plan button to make configuration forms.

Does there appear to be a big difference or advantage?

I have been working on a robot brain database and with this plugin I hope to have one variable to send the SQL query and a variable array for the returned data. Then do all the coding in ARC. One of the first things I am going to try is to put in the database an excel spreadsheet of data I have of music. This spreadsheet has every song that hit the top 100, how long it charted, who wrote it, the artist, flip side, album, ..... from 1890 to 2011. (The Whitburn project).

But not being a .net programmer, the work is slow. My programming skills came from my work with Z80 assembly language, GWBasic, VBScript and Arduino C++ and of course ARC.

But with the help of the Community and Google, I think I can do it.

Take a look at creating your own functions by extending EZ-Script capability.

Then you could have something like....

$id = 3
$x = QuerySingle("Select column from mytable where id = " + $id)

or return an array like..

$x = QueryArray("Select * from mytable")

Look at this example in the plugin tutorial: https://synthiam.com/Community/Tutorials/146/23

@Dj Oh I like that idea, time for another cup of java and go thru this tutorial. I might be in over my head (as I usually am) with this database brain idea. I currently have a script running populating the database with every word in the english language.

Thank you DJ, I was hoping you would post something.

Ok new plan, thank you again DJ. Plugin that adds 2 new EZ-Script commands. DSNConnect and QueryDB

# First parameter is the DSN name, the second is 1 = open, 0 = close
# Returns 1 if no error
$X = DsnConnect("DSN Name", 1)  

#Always returns an array
#Syntax depends on the database and DSN driver
$x = QueryDB("Select * from table where ...")

Thanks for writing this. Creating a database plugin is on my long list of things I will probably never get to, but there are so many potential uses for it.

Alan

@DJ Or anyone else who has the answer

Ok I think I am close but need a little help; Once the DSNConnect("DSNName", 1) is executed and I am ready to do a Query

How can I reference in my code the DSNName variable? Consider the following code. The line 5 from the bottom cn = new OdbcConnection("dsn=" + myDSN); The myDSN variable will not be defined. That parameter was supplied with the DSNConnect() command.

 // Check if the function is our function (SetColor)
            if (e.Name.Equals("DSNConnect", 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: DSNConnect(''DSN Name'', 1)");


                // Do something
                byte myDSN = Convert.ToString(e.Parameters[0]);
                byte myconstat = Convert.ToByte(e.Parameters[1]);
                if (e.Parameters[1].Equals(1))
                {

                    OdbcConnection cn;
                    cn = new OdbcConnection("dsn=" + myDSN);
                    cn.Open();

                    // Return something. Good idea to return TRUE if your function isn't meant to return anything
                    e.ReturnValue = true;
                }
                else
                {
                    OdbcConnection cn;
                    cn = new OdbcConnection("dsn=" + myDSN);
                    cn.Close();

                    // Return something. Good idea to return TRUE if your function isn't meant to return anything
                    e.ReturnValue = true;
                }
            }
            if (e.Name.Equals("QueryDB", StringComparison.InvariantCultureIgnoreCase))
            {
                string mySQL = Convert.ToString(e.Parameters[0]);
                OdbcCommand cmd;
                OdbcConnection cn;
                cn = new OdbcConnection("dsn=" + myDSN);
                cmd = new OdbcCommand(mySQL, cn);
                e.ReturnValue = true;
            }
            return;

thank you for the information for my final assignment

To solve my problem I have decided to go with just one command.

$X = DSNQuery("Query String", "DSN Name")

This command will open the DSN connection to the database, Send the query string and return it as an array to your variable and then close the DSN connection. This should make the process (and the programming) much simpler.

Well at least that is the new plan.

@plcdroid As for being your final project, it just might be, as the code I posted is not complete and does not work.

RichardZ

@richard,

The plcdroid post is SPAM!

The user registed today and add a link to a website.

Soon will add (edit the post) more spam links.

@ptp, I expected it was but figured it would not hurt to answer since I was posting anyway. but thanks for the confirmation.

Did you hit any wall. Do you need help with your plugin?

I think now that I am going with only a single command in the plugin, I can follow DJ's tutorial. I will let you know this evening when I rewrite the code. But not knowing C# and just using Google for find the code snippets there is a good chance I will need help so keep an eye on this thread. Thanks much, I will keep you posted on my progress.

@ptp,

Does this look correct?

 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]);
                OdbcCommand cmd;
                OdbcConnection cn;
                cn = new OdbcConnection("dsn=" + myDSN);
                cn.Open();
                cmd = new OdbcCommand(myQuery, cn);
                e.ReturnValue = cmd;
                cn.Close();


            }
            
            
                
            }
            return;

        }

@rz90208: You need more code.

e.ReturnValue = cmd;

You can't return a OdbcCommand.

In a few minutes I'll post some code.

I don't have visual studio in front of me but i think those might be Disposable objects, which means you can help the garbage collector by wrapping them in Using() statements like so...

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)) {

                  cn.Open();

                  using (var cmd = new OdbcCommand(myQuery, cn))
                    e.ReturnValue = cmd;

                  cn.Close();
                }


            }
            
            
                
            }
            return;

        }

Thank you DJ, I also think I cannot use the cmd to return my SQL response. And I think I need to use a loop to fill an array if I get more then one hit with the query. i need to slow down and think the process through.

Yeah - wait for ptp's response, he'll know. I'm not in front of visual studio to assist. He'll also need to check for a null if no records match and return a blank string

@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.

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:

$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

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.

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

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

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