Ez-db V.01 - Need Some Testers

 
#1

EDIT:
*************THIS PROJECT HAS BEEN ADDED TO EZ-AI******************


This has been updated to include the latest information about this tool.

EZ-DB is a utility that will allow EZ-Builder to store and retrieve data from a database. The current databases that are supported are MySQL and MSSQL. EZ-DB can be downloaded from this site.

EZ-DB Pre-Release

The location that you place the folder doesn't matter, as long as you can connect to your EZ-Builder application. Unzip the contents of the zip file (the EZ-DB folder) to any location that you would like to have as the applications home. I haven't tested yet to see if this can run on a different computer by using the EZBuilderIP setting in the config file.

CONFIGURING THE APPLICATION

The EZ-DB.exe.config file contains the configuration settings for this application. I have stored a copy of this file in the Backup folder for you, if you make a mistake and need to recover a working version.

This is the contents of this file. As you can see, you should be running .net 4.5. Going down the list under the <appSettings> section you will see the following

Code:


<?xml version="1.0" encoding="utf-8" ?>

<configuration>

<startup>

<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />

</startup>

<appSettings>

<add key="Server" value="localhost"/>

<add key="UserName" value="Robot"/>

<add key="Password" value="3Z-R0b0t"/>

<add key="Database" value="robot"/>

<add key="DBServerType" value="MSSQL"/>

<!--add key="DBServerType" value="MySQL"/-->

<add key="TableName" value="Variables"/>

<add key ="EZBuilderPort" value="6666"/>

<add key ="EZBuilderIP" value="127.0.0.1"/>

</appSettings>

</configuration>



Server - Name of the machine housing your database (localhost works if it is on the same computer)

UserName - The user name specified within your database server that has permissions to insert/update and read from the database. If you are going to use stored procedures, they will also need rights to execute the stored procs. Many people will just give this user DBO permissions over this database.

Password - The password for the specified user within your database server.

Database - The database that you will be storing to and querying data from.

DBServerType - There is one for MSSQL and one that is initially commented out for MySQL. Uncomment one and comment out the other. This is done by including the !-- before and -- after the xml element, but within the element starting and ending brackets. Example... <!--ALL OF THIS WILL BE COMMENTED OUT-->

TableName - This is the name of the table that you want to store your data in. Retrieval can be from anywhere on the database server, or using linked servers or replication could come from multiple servers.

EZBuilderPort - The port that the robot you are wanting to have communicate with the database is running on. This is not 23 or 24, but the port TCP port setting. See image below. If multiple robots are running, one of these will handle the database traffic for all robots for one machine.

EZBuilderIP - The IP address of the computer that EZ-Builder is running on.

After updating the EZ-DB.exe.Config file, save it and you will be ready to go from the application side.

USING WITH EZ-Builder

Some parameters have to be setup for this communication to work.

I add this to my initialization script to set these

Code:


#Starting variables for EZ-DB

$SqlReturn = ""

$SqlCommand = ""



To call the app and store data in the database, you would use a script component like this

Code:


Exec("C:\EZ-DB\EZ-DB.exe","$tempC Out Wall-E")


This will call the EZ-DB.exe application and pass some parameters to it.

The first parameter is the parameter that you want to store the value of. In this example, I am storing the $tempC variable from the EZ-Builder application into my database.

The second parameter must be In or Out. Notice the first letter is capitalized. Right now, it must be capitalized so it literally must be either In or Out. This is from the perspective of EZ-Builder. Are you passing something In to EZ-Builder or passing something Out of EZ-Builder? In this case, I am passing the variable Out to be stored in the database.

The third variable is the name of your robot. This will also be stored so that if you have multiple robots storing data in the same table, you will be able to identify which one sent the data.

To call the app to retrieve data from the database, you will need to first store your query in the
$SqlCommand variable and then call the application, passing in three parameters.

Code:


$SqlCommand = "SELECT AVG{CONVERT{INT,VariableValue}} FROM Variables WHERE VariableName = '$tempC' and RobotName = 'Wall-E' and DateEntered > CONVERT{VARCHAR{10},GETDATE{},101}"

EXEC("C:\EZ-DB\EZ-DB.exe", "$SqlCommand In Wall-E")


If you are familiar with any SQL language, you will realize that all of the ( have been replaced with { and all of the ) has been replaced with }. This is because you're variable in EZ-Builder would not be able to be set because of issues with having ( or ) inside of a variable value. EZ-DB converts { to ( and } to ) before the query is executed.

The result of the query will be passed back to the $SqlReturn variable in EZ-Builder. This can then be used to do whatever you want to do with it in EZ-Builder.

I have included a EZB project called EZ-DBExample.ezb in this folder. It might be helpful.

My email address is D.Cochran@CochranRobotics.com. If you have any questions, please let me know.



I will be adding more output data data to display information about what is going on with the app in the EZ-Builder panel. Messages like "connecting to database", "Getting Data", "Returning data to EZ-Builder" and stuff like that. I will probably make a configuration setting to enable or disable these messages.

I would appreciate more ideas or features that you might have.
User-inserted image

#2

Bug fixed. Let me know if you would be interested in testing. I will do the email thing to help you get it setup.

#3

I can, and would like to test, but probably no time until this weekend (also need to install sql server at home first, which means it is probably time for me to upgrade my home server, so may be a few days before I can start)

Alan@thetechguru.net

#4

sounds good. working out a couple of things still but it works from a command line. Now to get it able to be called from a script in EZ-Builder. Gotta be able to figure out how to pass parameters to an exe that require quotes to work. Hmm.

#5

Try hex code rather than characters to make up the filename for the exe in the exec() command. I've not tried it myself but it may be worth a go.

I'd offer to test it but am away for a long weekend and have mountains of work to get done before I go.

#6

good idea. Will give it a shot in the morning. I have got a meeting with the makerspace tonight. I am in no rush and figure that I will pass it along to anyone who wants it.

#7

Actually had some time. This works from command line -
ez-db.exe $tempC Out Wall-E

Now, how would I do this from a script. I have tried

Code:

Exec("C:\EZ-DB\EZ-DB.exe","$tempC Out Wall-E")



IDK, Will mess with it in the morning. If anyone has any suggestions, please let me know.

#8

Try this;

Code:


Exec("C:\EZ-DB\EZ-DB.exe" + 0x22 + $tempC + " Out Wall-E" + 0x22)



0x22 is the hex code for the ascii "
If you need the comma there too add in + 0x2C before the first 0x22

Use a + between variables to string them together

#9

got it working

#10

Code:

Exec("C:\EZ-DB\EZ-DB.exe","$tempC Out Wall-E")

works. dont know what I was doing wrong before. removed it and re-added it and it works.