Your cart is currently empty!
Applicable Model(s)
EBPro
Title
Creating SQL Tables with Advanced Mode
Date
04/30/2025
Rev
00
P/N
TN5158
Summary
This technical note demonstrates how to use EBPro’s SQL Advanced Mode to create a table on a connected SQL database. In the first section, users will learn how to enable Advanced Mode, define a new Command ID, and enter SQL syntax to statically create a table. The second section expands on this by introducing runtime flexibility using Arguments. An ASCII object or macro writes a table name to a designated LW register, which is then used dynamically within the SQL command to create custom tables during runtime.
Solution
Enabling Advanced Mode
NOTE: Advanced Mode is not able to be disable once enabled. If it is a new project, then you must first create a Database server and ensure connection can be established.
1.
Open an existing project or create a new project and navigate to Data/History tab and click on SQL Query.
2.
Create a new SQL query.
3.
Enable Advanced Mode.
4.
Click “Yes” to continue in Advanced Mode.
Define a Command ID, and SQL syntax
NOTE: One column is required to create a table.
1.
Click on the “Command” tab inside the SQL Query dialog menu.
2.
Click “New” to instantiate a new SQL Query Command.
3.
Give the Command ID a unique value, and a description of what the command will do.
4.
Inside the SQL Query section enter the following:
CREATE TABLE database_name.example ( id INT PRIMARY KEY );
5.
Place a Numerical Object pointed at the SQL Queries Control Address.
6.
Test in offline/online simulation.
7.
Write the Command IDs value to the Numerical object.
8.
Observe the newly created table.
Creating a dynamically named table
1.
Follow steps 1 through 4 of “Enabling Advanced Mode”.
2.
Follow steps 1 through 3 of “Define a Command ID, and SQL syntax”.
3.
Inside the SQL Query section enter in the following syntax:
CREATE TABLE database_name.${1} ( id INT PRIMARY KEY );
4.
Click the “Argument” tab then click “New”.
5.
Enable “String” and give it an available LW address. By default, it will be assigned 20 LW addresses starting from the defined LW starting address. Adjust the number of words according to the project’s requirements. Afterwards press “OK.”
6.
${1} is now defined as a LW register.
Testing
1.
Place a Numerical Object pointed at the SQL Queries Control Address.
2.
Place an ASCII object pointed at the SQL Queries string Argument LW register. Assign the ASCII object the same number of words defined in the Argument section.
3.
Test in offline/online simulation.
4.
Write a string value, with no spaces, to the ASCII object.
5.
Write the Command IDs value to the Numerical Object.
6.
Observe the newly created dynamically named table.