PDA

View Full Version : MP277, interactive databasing with csv file's


Combo
March 12th, 2009, 09:25 AM
Okay, in an other topic I was searching for saving and fetching data out of a database. I was doing it in a PLC, but it eated all the retentive memory of the CPU.

So the idea is writing to csv and reading from it.

I have 5 correctionparameters that I save and retrieve. I write them on a buttonevent and read them when the productcode changes.


This is the write script:

Dim f, fs, file_path, file_name, file_name_path
On Error Resume Next
Set fs = CreateObject("filectl.filesystem")
Set f = CreateObject("filectl.file")
file_path= "\Storage Card MMC\"
file_name= CStr(SmartTags("ACTIVE BATCH.ORDER_DATABASE.ARTIKEL_NUMMER"))& ".csv"
file_name_path= file_path & file_name
If Err.Number <> 0 Then
ShowSystemAlarm "Error#" & CStr(Err.Number)&""& Err.Description
Err.Clear
Exit Sub
End If
f.open (file_name_path), 2
f.Lineprint (SmartTags("INSTELLING PONSUNIT.CORRECTIES.LINKS.ECS") & ";")
f.Lineprint (SmartTags("INSTELLING PONSUNIT.CORRECTIES.LINKS.HOEK") & ";")
f.Lineprint (SmartTags("INSTELLING PONSUNIT.CORRECTIES.LINKS.HOOGTE") & ";")
f.Lineprint (SmartTags("INSTELLING PONSUNIT.CORRECTIES.RECHTS.ECS") & ";")
f.Lineprint (SmartTags("INSTELLING PONSUNIT.CORRECTIES.RECHTS.HOEK") & ";")
f.Close

Set f = Nothing
Set fs = Nothing

If Err.Number <> 0 Then
ShowSystemAlarm "Error#" & CStr(Err.Number)&""& Err.Description
Err.Clear
Exit Sub
End If
ShowSystemAlarm "Corrections storage was successful"

So I print 5 line's in a csv, the csv is named after the product (artikelnummer). This is done for retrieving easely.


This is the read script:

Dim f, fs, file_path, file_name, file_name_path, DataSet,MyZf, field, HiField, i, j
On Error Resume Next
Set fs = CreateObject("filectl.filesystem")
Set f = CreateObject("filectl.file")
file_path= "\Storage Card MMC\"
file_name= CStr(SmartTags("ACTIVE BATCH.ORDER_DATABASE.ARTIKEL_NUMMER"))& ".csv"
file_name_path= file_path & file_name
If Err.Number <> 0 Then
ShowSystemAlarm "Error#" & CStr(Err.Number)&""& Err.Description
Err.Clear
Exit Sub
End If
If fs.dir(file_name_path)="" Then
SmartTags("INSTELLING PONSUNIT.CORRECTIES.LINKS.ECS") = 0
SmartTags("INSTELLING PONSUNIT.CORRECTIES.LINKS.HOEK") = 0
SmartTags("INSTELLING PONSUNIT.CORRECTIES.LINKS.HOOGTE") = 0
SmartTags("INSTELLING PONSUNIT.CORRECTIES.RECHTS.ECS") = 0
SmartTags("INSTELLING PONSUNIT.CORRECTIES.RECHTS.HOEK") = 0
Else
Do While f.eof=False
MyZf=f.lineinputstring
field=Split(MyZf,";")
For i= 0 To 4
field(i)= Replace(field(i),"","")
HiField(j,i)=field(i)
Next
SmartTags("INSTELLING PONSUNIT.CORRECTIES.LINKS.ECS") = HiField(0,1)
SmartTags("INSTELLING PONSUNIT.CORRECTIES.LINKS.HOEK") = HiField(1,1)
SmartTags("INSTELLING PONSUNIT.CORRECTIES.LINKS.HOOGTE") = HiField(2,1)
SmartTags("INSTELLING PONSUNIT.CORRECTIES.RECHTS.ECS") = HiField(3,1)
SmartTags("INSTELLING PONSUNIT.CORRECTIES.RECHTS.HOEK") = HiField(4,1)
j=j+1
Loop
f.close
End If
Set f = Nothing
Set fs = Nothing

If Err.Number <> 0 Then
ShowSystemAlarm "Error#" & CStr(Err.Number)&""& Err.Description
Err.Clear
Exit Sub
End If
ShowSystemAlarm "Corrections fetching was successful"


This one doesn't work...
Any ideas please ?

Thanks is forward.

Combo
March 12th, 2009, 10:16 AM
BTW: I never saw a message in the alarm window while system alarms should be shown.

I think this is on a wrong place:
If Err.Number <> 0 Then
ShowSystemAlarm "Error#" & CStr(Err.Number)&""& Err.Description
Err.Clear
Exit Sub
End If

And the this is never shown: ShowSystemAlarm "Corrections fetching was successful"

Same for the save, even while it does save...

Combo
March 13th, 2009, 03:41 AM
Okay,

I know a little more now.

Saving works. Systemmessage is shown also
Fetching when the file doesn't exsists sets every value on zero and systemmessage is shown.

But, when fetching, and file exsists, nog systemmessage, none of the scripts is executed anymore, it's a complete scriptstall that I get. I guess there is something not right in the loop...

any help please ?



BTW: I never saw a message in the alarm window while system alarms should be shown.

I think this is on a wrong place:
If Err.Number <> 0 Then
ShowSystemAlarm "Error#" & CStr(Err.Number)&""& Err.Description
Err.Clear
Exit Sub
End If

And the this is never shown: ShowSystemAlarm "Corrections fetching was successful"

Same for the save, even while it does save...

Combo
March 13th, 2009, 03:46 AM
Store example

9771

JesperMP
March 13th, 2009, 04:52 AM
I dont have the time to scan through all your code.

But, isn't what you are trying to do the same as the standard recipe system in WinCC Flex ?
I would definitely use the canned function in stead of rolling my own.

Combo
March 13th, 2009, 05:24 AM
Not it's not the same. Import and export can do the same, I agree. But you cannot say= now activate recipe xxxx.

A recipe that is made can not be recalled automatically.


I need to fetch the data automatically from the csv file's.

the problem is in this section:
Else
Do While f.eof=False
MyZf=f.lineinputstring
field=Split(MyZf,";")
For i= 0 To 4
field(i)= Replace(field(i),"","")
HiField(j,i)=field(i)
Next
SmartTags("INSTELLING PONSUNIT.CORRECTIES.LINKS.ECS") = HiField(0,1)
SmartTags("INSTELLING PONSUNIT.CORRECTIES.LINKS.HOEK") = HiField(1,1)
SmartTags("INSTELLING PONSUNIT.CORRECTIES.LINKS.HOOGTE") = HiField(2,1)
SmartTags("INSTELLING PONSUNIT.CORRECTIES.RECHTS.ECS") = HiField(3,1)
SmartTags("INSTELLING PONSUNIT.CORRECTIES.RECHTS.HOEK") = HiField(4,1)
j=j+1
Loop


I rewrote it and now have this in place:

Else
For i=0 tor 4
HiField(i,0)==f.lineinputstring
SmartTags("INSTELLING PONSUNIT.CORRECTIES.LINKS.ECS") = HiField(i,0)
SmartTags("INSTELLING PONSUNIT.CORRECTIES.LINKS.HOEK") = HiField(i,0)
SmartTags("INSTELLING PONSUNIT.CORRECTIES.LINKS.HOOGTE") = HiField(i,0)
SmartTags("INSTELLING PONSUNIT.CORRECTIES.RECHTS.ECS") = HiField(i,0)
SmartTags("INSTELLING PONSUNIT.CORRECTIES.RECHTS.HOEK") = HiField(i,0)
Next


I'm trying to read 5 values in column 1, allways row zero.

I get: type mismatch now


I dont have the time to scan through all your code.

But, isn't what you are trying to do the same as the standard recipe system in WinCC Flex ?
I would definitely use the canned function in stead of rolling my own.

JesperMP
March 13th, 2009, 05:50 AM
But you cannot say= now activate recipe xxxx.
A recipe that is made can not be recalled automatically. I dont understand what you say there. But if you mean that the PLC cannot interact with the recipes without user interaction, then I think that it is possible.
There are functions such as GetDataRecordFromPLC, and SetDataRecordToPLC, and these can be set to be triggered from the PLC.
However, I have never used the recipe system myself, so I dont know if there is a catch somewhere.

Combo
March 13th, 2009, 06:06 AM
when you make a new record in a recipesystem, and you had 5 records allready, then it will be record 6. When you've given a 'product number' as recordname, then it's not possible to recall the record by this 'product number'. Another problem is that you can have 300 records I think / recipe. That's way too less.





I dont understand what you say there. But if you mean that the PLC cannot interact with the recipes without user interaction, then I think that it is possible.
There are functions such as GetDataRecordFromPLC, and SetDataRecordToPLC, and these can be set to be triggered from the PLC.
However, I have never used the recipe system myself, so I dont know if there is a catch somewhere.

JesperMP
March 13th, 2009, 06:44 AM
When you've given a 'product number' as recordname, then it's not possible to recall the record by this 'product number'.This part you would have to do in a script. However it should be simple to loop through all recipes to search for the matching number.

Another problem is that you can have 300 records I think / recipe. That's way too less.I see, yes your requirements are a bit high. There is reserved 64kB for recipes on an MP277. You probably exceed that. On a PC RT there is not this limit. You could switch to a panel PC if you need to.

Combo
March 13th, 2009, 07:17 AM
If I understand well, you would export and import recipes and get the right record...

For example: they are running product x for customer y.

Then I could build recipes / customer and the records are products.

If they activate a product, I could look for the right import and the right record. It's not very easy if u ask me



This part you would have to do in a script. However it should be simple to loop through all recipes to search for the matching number.

I see, yes your requirements are a bit high. There is reserved 64kB for recipes on an MP277. You probably exceed that. On a PC RT there is not this limit. You could switch to a panel PC if you need to.

JesperMP
March 13th, 2009, 08:11 AM
I would use the LoadDataRecord function.
I just notice now that you can specify the record to load by number or by name. So you dont have to search through all records by the number.

To me it looks as you can do everything with the system functions. It makes sense.

Combo
March 13th, 2009, 08:38 AM
okay,

I agree, I just checked too, and you're right, it is possible to call a record by name.

But, I'm limitated to 500 records in an MP277... that's way to less, they want at least 10000 products


64K integrated Flash, but it says expandable ?

My MMC is 1G



I would use the LoadDataRecord function.
I just notice now that you can specify the record to load by number or by name. So you dont have to search through all records by the number.

To me it looks as you can do everything with the system functions. It makes sense.

JesperMP
March 13th, 2009, 08:45 AM
10000 products ! And you say 'at least' !

You have to change your strategy here. Forget about doing this in a "flat" file. You will never get a reliable system that way. And you will spend way too much time with it.
You need to have an SQL database for that many records.
I just tried FactorySQL from Inductive Automation. I is not cheap, but very easy to use. So I recommend it. There are other solutions too.

Combo
March 13th, 2009, 08:53 AM
In the office I have orders / customer. An order is a recipe with several products. I import and export recipes with buttons todo this. This works fine. I hope I can keep this in the office side ? These are flat file's too and works fine...


For saving many many productcorrecttions on MMC or networklocation, isn't this the same, if it works for one, why not for all ?


hmm... I cannot buy other things, if the corrections cannot be linked without extra tools, then it will be without automatic corrections. I tried in the PLC too, but this takes all the retentive memory.


You will never get a reliable system === why not ?

10000 products ! And you say 'at least' !

You have to change your strategy here. Forget about doing this in a "flat" file. You will never get a reliable system that way. And you will spend way too much time with it.
You need to have an SQL database for that many records.
I just tried FactorySQL from Inductive Automation. I is not cheap, but very easy to use. So I recommend it. There are other solutions too.

JesperMP
March 13th, 2009, 09:07 AM
Flat files with 10000+ records ?
Even in an office environment I dont like this very much.
What software are you using in the office to manage this ?

Combo
March 13th, 2009, 09:13 AM
For the orders I use wincc flex...

Each order has products (records) not much. the orders (recipes) can be saved (exported to csv) and fetched (imported from csv).

All in wincc flex. This works fine. If they doe 10 orders / day (is not much) then you get 3650 flat file recipes stored in csv format. Can this be a problem ? Making folders is possible in vb-script if too many files in folder.

For the corrections, same logic, but even more then the orders I think.



Ow, now I see, nonooo, not the flat file's have so many records !!! I'm building the corrections a little different. Each correction (record) = a flat file, while the ordersystem in the office works with the recipes form wincc flex.





Flat files with 10000+ records ?
Even in an office environment I dont like this very much.
What software are you using in the office to manage this ?

JesperMP
March 13th, 2009, 09:18 AM
If it works for you, then fine.
But I would not do it that way.

Combo
March 13th, 2009, 09:19 AM
I do not have many options, they need such thing and I cannot spend more money on databasetools.

But do you think it will be a problem to work with flat files, have you bad experienced with this or so ???

Why not this way if it can be free

the only thing I can't test is, 10000 csv's in a dir, how windows ce is going to react, will it load the csv I'm asking, will it have troubles... my only concern at the moment


If it works for you, then fine.
But I would not do it that way.

JesperMP
March 13th, 2009, 09:38 AM
You are not really explaining how it actually works.
In case Excel is used, then the limit is 65,536 rows by 256 columns.
Even so, it is easy to goof up, make double entries and so forth.
And what if 2 persons must work on the data at the same time ?
If this is something that integrates into the production ordering system of a medum-sized factory, it shouldnt be taken lightly. Whoever that specify such requirements must expect to pay what it costs.
With a home-grown system, the costs for creating and maintaining the application must be factored in. So maybe it isnt cheaper at all in the long run.
For example, FactorySQL costs 2000 USD. Thats about 1 week engineering time.
My stance on VB scripts and this is that it should be kept to a minimum, and should definitely not be used for stuff that is critical.

Combo
March 13th, 2009, 10:01 AM
I'll send you the project in private, if you have the time, then take a look. Download all in a PLC. Run the PC_Runtime. There you can make products. When made, you'll get C:\S:\ORDERS\xxx.csv file's.

In the MP277 you can see the scripts, the save script works, the fetch not. The historian works too, but you cannot test this because it's windows ce.




You are not really explaining how it actually works.
In case Excel is used, then the limit is 65,536 rows by 256 columns.
Even so, it is easy to goof up, make double entries and so forth.
And what if 2 persons must work on the data at the same time ?
If this is something that integrates into the production ordering system of a medum-sized factory, it shouldnt be taken lightly. Whoever that specify such requirements must expect to pay what it costs.
With a home-grown system, the costs for creating and maintaining the application must be factored in. So maybe it isnt cheaper at all in the long run.
For example, FactorySQL costs 2000 USD. Thats about 1 week engineering time.
My stance on VB scripts and this is that it should be kept to a minimum, and should definitely not be used for stuff that is critical.

JesperMP
March 13th, 2009, 10:05 AM
Combo, do you expect me to use my time with that, just to take a look at it ? Sorry, but I dont think so.
I do not doubt that what you have created before worked as you described.
As you describe it, the reason for you trying to cram it into an MP277 is that noone reserved the money needed to make it the right way.

Combo
March 13th, 2009, 10:13 AM
Okay, nevermind then, I tought you were interested in how it worked, sorry.

We started with a simple batching thing: length, diameter and corner. No recipes, nothing. Then the customer wants an extra, and again an extra, and another extra, sorting, historian, etc..., it had grown, and I agree, a little too big for wincc flex and using flat files. If I knew what I know now, I would've used other tools. I wouldn't create it, more an IT'guy. Indeed, money, allways the same priority.

I have no choice now, continu with what I have, and search why the FETCH code doesn't work

Combo, do you expect me to use my time with that, just to take a look at it ? Sorry, but I dont think so.
I do not doubt that what you have created before worked as you described.
As you describe it, the reason for you trying to cram it into an MP277 is that noone reserved the money needed to make it the right way.

Combo
March 21st, 2009, 12:40 AM
found the error:

the line f.open (file_name_path), 1 is missing in the read script

Okay, in an other topic I was searching for saving and fetching data out of a database. I was doing it in a PLC, but it eated all the retentive memory of the CPU.

So the idea is writing to csv and reading from it.

I have 5 correctionparameters that I save and retrieve. I write them on a buttonevent and read them when the productcode changes.


This is the write script:

Dim f, fs, file_path, file_name, file_name_path
On Error Resume Next
Set fs = CreateObject("filectl.filesystem")
Set f = CreateObject("filectl.file")
file_path= "\Storage Card MMC\"
file_name= CStr(SmartTags("ACTIVE BATCH.ORDER_DATABASE.ARTIKEL_NUMMER"))& ".csv"
file_name_path= file_path & file_name
If Err.Number <> 0 Then
ShowSystemAlarm "Error#" & CStr(Err.Number)&""& Err.Description
Err.Clear
Exit Sub
End If
f.open (file_name_path), 2
f.Lineprint (SmartTags("INSTELLING PONSUNIT.CORRECTIES.LINKS.ECS") & ";")
f.Lineprint (SmartTags("INSTELLING PONSUNIT.CORRECTIES.LINKS.HOEK") & ";")
f.Lineprint (SmartTags("INSTELLING PONSUNIT.CORRECTIES.LINKS.HOOGTE") & ";")
f.Lineprint (SmartTags("INSTELLING PONSUNIT.CORRECTIES.RECHTS.ECS") & ";")
f.Lineprint (SmartTags("INSTELLING PONSUNIT.CORRECTIES.RECHTS.HOEK") & ";")
f.Close

Set f = Nothing
Set fs = Nothing

If Err.Number <> 0 Then
ShowSystemAlarm "Error#" & CStr(Err.Number)&""& Err.Description
Err.Clear
Exit Sub
End If
ShowSystemAlarm "Corrections storage was successful"

So I print 5 line's in a csv, the csv is named after the product (artikelnummer). This is done for retrieving easely.


This is the read script:

Dim f, fs, file_path, file_name, file_name_path, DataSet,MyZf, field, HiField, i, j
On Error Resume Next
Set fs = CreateObject("filectl.filesystem")
Set f = CreateObject("filectl.file")
file_path= "\Storage Card MMC\"
file_name= CStr(SmartTags("ACTIVE BATCH.ORDER_DATABASE.ARTIKEL_NUMMER"))& ".csv"
file_name_path= file_path & file_name
If Err.Number <> 0 Then
ShowSystemAlarm "Error#" & CStr(Err.Number)&""& Err.Description
Err.Clear
Exit Sub
End If
If fs.dir(file_name_path)="" Then
SmartTags("INSTELLING PONSUNIT.CORRECTIES.LINKS.ECS") = 0
SmartTags("INSTELLING PONSUNIT.CORRECTIES.LINKS.HOEK") = 0
SmartTags("INSTELLING PONSUNIT.CORRECTIES.LINKS.HOOGTE") = 0
SmartTags("INSTELLING PONSUNIT.CORRECTIES.RECHTS.ECS") = 0
SmartTags("INSTELLING PONSUNIT.CORRECTIES.RECHTS.HOEK") = 0
Else
Do While f.eof=False
MyZf=f.lineinputstring
field=Split(MyZf,";")
For i= 0 To 4
field(i)= Replace(field(i),"","")
HiField(j,i)=field(i)
Next
SmartTags("INSTELLING PONSUNIT.CORRECTIES.LINKS.ECS") = HiField(0,1)
SmartTags("INSTELLING PONSUNIT.CORRECTIES.LINKS.HOEK") = HiField(1,1)
SmartTags("INSTELLING PONSUNIT.CORRECTIES.LINKS.HOOGTE") = HiField(2,1)
SmartTags("INSTELLING PONSUNIT.CORRECTIES.RECHTS.ECS") = HiField(3,1)
SmartTags("INSTELLING PONSUNIT.CORRECTIES.RECHTS.HOEK") = HiField(4,1)
j=j+1
Loop
f.close
End If
Set f = Nothing
Set fs = Nothing

If Err.Number <> 0 Then
ShowSystemAlarm "Error#" & CStr(Err.Number)&""& Err.Description
Err.Clear
Exit Sub
End If
ShowSystemAlarm "Corrections fetching was successful"


This one doesn't work...
Any ideas please ?

Thanks is forward.

Combo
March 24th, 2009, 04:54 AM
I did a test with 15000 csv file's in 1 folder en tested the scripts for that folder (tested on MMC).

It was no problem, no delays or whatever, works great!

So this a sort of databasing that can work !! It's cheap and it works...


The reason why it isn't slow... can it be because I write and read to csv's directly (path + filename)... ? I agree when I open the folder in Windows that it takes a while before I can see the files... But that doesn't matter

GeoffC
March 24th, 2009, 05:27 AM
I agree with Jesper I would not do it in a flat file. I would use MySQL, it is also free but is a full SQL database system. I don't know about Wincc but can't you just read and write to a database using SQL commands, or are there tools to access a SQL database. I do this all the time in Citect
Also it is much easier to do a database backup than try and save and manage 1000s of files

Combo
March 24th, 2009, 05:35 AM
Hey,

Yes, you're right, on the PC side I can do that. I can connect data to an SQL database with vbscripting.

But the MP277 needs a database also. We allready bought the MP277, so we need to continu with it. An MP277 has also vbscripting, but no supportation of SQL databasing... It should have been a panelpc with flex runtime, then I could do sql databasing


I agree with Jesper I would not do it in a flat file. I would use MySQL, it is also free but is a full SQL database system. I don't know about Wincc but can't you just read and write to a database using SQL commands, or are there tools to access a SQL database. I do this all the time in Citect
Also it is much easier to do a database backup than try and save and manage 1000s of files

Combo
March 24th, 2009, 05:46 AM
Another thing, why is Siemens offering import and export fuctions for csv (recipesystem of wincc flex)... if it's not trustable or efficiŽnt, why are they offering it then ?

JesperMP
March 24th, 2009, 07:34 AM
Hi Combo.

That Siemens makes it possible to edit the recipe-csv files manually, does not mean that it is a good idea. Maybe yes for a simple small project.

Anayway, the system limits is another indication that it may not be a good idea. The number of data records are limited to 500 on an MP277 and 5000 on a PC RT. And you said you need "at least 10000".

I would really consider to let a 3rd party product on a server PC handle all of this. Like I said, FactorySQL would do the trick for what amounts to 1 week work. Can you do it faster than that ? Will it be as reliable ?

edit: Since you mention that it is possible to connect to MySQL via scripting.
This is not what I suggest.
I warn against connecting to SQL via VBS. I have been burned. It would be OK for non-critical stuff. In your case it sounds as it is an important part of the project.

Combo
March 24th, 2009, 07:54 AM
at least 10000, hmm, I'm sorry, but that's not the case, sorry that I wrote that. I wanned to be sure that 10000 was no problem. They make different products / year.

10000 / 500 = 20 years, I think, 15000 = absolute max (30 years is a long time).


And I agree , the next time I'll go for inductive automation, I've heard very positive comment of them. For now I think I'll need to go for the recipes in wincc flex with the import and export with csv file's.







Hi Combo.

That Siemens makes it possible to edit the recipe-csv files manually, does not mean that it is a good idea. Maybe yes for a simple small project.

Anayway, the system limits is another indication that it may not be a good idea. The number of data records are limited to 500 on an MP277 and 5000 on a PC RT. And you said you need "at least 10000".

I would really consider to let a 3rd party product on a server PC handle all of this. Like I said, FactorySQL would do the trick for what amounts to 1 week work. Can you do it faster than that ? Will it be as reliable ?

edit: Since you mention that it is possible to connect to MySQL via scripting.
This is not what I suggest.
I warn against connecting to SQL via VBS. I have been burned. It would be OK for non-critical stuff. In your case it sounds as it is an important part of the project.

Combo
September 20th, 2010, 11:15 AM
Hellow,

Long time ago I used the flatfile read and write with VBS. This works.

Now for a logging project I need a solution to log parameters of a used recipe. Everything in a table form. It has to be possible to request on date and time which recipe has been used, how long did the steps needed before transistion , etc...
This cannot be done in flat files, because searching after certain data would take too much time.

So I was thinking and searching...
Found: 24677043 on the siemens support site.

It's a helppdf + project that is explaining how to read and write to and from an SQL database. Could this be done or should I use OPC and write my own query program in C or VB or something ? I've red from JesperMP that you can be burned by reading and writing with SQL via VBS code...
What's the best solution ?
FactorySQL is not an option.

Kind regards,
Gerry



Hi Combo.

That Siemens makes it possible to edit the recipe-csv files manually, does not mean that it is a good idea. Maybe yes for a simple small project.

Anayway, the system limits is another indication that it may not be a good idea. The number of data records are limited to 500 on an MP277 and 5000 on a PC RT. And you said you need "at least 10000".

I would really consider to let a 3rd party product on a server PC handle all of this. Like I said, FactorySQL would do the trick for what amounts to 1 week work. Can you do it faster than that ? Will it be as reliable ?

edit: Since you mention that it is possible to connect to MySQL via scripting.
This is not what I suggest.
I warn against connecting to SQL via VBS. I have been burned. It would be OK for non-critical stuff. In your case it sounds as it is an important part of the project.