Asked — Edited

Ez-Db V.01 - Need Some Testers

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 ARC 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 ARC 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 ARCIP 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


&lt;?xml version=&quot;1.0&quot; encoding=&quot;utf-8&quot; ?&gt;

&lt;configuration&gt;

    &lt;startup&gt;

        &lt;supportedRuntime version=&quot;v4.0&quot; sku=&quot;.NETFramework,Version=v4.5&quot; /&gt;

    &lt;/startup&gt;

  &lt;appSettings&gt;

    &lt;add key=&quot;Server&quot; value=&quot;localhost&quot;/&gt;

    &lt;add key=&quot;UserName&quot; value=&quot;Robot&quot;/&gt;

    &lt;add key=&quot;Password&quot; value=&quot;3Z-R0b0t&quot;/&gt;

    &lt;add key=&quot;Database&quot; value=&quot;robot&quot;/&gt;

    &lt;add key=&quot;DBServerType&quot; value=&quot;MSSQL&quot;/&gt;

    &lt;!--add key=&quot;DBServerType&quot; value=&quot;MySQL&quot;/--&gt;

    &lt;add key=&quot;TableName&quot; value=&quot;Variables&quot;/&gt;

    &lt;add key =&quot;EZBuilderPort&quot; value=&quot;6666&quot;/&gt;

    &lt;add key =&quot;EZBuilderIP&quot; value=&quot;127.0.0.1&quot;/&gt;

  &lt;/appSettings&gt;

&lt;/configuration&gt;

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 ARC 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 ARC

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

I add this to my initialization script to set these


#Starting variables for EZ-DB

$SqlReturn = &quot;&quot;

$SqlCommand = &quot;&quot;

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


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

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 ARC 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 ARC. Are you passing something In to ARC or passing something Out of ARC? 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.


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

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

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 ARC 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 ARC. This can then be used to do whatever you want to do with it in ARC.

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 ARC panel. Messages like "connecting to database", "Getting Data", "Returning data to ARC" 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


ARC Pro

Upgrade to ARC Pro

Get access to the latest features and updates before they're released. You'll have everything that's needed to unleash your robot's potential!

#1  

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

#2  

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

#3  

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 ARC. Gotta be able to figure out how to pass parameters to an exe that require quotes to work. Hmm.

United Kingdom
#4  

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.

#5  

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.

#6  

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

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

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

United Kingdom
#7  

Try this;


Exec(&quot;C:\EZ-DB\EZ-DB.exe&quot; + 0x22 + $tempC + &quot; Out Wall-E&quot; + 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  
Exec(&quot;C:\EZ-DB\EZ-DB.exe&quot;,&quot;$tempC Out Wall-E&quot;)

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

#10  

@d.cochran, This all looks very impressive exciting. I love the idea of people like you making something like EZ Robot even better and useful for more people. Thanks for all your hard work!

I have one question though; What will this do and how can someone like me use it for?

#11  

Basically, you would be able to store information from variables into a database.

say for example you wanted to make 10 robots work together all over the world. If each could connect to a database via the internet, they could communicate with each other and do things based on what other robots are doing.

another example might be Jarvis by Rich. Jarvis can control parts of Rich's home. It could get the outside temp and humidity from an RSS feed. He then could see what temp the thermostat has been set to in the past when the temp and humidity were similar to what it currently is.

another example would be to log moves in a game and be able to see what the next move normally is based on the move that was just made and the move you are about to make.

there are so many possibilities. I work with databases daily for a large bank. It is in my blood I guess. I really don't know yet exactly what I will do, but the thought of many robots being able to communicate with each other and make decisions based on what the other ones are doing (robotic vacuums or lawn mowers working together) interests me a lot.

#12  

Thanks for the explanations! That totally awesome. You just may be the creator and father of the Borg civilization. eek

United Kingdom
#13  

Just to point out though, the GetVar() and PushVar() commands will do the same thing once the cloud is set up for data storage/retrieval.

#14  

The beauty of a database is not just shared storage and retrieval of variables, but the ability to do complex queries for the variable retrieval.

Answering from my phone, so don't want to get too deep, but I'll try to come up with some example ideas later to demonstrate.

#15  

Right. Passing any getting variables is one thing, but being able to dig through a lot of data for the answer to a question in a second or two is where the power is.

say you store the points in a path that was taken, and the time it took to take that path. The next 30 days you took other random path and recorded the paths. You would then be able to query the database for the shortest or quickest path or create a different path based off of all of the gathered data.

decisions made off of data gathered is where this becomes powerful.

United Kingdom
#16  

You are right, which is why Jarvis logs everything.

For instance heating, hot water and external temperature

User-inserted image

Aside from a cool chart it also helps when work out when to turn on the heating to make sure it's warm when I get home. Knowing the information has saved me so much in energy bills.

But I digress...

#17  

@d.cochran.... obviously great work.... programming is definitely your thing... To me I would worry about this only being useful to a handful of guys on here like Alan, Rich, Doc and a few others of course.... Being a simpleton myself (more to the point, I have no real interest in hard core Windows stuff) ... I do however want to lean more about networking , WOL, WiFi and that kind of stuff... Your first post is basically greek to me... I have no idea what MSSQL or SQL (I'll go as far to guess it is a type of Windows database of some sort).... I used to program on a old Psion 3mx and 5mx computer (maybe Rich would remember the Psion computer..... made in England and absolutely brilliant) a while back, so I do know a thing or two about what a database is and what the terms relational and creating tables mean... But even if I really knew what it was I still would have no idea on implement or use it.... I am sure I could learn, but I would probably opt to use DJ's PushVar, PullVar cloud solution....

Still, I am glad we have guys like you on here to help us with Windows and other things outside of ARC...

Cheers Richard

#18  

That's awesome Rich!

Well, I got the bug that I was having worked out. I am now able to use a script to log data to a database table, and then run a query to retrieve data from the table. Amazing how simple this is in C#, but the issues came in when calling from a script in ARC. Getting the script to call the EZ-DB executable and pass a query as simple as "Select count(*) from Variables" was not easy. ARC doesn't like variables with "(" or ")" in them. So the work around was to put "{" and "}" in your query in place of "(" and ")" but then SQL Server doesn't like the query and some things were getting confused in the code when using "{" and "}" which C# uses. I couldn't use "[" and "]" because those are valid SQL characters. So, I had to use "{" and "}" in the variable in ARC, and have the executable catch and convert these to "(" and ")".

So far, all I have tested is some pretty simple queries, but the use of "(" and ")" is very high in SQL, so I had to get these working somehow.

I will type up some instructions on how to get it setup. It is a first attempt and I will put in the work to make it better as time goes on.

Feel free to contact me at d.cochran@cochranrobotics.com or on this post if you have any questions.

#19  

@Richard R

I agree that this might only be used by a couple of people which is why I didn't think that it was a good use of EZ-Robots time to make it. There are a few people that will think its really cool and use it, but most will have no reason to do so. Also, I wanted to have an example of how to create a component for EZ-B using C# for the class I will be teaching next year.

I hope some will use it beyond me, but if not, I will get use of it. I like to share like most in this community, and hope that something that I put out helps others.

Btw, my first computer was a TI-99/4a computer. My first program was written on that computer when I was in the 3rd grade. I have had a lot of exposure to many systems in my life, but have never heard of the Psion. I will have to go check that out.

#20  

.... Ha, Ha.... my first computer was a Commodore 64.... :)

#21  

Yep, the Commodore 64 rocked at the time. The Vic-20 and Commodore 64 were also owned us, along with the Adam (oops), Franklin Ace 1000, Apple II and Apple II e. I then bought my own computer. It was a 286 with a math co-processor so that I could do some CAD. Since then I have upgraded about every 2 years but still have many old machines running things like a NAS, Firewall, Web server, Email server, MSSQL Database server, DVR and media server, I had a Astrics server setup as a phone switch for a while, but got sick of land lines so that one is gone now.

Sad thing is that when someone asks me to work on their computer, I normally decline. I know myself and know that it would just sit for a while and nothing would be done on it. I don't mind taking care of my stuff, but hate being obligated to others to fix their stuff.

Okay, trip down computer memory lane... I always like those.

#22  

@Rich

Just added the ability to store in MySQL. I know that is the flavor you currently use. Will be testing and will have it available by this weekend. I am not a familiar with MySQL but I am sure it wont be any issue based on the limited amount of changes to this compared to MSSQL. In any event, no need for you to go download and install MSSQL if you are already using MySQL.

This is configurable in the configuration file.

#23  

Yep, MySQL is complete. Working on documentation and a sample EZB file. Will post it on my website for downloand and put the instructions out there for setup.

I will also include the scripts to create the table, but the table name configurable in the config file so you should probably change the config file and the script to have the name you want to use. By default it will be Variables

This only does one variable at a time right now. Will working on allowing it to pass more in the near future, but want to get this tested on a couple of other machines to get a base that I know works.

more to come..

#24  

EZ-DB v 0.01 for testing

There is a sample project, a readme.txt with instructions and some scripts to create the table in your database. The website has the current information on the download page. It is much easier to update. If you have any questions, email me at d.cochran@cochranrobotics.com

#25  

The download link on your website gives me a error?

#27  

Give it a shot now. dont know how that link got messed up but, it should be working now.

#29  

#d.cochran are you going to post your source code online so I can compile it myself?

#31  

Source code is now available.

EZ-DB Pre-Release source code.

I am working (slowly) on the ability to grab all variables and store them in database records. This is one of about 10 projects that I have going right now, so it might be a while.

#32  

Excellent. Just downloaded the project source code and ran it in VS2012.

#33  

I just put out EZ-DB Multi, which allows you to export an array of variables to a database. This can be downloaded from cochranrobotics.com and there is a sample project on the EZ-Cloud called db sample array.

I am just bored and needed something to keep my mind from pondering the 3d printer debate going on in my head for the past couple of months. Use it and abuse it. Works with MSSQL or MySQL. I dont have a piece in yet to return values from the database back to an array of variables. I didnt have a need for it yet, but when I do, I am sure this will be revisited. The instructions for EZ-DB setup should work fine for EZ-DB Multi. If you have any issues, let me know. I would definately download the project from EZ-Cloud to get you started.

#34  

EZ-DB has been replaced by EZ-DBMulti. There are some changes, but I think it is simpler to use. I will post the source code for it when I am finished with my current project and have it tested pretty well.

Current project uses EZ-Face to recognize the user of the robot if they are in the catalog of recognized users, and then searches the database using EZ-DBMulti for information about that user. Then in the interaction of the user with the robot, the robot does things like checking to see if it is the users birthday. If so, it wishes them a happy birthday.

So far, tests are good. I dont know other things that it will store in the database yet. maybe the last time that a nursery rhyme was told to a user and limit its retelling of the same rhyme based on the previous tells to that person, or something like that.

This robot is a Teddy Ruxpin that is being setup for my granddaughter. It will be used as a building platform for my inmoov to try to give it some "intelligence". A lot of the same things that I want to accomplish with the Ruxpin will be building blocks for the inmoov. Anyway, I will post on this topic again when I have had more time to test through this project.

#35  

Nice, have you thought about connecting it to Jarvis (link) project? for a AI Jarvis

#36  

There are a few services out there that I am looking at. Jarvis is one, Google has one, IBM has one. There are a few others. I am waiting for something to pan out more with them.

My son is doing a project with with a company that shall remain unnamed right now Voice Recognition and database/data cube querying for responses to questions. Its one of the large ones, but the project is just starting. I may be testing some of his code when it gets to that point:)

My granddaughter is only 1 and barely speaking right now, so I want to use this as a method of teaching through repetition. Things like "Your birthday is..." or "You are now x years old" and things like that. Its more about her specifically but she will have a younger brother in the next few month, so I decided to go ahead and make it database driven so that it could be used by more than one user without rewriting a lot of stuff.

Fun possibilities all the way around. Also, I help an Elementary school with Robotics. It is a really cool way to show what is possible. Having the Teddy put with a very young group of kids and it being able to interact with them differently based on who it is would be the ultimate goal.

#37  

@d.cochran Quick Question First please, which is better MSSQL or MySQL? I googled both, you got to pay for MSSQL and MySQL is free.

Thanks

#38  

Mssql express is free also. Currently ezdb only supports mssql and mssql express because I had to push one of them forward. I will be making ezdb work again with my sql but it's going to be a bit. There is a lot to add in the way of table design and stored procs.

If you download sql express I would also download sql management tools. It will give you a tool for making changes to the database.