MP277, interactive databasing with csv file's

userxyz · Mar 12, 2009

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:

Code:

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:

Code:

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.

userxyz · Mar 12, 2009

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

userxyz · Mar 13, 2009

okay

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 ?

Combo said:
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...

userxyz · Mar 13, 2009

Store example

JesperMP · Mar 13, 2009

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.

userxyz · Mar 13, 2009

euhm

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:

Code:

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:

Code:

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

JesperMP said:
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 · Mar 13, 2009

Combo said:
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.

userxyz · Mar 13, 2009

hmm

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.

JesperMP said:
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 · Mar 13, 2009

Combo said:
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.

Combo said:
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.

userxyz · Mar 13, 2009

..

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

JesperMP said:
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 · Mar 13, 2009

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.

userxyz · Mar 13, 2009

ok

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

JesperMP said:
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 · Mar 13, 2009

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.

userxyz · Mar 13, 2009

Office

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 ?

JesperMP said:
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 · Mar 13, 2009

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 ?

MP277, interactive databasing with csv file's

userxyz

Member

userxyz

Member

userxyz

Member

userxyz

Member

JesperMP

Lifetime Supporting Member + Moderator

userxyz

Member

JesperMP

Lifetime Supporting Member + Moderator

userxyz

Member

JesperMP

Lifetime Supporting Member + Moderator

userxyz

Member

JesperMP

Lifetime Supporting Member + Moderator

userxyz

Member

JesperMP

Lifetime Supporting Member + Moderator

userxyz

Member

JesperMP

Lifetime Supporting Member + Moderator

Similar Topics