Showing results 1 to 8 of 8

Thread: Sqldrv

  1. #1
    vasco Gast

    Default Sqldrv

    How can I read from a SQL table?
    I had already created both "IN" and "OUT" tables with equal format both, and also I can write to the "OUT" table throght zenon RT, but the fields where the SQL variables are linked are with a red mark, and always with zero value.
    What data should I put in the table "IN"?
    What means the last field: Status flags of the variable?

    Thank you all!

  2. #2

    Default Re: Sqldrv

    What you can do to see how it works, is copy the line from your OUT table, and insert the line into your "IN" table. Normally if the SQL Table is configured correctly, and the driver reads the variable, the line should be deleted automatically after the runtime has read the variable. In zenon the value you have previously written to your "OUT" table should be displayed, instead of "0" and the red square.

    The time-stamp of the variable is also external, meaning you can define the time-stamp of the variable in the SQL "IN" table.

    Also you can set specify the status of the variable with the statusflags. The number you see in this column, is the decimal value of all the active statusbits. Please see the documentation "status processing" in the online help for more information.

    If the line is not deleted and the variable is not read correctly when copying the row from the "OUT" table to the "IN" table, you might want to try and set the option "Use TS time format" (Time in STRING and not in DATETIME format)

  3. #3
    vasco Gast

    Default Re: Sqldrv

    Nop, it doesn’t work...
    Read from OUT table works perfectly, with and without the "Use TS time format" option.
    I'd tried copying all data from this OUT table (named zenonout) to IN table (named zenonin) that are the same format, with the same time-stamp and a zero flag also, but nothing happens.
    I'm working with the zenon demo version, could be that why this don't work?
    Could you send me a small zenon application example, with a SQL creating table and inserting values statement also?
    Thank you all once more.

  4. #4

    Default Re: Sqldrv

    There is no difference in functionality what the demo-mode is regarding. It is only time-limited.

    Please find the create statements below:

    USE [DATABASENAME]
    GO
    /****** Object: Table [dbo].[zSEND] Script Date: 06/18/2007 15:52:29 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[zSEND](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [NAME] [nvarchar](max) NOT NULL,
    [DATUMZEIT] [datetime] NOT NULL,
    [ZEIT_MS] [int] NOT NULL,
    [WERT] [nvarchar](max) NOT NULL,
    [STATUS] [int] NOT NULL,
    [ACK_SRV] [int] NULL,
    [ACK_SB] [int] NULL,
    [INSERTZEIT] [datetime] NULL,
    CONSTRAINT [PK_ZENONSENDEN] PRIMARY KEY CLUSTERED
    (
    [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    USE [DATABASENAME]
    GO
    /****** Object: Table [dbo].[zRECEIVE] Script Date: 06/18/2007 15:51:59 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[zRECEIVE](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [NAME] [nvarchar](max) NOT NULL,
    [DATUMZEIT] [datetime] NOT NULL,
    [ZEIT_MS] [int] NOT NULL,
    [WERT] [nvarchar](max) NOT NULL,
    [STATUS] [int] NOT NULL,
    [ACK_SRV] [int] NULL,
    [ACK_SB] [int] NULL,
    [INSERTZEIT] [datetime] NULL,
    CONSTRAINT [PK_ZENONEMPFANGEN] PRIMARY KEY CLUSTERED
    (
    [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    perhaps if it still does not work, maybe you could send a workspace backup, a backup of the sql database where you store the values, and the file-dsn to the support e-mail address?

    support@copadata.at

  5. #5
    flajoso Gast

    Default Re: Sqldrv

    Hi Vasco,

    did you solved your problem? We had exactly the some problem... we just can read the first line of the SQL Table.

    Regards,

    Filipe

  6. #6

    Default Re: Sqldrv

    Please note, that the first line is automatically deleted, after it has been read from the SQL driver correctly. If the line is not deleted, it means that the SQL driver was not able to read correctly (perhaps due to an incorrect table).

    You could check the log-files from the diagnosis server, using the tool "diagnosis viewer" to find out more about why the driver communication to the SQL database is going wrong.

    Or you could also send us a project backup of your project, a backup of the SQL database where you want to store (write) the values and read the values, your file-DSN, and a detailed description, by e-mail to: support@copadata.at

  7. #7
    flajoso Gast

    Default Re: Sqldrv

    Hi Mark once again,

    we checked the Diagnosis Viewer and tehre is the following error:

    "Cannot read from Receive-Table: The cursor does not include the table beeing modified or the table is not updatable through the cursor.~The statement has been terminated.~"

    We are going to send a small project backup and sql back and dsn backup.

    Thanks!

  8. #8
    flajoso Gast

    Default Re: Sqldrv

    Problem solved!

    Thanks to the help of MarkClemens, we checked two errors:

    -we had SQL Server instead of SQL Native Cliente on the ODBC connection

    -Our sql user was not configured with delete permissions, just read. So, when the driver tryed to read the values on the table and then tryed to delete it, the problem happened.

    Well, hope that this will be usefull for another Zenon user.

    Best regards,

Posting Rules

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