Database Acces

Arne Smets

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

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.

Arne Smets

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

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

Arne Smets

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

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

Arne Smets

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

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

yoyo

I want to store some data from plcnext in mysql database , i try to use DBFL_SQL but i have an error that thé datagram IS too long.Can you please help me to solve this error.

John_Capture

Dear Expert

I'm using DBFL_SQL  Version 8.

I can cannect to SQL, create table, but can't read table command.

The error 16#22 happened

How can I fix it.

Many Thank

steve.windham

Can you please post the command you are sending to read the table?

John_Capture

https://forum.plcnext-community.net/discussion/comment/13586#Comment_13586

Dear Sir.

The command: "Select * from Example". This commend is execute in SQL Ok.

Example is existed table in SQL.