SQL statement variable stitching

Key words: Zijinqiao Software, Zijinqiao monitoring configuration software, relational database, SQL statement

Many users will use the Zijinqiao software to build the control system and interact with the relational database. In the process of using the relational database, a large number of SQL scripts are bound to be used. The where statement in the SQL script often needs to be composed of variables. , So how to splice variables in the script has become a problem for many users, this article will explain how to splice variables in the SQL script.

First we create a data table named People in the relational library, as shown below:

(The relational library is MS2005)

After the data table is created in the relational database, the Zijinqiao project is opened, and the data link is established with the relational database in the project. (Note: How to create a relational library link please refer to the document of Zijin Bridge Configuration Software and Relational Library Communication)

After the data link creation is completed, a 'query button' is created on the Zijin Bridge screen, and the conditions 'text box' and 'free report' are used to display data.

Query the contents of the relational library on the condition of 'name', and put the content in the report. Double-click the button to write a query script in a custom mouse action.

First define nSql, Name two string temporary variables, and then assign the content of the text box as a condition to the Name variable in the screen, assign the written SQL statement to the nSQL variable, and finally execute this SQL code by the free statement.

The positions labeled with red color boxes in the figure are grouped into one paragraph. The two double quotes refer to each other in the middle. The double quotes marked with black boxes refer to the single quote in the middle, and the contents in the middle are not marked. For the variable part, run the software after writing the script to see the effect

First of all, we query all the data, you can see the report shows all the data in the database. Then we enter the name in the name text box, indicating that only the field [Name] is equal to the content of the condition, as shown below

When we click 'Data' in the text box and click on the query button, the data with the name 'Li Hong' will be queried.

The above we are talking about the variable is a character type splice variable, then how to write when the variable is the PV value of the database point, the following we talk about.

First create a point A1 in the point configuration and associate it with the text using the analog input in the screen. Then open the script and write the script as shown.

You can see that the condition in the script content is that the query [Tel] field is equal to 'A1.PV'. Then why does the A1.PV variable use the IntToStr() function? Because the SQL statement is a string type, the condition variables mentioned above are also characters. Since the string type does not need to be converted, and 'A1.PV' is a real variable, the IntToStr() function is required to convert the integer to a string, otherwise the data type error will be prompted.

After running it, look at the effect, and when it starts running, check out all the data.

Then we modify A1.PV to 141, and then click on the query button, then the query data is the content of the data in line with the conditions

When the Delete, Update, Insert, etc. statements need to be concatenated during use, the method is the same as the Select statement.

I hope this article can help everyone get some help when using the software. If the article is unclear or has other problems, you can also contact our technical staff at 0459-8151391.