Machsupport Forum

Mach Discussion => Mach4 General Discussion => Topic started by: KenSchnell on February 05, 2019, 06:42:23 PM

Title: Sending Info to a Database from a Script
Post by: KenSchnell on February 05, 2019, 06:42:23 PM
I am trying to send data to a data base using the Lua Script - one of the issues I have is I am not familiar with Mach4 or Lua . Can I do this another way ? My Gcode programmer has Gcode writen where he can call macros , I want to be able to have him call M150 with 3 maybe 4 parameters that I can use a stored procedure in my macro to store the 4 parameters he sends. I would also like to use Script to return a value to his GCode .

I tried installing Lua-luasql-mysql drivers with no success , I tried installing sqlite drivers with no success (it seems to want the source code .h files)  so I am going to try to install the odbc drivers .

Does anyone have any process or method whereby I can send data to a database via Mach4 while the GCode is running - because this is where his data is generated and I want to capture that. I do not want to get to hacky with it - would like something in KISS format.
Title: Re: Sending Info to a Database from a Script
Post by: joeaverage on February 05, 2019, 06:54:27 PM
Hi,
I'm not familiar with sending data to a database but I am familiar with having Lua scripts open files
to either read or write data. Would that work?

Craig
Title: Re: Sending Info to a Database from a Script
Post by: KenSchnell on February 05, 2019, 07:17:54 PM
I will take that if you have it ..

And thank you very much .. I can use a hotfolder and update from there - if you would like I could send back a copy to you.
Title: Re: Sending Info to a Database from a Script
Post by: joeaverage on February 05, 2019, 08:03:33 PM
Hi,
the simplest is an example.....most if not all of my file opening/closing has started life as an example which ships with Mach4.

Mach4Hobby/LuaExamples/ProbeToFile/M401.mcs

That provides a simple file dialog using wxLua to browse to either an existing file or create a new one.
Study it and see how it works. I've always struggled with wxLua but in recent weeks have done a bit more work on wxLua,
the wxWidgets reference manual and most recently wxFormBuilder. I may be able to help with some of the simplest of
questions.

Craig
Title: Re: Sending Info to a Database from a Script
Post by: KenSchnell on February 05, 2019, 08:47:22 PM
I don't have Mach4Hobby I do not see those folders LuaExamples or that file . Could you post that file for me here?

I will not be using wxLua that I know of ...at least for the moment - I intend to automate sending the data to the data base as the process runs ..without human intervention.

I have Mach4 folder but nothing like LuaExamples
Title: Re: Sending Info to a Database from a Script
Post by: joeaverage on February 05, 2019, 08:50:58 PM
Hi Ken,
there are some differing styles of syntax when dealing with files, I often get confused and end up writing faulty code.
This is the style of syntax that I now use which seems to work best, or at least consistently:

local fileHandle=io.open(pathfilename,"w+")       --This opens a file with the path and file name =pathfilename in append write mode.

I can now refer to that file (as a first class value) as fileHandle. thus to write a line to the file would look like this:

fileHandle:write(offset,'\n')                              --This appends the string offset to the open file

And these statements flush the buffer and close the file when I've finished writing to it:

fileHandle:flush()
fileHandle:close()

There is a somewhat simpler syntax, syntactic sugar, I think its called but I get better results using the longer more formal approach.

Craig
Title: Re: Sending Info to a Database from a Script
Post by: KenSchnell on February 05, 2019, 08:56:00 PM
Thank you Craig.

I like your meme in your signature - reminds me of a Clint Eastwood movie quote (at least I think it was). He was telling someone about what happened with his wife. He said His wife cheated with his best friend - so he shot her, and the man asked well what about your friend. Eastwood responds well he was my best friend.
Title: Re: Sending Info to a Database from a Script
Post by: joeaverage on February 05, 2019, 09:02:51 PM
Hi,
the example is:

Code: [Select]
function m401()
inst = mc.mcGetInstance();
    -- create the wxFrame window
    mainframe = wx.wxFrame( wx.NULL,          -- no parent
                        wx.wxID_ANY,          -- whatever for wxWindow ID
                        "DummyFrame", -- frame caption
                        wx.wxDefaultPosition, -- place the frame in default position
                        wx.wxDefaultSize,     -- default frame size
                        wx.wxDEFAULT_FRAME_STYLE ) -- use default frame styles

    -- create a panel in the frame
    panel = wx.wxPanel(mainframe, wx.wxID_ANY)--We are not going to show it but we need to have this to use the File dialog

local file = wx.wxFileDialog(panel, "Select Probe File", "", "", "Text files (*.txt)|*.txt|Tap files (*.tap)|*.tap",
                             wx.wxFD_SAVE,wx.wxDefaultPosition,wx.wxDefaultSize, "File Dialog" );
        if(file:ShowModal() == wx.wxID_OK)then
            local path = file:GetPath()   
--wx.wxMessageBox(tostring(path))
--[[
Set the output of the probe points with the format String
Example:
X%.3AXIS_X
will output X<xprobevalue>
]]--
mc.mcCntlProbeFileOpen(inst, path, "X%.3AXIS_X Y%.3AXIS_Y Z%.3AXIS_Z A%.3AXIS_A\r\n", true);
        end

end

if (mc.mcInEditor() == 1) then
dofile ("load_modules.mcs")
    m401()
end

Hi you may not be particularly interested in wxLua but it is in fact an integral part of Mach, the GUI especially.
In this instance you will have m150 in your Gcode job and a function would run which among other things opens
a file to store results. You could have it run automatically but it would save to the same file on each and every occasion.
You could also have it run with this dialog so that you could choose the directory and filename of the file and the dialog
would dismiss itself thereafter for the remainder of the session.

One thing is almost certain that when you start manipulating files or databases I would guess then having wxLua dialogs
to chose the correct files and/or directories is going to be mandatory.

Craig
Title: Re: Sending Info to a Database from a Script
Post by: KenSchnell on February 05, 2019, 09:35:08 PM
I have an Automated process that will run operator less continually hours on end - the process as it runs will need to update the database each and every time it finishes an item.

wx Components are usually gui elements as in python and they require operator intervention.

The file name I don't need to be concerned with - I can name the files randomly as long as I use the same extension. 


'One thing is almost certain that when you start manipulating files or databases I would guess then having wxLua dialogs
to chose the correct files and/or directories is going to be mandatory.'

No this is not accurate - typically I write code in .NET and other languages - however when recording data in a database it just involves passing the right parameters to the sql server or even a data file. Storing in a properly structured database is somewhat as easy as your code - if I could get LuaSql driver to install. basically like this:
connection.Open()
connection.parameters = x,y,z ,
connection.query ="myquery"
connection.execute
connection.close()

I do not write in LUA - first time working with it, and Mach4 I am very new to it as well - so this is a learning curve..


Title: Re: Sending Info to a Database from a Script
Post by: joeaverage on February 05, 2019, 10:00:32 PM
Hi,
well I suspect you will have no trouble with Lua, it is deceptively simple. The syntax is "syntax that only a mother could
love' but the rest of it is fine.

I have this open when I am coding:

https://www.lua.org/manual/5.2/ (https://www.lua.org/manual/5.2/)

Smurph tells us that the most recent builds of Mach now use Lua 5.3, there are a few subtle differences.

Craig
Title: Re: Sending Info to a Database from a Script
Post by: smurph on February 07, 2019, 08:43:36 PM
wxLua is LUA with bindings to the wxWidgets library.  You don't necessarily HAVE to use the wxWidgets bindings.  Just don't write code that uses anything with the "wx." prefix. 

The database should be doable.  We provide luasql for mySQL and ODBC.  However, neither will won't work by itself!  Database client software also needs to be installed.  For instance, if you installed an IBM DB2 client, it would install the ODBC driver.  For mySQL, the client files need to be installed on the machine, namely "libmysql.dll".  And these DLLs need to be in the PATH as well so that the luasql driver can find them.  Or copy them into the Mach4 installation folder.  But it is best to put their orignal location in the PATH environment variable though.

Attached is an example file from https://keplerproject.github.io/luasql/examples.html (https://keplerproject.github.io/luasql/examples.html) that I munged up to include our module paths.  It assumes that there is a database called "luasql-test" available that requires no password to access and alter.  I set one up and the script ran flawlessly. 

A mySQL database can also be accessed via ODBC with the following: https://dev.mysql.com/downloads/connector/odbc/ (https://dev.mysql.com/downloads/connector/odbc/)  Install that and configure.  Then change the mysql specific stuff in that example file from myslq to odbc (lines 5 and 7). 

Steve
Title: Re: Sending Info to a Database from a Script
Post by: KenSchnell on February 08, 2019, 02:47:01 PM
Thank you Smurph.

understood wx is Graphical - like wxWidgets python etc

Yes I have Installed MySQL 8.0 , We installed all drivers except the c++ library ; since I will also access this database with .NET. and was not sure how Lua would connect - being new to mach4 I saw the python folders and said better to have more installed than less - while I try to make this work.

Because LuaSQL via Luarocks was not installing - maybe I did not need to do that ...
We went to the odbc driver for a MySQL connection and set up the connection in the windows ODBC connection.


I will double check to see if MySQL installer placed them in the %path% variable but I think that is correct - I try to point to the original rather than a copy located elsewhere - as upgrades may / will require that specific version. Just easier.


" I set one up and the script ran flawlessly. "
Did you run the script from with in Mach4 ? as a Macro or regular .LUA script  ?

I intend to call this with a MCode from a GCode script that our machinist wrote .

I meant to ask about this line of code
package.path = package.path .. ";.\\Modules\\?.lua;"

It seems to be a generic folder  where we point to .Lua files could you give a quick what it is , and that syntax represents ??



 
Title: Re: Sending Info to a Database from a Script
Post by: smurph on February 08, 2019, 03:48:53 PM
Because LuaSQL via Luarocks was not installing - maybe I did not need to do that ...
We went to the odbc driver for a MySQL connection and set up the connection in the windows ODBC connection.

We provide the luasql modules in the Mach installation forlder's "Modules" directory.  Luarocks isn't needed or desired unless you have a separate LUA installation that you also want to have lausql connectivity. 

I will double check to see if MySQL installer placed them in the %path% variable but I think that is correct - I try to point to the original rather than a copy located elsewhere - as upgrades may / will require that specific version. Just easier.

The MySQL installer didn't put ANYTHING in my PATH.  I had to add it myself.  My installation is in C:\MySql, so what I added to the PATH environment variable was "C:\MySQL\lib", as that is where the client DLL files were.  I also added "C:\MySQL\bin" for grins. 

" I set one up and the script ran flawlessly. "
Did you run the script from with in Mach4 ? as a Macro or regular .LUA script  ?

I ran the script from the Zerobrane editor.  Mach running, do the menu path Operator -> Open Script Editor.  Then open the file from my last post.  You can then step through the program line by line.  Hit F5 to start the program.  It will pause on the first line, then hit F10 to advance to the next line.  This is called single stepping.  Anyway, when it gets to the line where it "requires" the module step past that.  If you get an error there then that means luasql can't find the libmysql.dll dependency.  The error message is misleading.  It says that lua can't find luasql\mysql.dll in the specified directory.  However, if you look in that directory, it is indeed there.  So yes, that is misleading.  But that is more of a Windows thing than a LUA thing. 

I intend to call this with a MCode from a GCode script that our machinist wrote .

That will work.  I ran it from within the editor to test it.  But that code could be put into a macro script and it would be fine. 

I meant to ask about this line of code
package.path = package.path .. ";.\\Modules\\?.lua;"

It seems to be a generic folder  where we point to .Lua files could you give a quick what it is , and that syntax represents ??

Well.  those are the lines I added to the example on the luasql site.  That is the LUA equivalent of the PATH environment variable (package.path and package.cpath).  They are search directories.  So what those lines do is add our Modules directory to the directories that are searched when we try and load a module.  So lets examine the string that we appended:

";.\\Modules\\?.lua;"

";" is a path separator, just like what is used in the PATH environment variable. 
"." means "current directory".  This is used when we provide a relative path.  An absolute path could be used as well, such as "C:\\Mach4Hobby\\Modules\\?.lua".  But the relative path is used because we always have the Mach installation directory as the current working directory when launching scripts. 
"\\Modules\\" is the part of the path that points to the Modules directory.
"?.lua;" specifies the valid file extention. 

A lot of times you might do something like this:

package.path = package.path .. ";.\\Modules\\?.lua;.\\Modules\\?.mcs"

This will look for files with *.lua and *.mcs extentions. 

package.cpath will always need to look for *.dll files on Windows (*.so files on Linux). 

Anyway, look in your Mach installation's Modules directory to see what all we provide for extending LUA.  There are modules like luars232 for serial comms and luasockets for network comms, etc...

Steve
Title: Re: Sending Info to a Database from a Script
Post by: KenSchnell on February 08, 2019, 06:00:40 PM
 :)AWESOME :) Thank you Smurph - this should now be plenty to get us going .. my first experience with Mach4, and Lua ...

Thanks for the explanation of the path variable..
Title: Re: Sending Info to a Database from a Script
Post by: KenSchnell on February 11, 2019, 05:12:46 PM
When running the code below from the Zerobrane Studio:

package.path = package.path .. ";.\\Modules\\?.lua;.\\Modules\\?.mcs"

-- mysql.dll and odbc.dll are in the addons path so I included a direct reference search path of AddOns

package.cpath = package.cpath .. ";.\\Modules\\?.dll;.\\Modules\\AddOns\\?.dll"

-- load driver
local driver = require "luasql.odbc"
-- if I run this in the mcLUAEditor - debug the script exits with exit code -1 (I assume that means it could not find the driver
-- my macros are located here  ..       C:\Mach4\Profiles\Mill\Macros

-- create environment object
env = assert (driver.odbc())
-- connect to data source
con = assert (env:connect("mycnc_v1"))
-- reset our table


  res = assert (con:execute(string.format([[
    INSERT INTO operator
    VALUES ('Me', 'Myself')]], FirstName, LastName)
  ))

 
con:close()
env:close()



I get the Error output :

Program starting as '"C:\Mach4\ZeroBraneStudio\bin\lua52.exe" -e "io.stdout:setvbuf('no')" "C:\Users\MACHMO~1\AppData\Local\Temp\.3F36.tmp"'.
Program 'lua52.exe' started in 'C:\Mach4\Profiles\Mill\Macros' (pid: 3588).
C:\Mach4\ZeroBraneStudio\bin\lua52.exe: C:\Users\MACHMO~1\AppData\Local\Temp\.3F36.tmp:1: module 'machipc' not found:
   no field package.preload['machipc']
   no file 'C:\Mach4\Modules/machipc.lua'
   no file 'C:\Mach4\ZeroBraneStudio\bin\lua\machipc.lua'
   no file 'C:\Mach4\ZeroBraneStudio\bin\lua\machipc\init.lua'
   no file 'C:\Mach4\ZeroBraneStudio\bin\machipc.lua'
   no file 'C:\Mach4\ZeroBraneStudio\bin\machipc\init.lua'
   no file '.\machipc.lua'
   no file './machipc.lua'
   no file './machipc/init.lua'
   no file './lua/machipc.lua'
   no file './lua/machipc/init.lua'
   no file 'C:\Mach4\ZeroBraneStudio\lualibs/machipc/machipc.lua'
   no file 'C:\Mach4\ZeroBraneStudio\lualibs/machipc.lua'
   no file 'C:\Mach4\ZeroBraneStudio\lualibs/machipc/machipc/init.lua'
   no file 'C:\Mach4\ZeroBraneStudio\lualibs/machipc/init.lua'
   no file 'C:\Mach4\Modules/machipc.dll'
   no file 'C:\Mach4\ZeroBraneStudio\bin/clibs52/machipc.dll'
stack traceback:
   [C]: in function 'require'
   C:\Users\MACHMO~1\AppData\Local\Temp\.3F36.tmp:1: in main chunk
   [C]: in ?
Program completed in 0.19 seconds (pid: 3588).
Title: Re: Sending Info to a Database from a Script
Post by: smurph on February 12, 2019, 12:38:19 AM
What version (build) of Mach are you running?  The luasql module have never been in the addons directory to my knowledge. 

Should be:
C:\<machDir>\Modules\luasql\myslq.dll
and
C:\<machDir>\Modules\luasql\odbc.dll

Steve
Title: Re: Sending Info to a Database from a Script
Post by: KenSchnell on February 12, 2019, 09:52:14 AM
We have Mach 4.2.0.3804 Build 3804

I do not have a luasql directory ..

How can I install that ?

Can you zip that up and I can then install it ? - I assume luasql does not need to be registered in windows in order for it to work.
Title: Re: Sending Info to a Database from a Script
Post by: smurph on February 14, 2019, 12:33:15 AM
Well...  I guess I put all of those modules in after 3804!  :(  You could try one of the development versions from the FTP site.  4088 is the most recent.  And by all accounts it is looking pretty good. 

Steve
Title: Re: Sending Info to a Database from a Script
Post by: KenSchnell on February 14, 2019, 08:01:42 AM
Thank you.. I have downloaded the latest - I was checking the change log and 3139 - 3113 LuaSQL was added - however mine is 3804 and does not have it - so maybe it missed the files/folders inclusion in the build process. Anyway I have downloaded the Latest and Greatest .. hoping I can simply move the folders directory and do what I need to do as the current system is running -  - and I do not want to 'fix' it to broken. We will see how it performs thanks again! :)
Title: Re: Sending Info to a Database from a Script
Post by: KenSchnell on February 14, 2019, 04:35:11 PM
Hello - Smurph ..
Having an issue still - I tried my code and received an error - the module could not be found.
So I put your code in with no changes and I received the same result.
 I skipped the dynamics and set the path physically C:\Mach4Industrial\Modules\?.dll , also the same for the package.path  except ?.lua at the end
I have had the same results same error - the module is there in the path - the 4.20.0.4088 installer placed two files in the luasql folder mysql.dll and odbc.dll
When I try the odbc driver I get procedure not found. I tried running these form both mcLuaEditor and zerobrane with the exact same errors - respectively.

I checked folder permissions and so I set them for everyone - I am running zerobrane and mcluaeditor (Run As Administrator)  and I am still not passed this point. On my development machine I am running in demo mode, should not matter - but then again maybe..

Debugging session started in 'C:\Mach4Industrial\'.
error loading module 'luasql.mysql' from file 'C:\Mach4Industrial\Modules/luasql\mysql.dll':
   The specified module could not be found.
Title: Re: Sending Info to a Database from a Script
Post by: smurph on February 14, 2019, 05:12:58 PM
That means that the luasql/mysql.dll can't find the libmysql.dll client file.  This goes back to putting the mySql dll files into the PATH environment variables.  Or, you can drop the libmysql.dll into the Mach directory. (Attached is my libmysql.dll file).

Then Start Mach.  Next, open the Zerobrane editor with the menu path "Operator -> Open Script Editor" and load the file I a previously attached.  You should be able to step through the code.

Steve



Title: Re: Sending Info to a Database from a Script
Post by: smurph on February 14, 2019, 11:48:55 PM
I did a video that maybe explains things. 

In the video section of this forum:  https://www.machsupport.com/forum/index.php?topic=39466.0 (https://www.machsupport.com/forum/index.php?topic=39466.0)

or direct to youtube:  https://www.youtube.com/watch?v=BtzPPv1WNZQ (https://www.youtube.com/watch?v=BtzPPv1WNZQ)

Steve

Title: Re: Sending Info to a Database from a Script
Post by: KenSchnell on February 15, 2019, 12:45:42 PM
Hello Smurph and Thank you!!

yes using the code you provided and the NEW installation of Mach4 4.20.0.4088 on my development box (forgot to put the path in the environment on the development box - just as you stated ..aaargh)  I was able to successfully step through the code.

I have the customer box setup in a VM [So I can do my testing with it - without messing them up) it is windows 7 embedded (ugh).

I added to the path environment and also added the luasql folder with its files (from .4088 build)  to the Mach4\Modules folder - so I have Mach4\Modules\luasql\
While I have added the libmysql.dll to the path I had also copied it to the Mach4 directory to be sure. This so far has not worked.  Same Errors.
So then I copied the root of the .4088 build (with lua53.dll) to my Mach4 3804 build folder - without overwriting the existing installation files - this produced a different error c0000005 (which seems to be a memory access violation error). So that tells me the driver has been located at this point and that some other file is an issue.


Do I need to upgrade the Mach4 software in order to accomplish this  - or is there some other folder / files I need to copy over in order for it to function?

I'am trying to avoid the upgrade route because MachMotion has their files loaded into it as well, and unfortunately what I am doing is also not my favorite route as I am doing a franken-version - but I am more willing to do this as I think I will be less prone to have something else in this application break.

 
Title: Re: Sending Info to a Database from a Script
Post by: smurph on February 15, 2019, 01:27:34 PM
You can't use the Lua module files from the 4088 build in the 3804 build.  The reason is that the 4088 uses Lua 5.3 where 3804 uses Lua 5.2.  Copying the lua53.dll file into the 3804 directory didn't do much other than let the 5.3 module go a step further.  However, the Mach GUI and core are still Lua 5.2, which is causing the 0xc0000005 exception. 

So yes, you need to upgrade the Mach 4 software on the target machine to use the Lua 5.3 modules from 4088.  However, knowing that it is a Mach Motion Win 7 Embedded control, I would not do that.  It will break a TON of their screen scripts.  So I went looking for Lau 5.2 versions of the luasql modules and found them in a later build.  They are attached.

Steve

Title: Re: Sending Info to a Database from a Script
Post by: KenSchnell on February 16, 2019, 02:49:04 AM
The mcLuaEditor will successfully run the code but zerobranestudio will not - it wants machipc.dll, I copied that file in to the Mach4 directory using the machipc.dll from the 4088 build and when I do that - zerobranestudio says mclua52.exe crashed.

using mcluaeditor I can insert into my database - even using the MySQL 8 Server with a username and password  (I have the 32bit  c connector installed and have it in the path).

My only issue now seems to be the zerobranestudio crash.
Title: Re: Sending Info to a Database from a Script
Post by: KenSchnell on February 16, 2019, 04:16:26 AM
In my zerobrane studio I only have an issue if I have the Mach4 interpreter selected - found that in the preference.. so I need to find out about the interpreter and if I am pointed to he wrong one ... even with Mach4 .

Will my code still work if called from a Gcode file ?
Title: Re: Sending Info to a Database from a Script
Post by: smurph on February 16, 2019, 06:37:05 PM
Mach Motion may not have included those files in their installation.  Attached are the complete set of Lua5.2 modules from 3804.  Don't overwrite your existing luasql thought, as the files you have now are newer.  Just get the modules you don't have. 

Use the Mach4 interpreter in ZeroBrane.  Always launch the Zerobrane editor from the Mach menus.  Otherwise, the paths will not be correct and it will not find the modules (machipc.dll, etc...).

As to if they will the run in the G code, yes.  Build a regular M code macro and put your DB code in there and it will be fine. 

Steve