Store PLCnext CommunityPLCnext on LinkedInPLCnext on Instagram  PLCnext on YouTube Github PLCnext CommunityStore PLCnext Community

0
Votes
Undo
  1. Arne Smets
  2. PLCnext Technology & PLCnext Controls
  3. Wednesday, 14 April 2021
Hey,
I've the wish to have a local (cloud-based in the future) database, where I can store different values of PLC variables. Those should be available for different PLCNext devices. In the device, i want to select (on the HMI) which data I want to use to set all those variables to,
I was thinking about a SQLite connection in C#, but i've got quite some problems with using Nuget package System.Data.Sqlite, since it requires different frameworks then the eCLR 3.3. I'm not sure if this is possible.
Does anybody has some experiences with database interaction? What is the easiest way to set this up?

Arne
Rob H Accepted Answer Pending Moderation
0
Votes
Undo
I've been successful and happy with the DBFL_SQL library and reading/writing data via IEC 61131 to MSSQL. I have not tried any other methods- C#, C++, or the datalogger libraries.
Automation Engineer - MI, USA
Arne Smets Accepted Answer Pending Moderation
0
Votes
Undo
Thanks Rob! Haven't thought at the store at all...
I will have a look at it and keep this post updated.
Oliver PLCnext Team Accepted Answer Pending Moderation
0
Votes
Undo
Hello Arne,
sqlite is only supporting Local Database files so this is not what you are looking for.
(even if you get it running)
Robs suggestion is good but requires a seperate SQL Server. MS SQL or MySQL
But as you want multiple PLCs to access the same data anyways I think this a good solution.

kind regards,
Oliver
Phoenix Contact Electronics Headquarters - PLCnext Runtime Product Management and Support
Arne Smets Accepted Answer Pending Moderation
0
Votes
Undo
Thanks for your answers!
I've not worked with db's before so maybe the questions are quite easy to solve.

I have one more question about mysql table.
I am able to connect to a database on my PC with the mySQL acces of this library block DBFL_SQL.
It also recognizes the DB_name, but how can I specify the name of the table? I have a RCV_SIZE but ROW-/COL_CNT remains zero.
I've created a simple 3x3 table.
Arne
Noah Greene Accepted Answer Pending Moderation
0
Votes
Undo
There is a FB called "DBFL_CODE" in the DBFL_SQL library. You will use use the various STRING datatype inputs to write your command. For example, if you want to retrieve the "Title" value from table "Books" where the Author is "Ian Fleming", you would break up the string "SELECT author FROM Books WHERE author='Ian Fleming;'"

Page 48/63 in the documentation for the DBFL_SQL library gives a decent break down of this operation, but it uses the "CREATE TABLE" query as its example. Changing this to a different query isn't a difficult task though.

Cheers,
Noah Greene
Attachments (1)
Arne Smets Accepted Answer Pending Moderation
0
Votes
Undo
I have an new question on this.
I understand how to use the blocks and recieve the data.
What I saw now is that the buffer of the block is only 1440 bytes? This looks a bit small for a table...
I have a table with 34 columns, and i think it can expand to about 30 rows.
I check the size of the DB with the line : *ROUND((DATA_LENGTH + INDEX_LENGTH)) AS `Size (B)`*
This returns around 16kB.

But it does not change size if i'm adding rows.. How is this possible and does someone know how to decrease size of the table?

Arne


EDIT
I've tried to change the table. Deleted half the columns, rows, new datatypes etc, but the size is still around 16kB.
So maybe a better question is, what is the best way PLCNext can handle this? 16kB is quite small...
Oliver PLCnext Team Accepted Answer Pending Moderation
0
Votes
Undo
Hello Arne,
you handle this as you usually handle databases.
You dont return the whole DB but just the specific Row you are looking for.
Or if you need all the data you iterate through the DB by polling the data row by row or even cell by cell if it the content is a string of length X.
Changing the sice of the buffer is not realy a solution as usually DBs are very large.
Have a look at the SQL syntax SELECT etc.
Hard coded commands usually are not what you need you create the table just once after that you will be inserting rows.
Depending on the situation you need to assemble the command strings.
The FB has a couple inputs so you are able to structure that command more nicely.

What FB exactly would you need a larger array at?

kind regards,
Oliver
Phoenix Contact Electronics Headquarters - PLCnext Runtime Product Management and Support
  • Page :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.