How to add records to the PostgreSql table using SqlRequest function block?

Question:
How to add records to the PostgreSql table using SqlRequest function block?

Answer:
Example of adding a record to a PostgreSQL database table using the SqlRequest function block (hereinafter referred to as SqlRequest).

A test project is attached (see PostgreInsert.7z)

1. Create the "distributors" table in the database (hereinafter referred to as DB) (in this example, the DB name is "mps1") by executing the following instruction:

-- Table: public.distributors
DROP TABLE IF EXISTS public.distributors;
CREATE TABLE IF NOT EXISTS public.distributors
(
did integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
dname character varying(40) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT distributors_pkey PRIMARY KEY (did),
CONSTRAINT distributors_name_check CHECK (dname::text <> ''::text)
)
TABLESPACE pg_default;
ALTER TABLE IF EXISTS public.distributors
OWNER to postgres;

2. Establish a connection from the MasterScada 4D project to the PostgreSQL server

2.1. Add a Workstation, for example, Workstation1.

2.2. Add the PostgreSQL protocol to Workstation1.

2.3. Configure the Protocol properties for connecting to the DB:

1.png

3. Override the SqlRequest in the local library by adding input parameters corresponding to the table fields created in step 1:

- did, type UDINT;

- dname, type STRING.

2.png

4. Add a program to the protocol, for example "prInsertCommand", of type MasterSCADA.Program.ProgramFBD.

5. Add the SqlRequest instance from the local library to the created program.

6. Set the program parameters and link them to the SqlRequest parameters:

3.png

4.png

7. Run the project in runtime mode with configuration loading.

8. Set the values for the 'pdid' and 'pdname' parameters.

5.png

9. Set the 'pRun' parameter to True.

10. Check the result in the DB

6.png

Обсуждение

Log in or register to participate in the discussion.

Thanks! You will be contacted soon.

Write to us
Contact person: 

Request topic 
Message:

Thanks! You will be contacted soon.

Contact manager
Contact person:

Describe the project:

Attach file:

1 pdf/doc/docx file, max 10MB

Thank you for contacting us! The download links have been sent to your email

We invite you to cooperate

Contact person:

Request topic

Message:

Thank you! Your application has been accepted!

Cooperation request
Your cooperation goal with us:
University name:

City:

Education form: 
Contact person:

Thanks! You will be contacted soon.

Send resume
Contact person:

Specify education and specialty:

Short motivation letter: test
Resume file:

1 pdf/doc/docx file, max 10MB
Describe work experience:

Thanks! You will be contacted soon.

Event registration
Contact person:

Thanks! You will be contacted soon.

Get consultation
Contact person:

Describe the project:

<

Thanks! You will be contacted soon.

Start training
Contact person:

Thank you for contacting us! The download links have been sent to your email

Try software

Leave your contact information and we will send you a link and instruction.

You will get access to the demo project.

Contact person:

Comment:

Thank you for contacting us! The download links have been sent to your email

Try software on your model

Leave your contact details - we'll send installation link and instructions.

You can test the software on your information model.

Contact person:

Comment:

Thank you! Your application has been accepted!

Request configuration
Contact person:

<

Number of tags for each MasterOPC copy separated by semicolons (e.g. 500;1000):

Automation object:

Details:

Additional wishes:

Additional documents:

Up to 2 pdf/doc/docx/odt files, max 10MB each

Thank you! Your application has been accepted!

Request configuration
Contact person:

Configuration parameters
Number of external I/O points:
Number of server I/O:
Number of additional clients:
Protocols for lower-level communication:
Operating system in the project:
ToR, block diagram, or other documents:

Up to 2 pdf/doc/docx/odt files, max 10MB each
Additional information:

Thank you! You have successfully registered

Registration is successful

Registration
First name

Field is required

Last name

Field is required

Middle name

Field must not contain digits

Phone

Invalid phone number

E-mail

Invalid email

Company

Field is required

Tax ID (INN)

Password

Field is required

Confirm password

Field is required

Письмо для смены пароля отправлено

Восстановление пароля
E-mail

Invalid email

Вернуться к началу страницы