Page 2 of 2 FirstFirst 1 2
Showing results 11 to 19 of 19

Thread: Receive and send tables SQL Database

  1. #11
    Join Date
    09.09.2013
    Posts
    26

    Default AW: Receive and send tables SQL Database

    Hi again Mark,

    The thing is that I have to store the data that I get from Beckhoff in the database and then read them when it´s necessary.

    I mean, I have data stored, a complete database that has historical data from the process. What I need is to read them depending on the time period. I will have data from the results of one day, that I have to show in one screen; the data from a complete week in another screen, and the data from a complete month in another one. I have to select the tag, the month and the week and, depending on that, I should show the results.

    I hope I have explained correctly what I need.
    Is for that the best option using the Report Generator?

    Thank you,
    Kind regards,
    Luc

  2. #12

    Default Re: Receive and send tables SQL Database

    Hi Luc,

    If the data is already in the database, then the report generator is probably your best option.

    But I still don't understand exactly why you don't use the Beckhoff driver and the zenon historian to get the data from PLC, store it in an archive and subsequently store it in a SQL database?

    Best regards,
    Mark

  3. #13
    Join Date
    09.09.2013
    Posts
    26

    Default AW: Receive and send tables SQL Database

    Hi Mark,

    I am using already the historian to stored the data with evacuation in a SQL database, as you have told me in your last message. The problem that I have now is that Zenon has created the tables in the database (I can see them with the SQL Server Management Studio Express) but I have no value from the historian on them. I mean, it seems like the data are not really evacuated...

    Using the report generator, I can see the values from the historian, but when I try to read them directly from the SQL database (with "sqldao") I cannot see any value.

    I only want to check if the configuration is correct, and the data are stored in the database from the archive... Any idea?

    Thank you again,
    Luc

  4. #14

    Default Re: Receive and send tables SQL Database

    Hi Luc,

    In the historian configuration, you define several options that influence the evacuation of the archives into the SQL database.

    Firstly a time after which the archive is closed (saved).

    - Only closed archives are evactuated in the SQL database.

    Secondly, a time for which the archives are kept locally until they are evacuated.

    - Only after this time has expired the archives are evacuated in the SQL database.


    So if you want to have your archives in the SQL database almost immediately, you can define a short archive save time (e.g. 1 minute) and no time frame which the archive files should be stored (0 minutes).

    This way after the archive is closed, the archive is immediately evacuated in the database.

    For the runtime it is transparent where the data resides. When for example you view a trend, it can be that part of the data is coming from the local archive files and another part of the data is coming from the sql database because that archive data was already evacuated. The user will not notice any of this, it is done automatically.

    Finally, the SQL evacuation is an optional module. When you can configure it in the editor, make sure that it is also licensed on the runtime computer. If in this case the editor and the runtime are on the same computer and use the same license, it should work.

    Best regards,
    Mark

  5. #15
    Join Date
    09.09.2013
    Posts
    26

    Default AW: Receive and send tables SQL Database

    Thank you Mark!

    I hadn´t the right configuration, but now I can see immediately the data in the database

    Kind regards,
    Luc

  6. #16
    Join Date
    09.09.2013
    Posts
    26

    Default AW: Receive and send tables SQL Database

    Hello again Mark,

    As I told you the another day, I have solved the problem with the SQL database and I can already write on it using an archive (with the evacuation in SQL).

    My problem now is when I want to read some variables and show them in the report generator.
    I have a group of data that have different values depending on the value of another one. All of them are into the database.

    As example:

    Charge = 1
    Var1=10
    Var2=20
    Var3=30

    Charge=2
    Var1=1
    Var2=2
    Var3=3

    I need to read the variables Var1, Var2 and Var3, depending on the value of Charge, which I must be able to change in Runtime.

    I have tried it with SQLDAO, writing a condition with WHERE. When I use a numerical value in the condition I have no problem, but as I have already said, the value of Charge can change in Runtime, so I need another solution, like a symbolic statement that allows me to change the value of Charge and be able to read the data associated to that value.

    Have you any idea?

    Thank you very much,

    Kind regards,
    Luc

  7. #17

    Default Re: Receive and send tables SQL Database

    Hi Luc,

    I'm not sure if this is of any help, but did you have a look at the parameters that you can specify at the report generator function?

    Click image for larger version

Name:	ScreenShot093.png
Views:	25
Size:	9.4 KB
ID:	1275

    Alternatively, you could try and read the value of a variable in your report, and perhaps use that value (cell that contains the value) in your SQLDAO statement. A report is executed row by row from left to right, so make sure that the function where you read the variable is in a row above the sqldao function.

    Best regards,
    Mark

  8. #18
    Join Date
    09.09.2013
    Posts
    26

    Default AW: Receive and send tables SQL Database

    Hi Mark,

    I have already tried to read a value from another cell with SQLDAO:

    That is what I have made:

    In the cell B2: =variabler("Charge","wert")

    In the cell F7: =sqldao("Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=1059d721-ab62-4a9b-a04e-3e1a35b76559;Data Source=.\ZENON_DEV;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=ST-WB5-SSD;Use Encryption for Data=False;Tag with column collation when possible=False;MARS Connection=False;DataTypeCompatibility=0;Trust Server Certificate=False","SELECT PROJEKT_RZ.VALUE FROM PROJEKT_RZ WHERE (PROJEKT_RZ.VALUE=%d);",B2,0).

    But the only result that I have is an error in the cell of the report (ERR_SQLDAO) and I cannot see any value in runtime.

    Please, could you tell me why I get an error like that and how could I solve it?

    Thank you,
    Luc

  9. #19

    Default Re: Receive and send tables SQL Database

    Hi Luc,

    Have you tried with "%s" instead of "%d" to pass a string parameter instead of a numerical value?

    Best regards,
    Mark

Similar Threads

  1. Date Tables
    By lew in forum zenon Supervisor
    Replies: 1
    Last Post: 22nd January 2012, 23:53
  2. Restore project from SQL Database Folder
    By bernhardf in forum zenon Supervisor
    Replies: 0
    Last Post: 13th October 2009, 09:07
  3. SQL Database and Zenon Editor
    By bmcbrita in forum zenon Supervisor
    Replies: 4
    Last Post: 2nd August 2009, 22:24
  4. SQL Database files
    By bmcbrita in forum zenon Supervisor
    Replies: 7
    Last Post: 17th June 2009, 14:26
  5. Replies: 0
    Last Post: 15th November 2007, 13:32

Posting Rules

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •