How to use a dynamical value in a SQL from DBconnector Import

Question

question

Borja Lorenzo asked Oct 4, '24 Borja Lorenzo commented 6 days ago

How to use a dynamical value in a SQL from DBconnector Import

Hello
I have a query in DBConnector, this query is executed from the DBConection import option in FlexSim. It searches a table in the connected database for specific data that has the same timestamp selected by the user in the GUI.

WITH SignalData AS (

SELECT
*,
SUBSTRING_INDEX(_NAME, '.', -1) AS SignalType,
SUBSTRING_INDEX(SUBSTRING_INDEX(_NAME, '.', 3), '.', -1) AS Station,
DATE_FORMAT(_TIMESTAMP, "%Y-%m-%d %H:%i:%s.%f") AS _FECHAHORA
FROM OPC
)
SELECT
*,
(
CASE
WHEN _NAME ='plcOut.Device1.Out.Plataforma_Final_Cinta' THEN 1
WHEN Station = 'MagFront' THEN 2
WHEN Station = 'Meas' THEN 3
WHEN Station = 'Drill' THEN 4
WHEN Station = 'MagBack' THEN 5
WHEN Station = 'Press' THEN 6
WHEN Station = 'Out' THEN 7
END
) AS SignalOrderStation,
(CASE
WHEN SignalType = 'Plataforma_Inicio_Cinta' THEN 1
WHEN SignalType = 'Plataforma_Final_Cinta' THEN 2
END
)AS SignalOrderInicioFin
FROM SignalData
WHERE _TIMESTAMP = '2024-09-20 11:54:42.693' -- Filtro de tiempo exacto
ORDER BY
_NUMERICID, -- Primero ordenamos por _NUMERICID
CASE
WHEN _NUMERICID = 0 THEN SignalType -- Para _NUMERICID = 0, ordenamos por tipo de señal
WHEN _NUMERICID = 1 THEN SignalOrderStation -- Para _NUMERICID = 1, usamos SignalOrder predefinido
END,
CASE
WHEN _NUMERICID = 0 THEN SignalOrderStation -- Para _NUMERICID = 0, ordenamos por tipo de señal
WHEN _NUMERICID = 1 THEN SignalOrderInicioFin -- Para _NUMERICID = 1, usamos SignalOrder predefinido
END;

The timestamp should be selected from a GUI that I have prepared. My proposal is to define a GlobalVariable that points to the itemcurrent of that combobox cb_milesecond. This itemcurrent should be used to go to the list of items in cb_milisecond and extract the string at the index equal to the value of itemcurrent. This string should be able to be inserted into the query through the Global Variable since it is dynamic data.

In the captured example, I should be able to pass the string 2024-09-20 11:54:46.105000 to the query. The key is to do it dynamically with the user’s selection in the programmed GUI

How can I do this? I will send screenshots to explain better.

Thank you in advance

Software Version:

FlexSim 24.1.1

gui database globaltable import data dinamically value

1728036642539.png (149.7 KiB)

· 2

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Jeanette F ♦♦ commented · 6 days ago

Hi @Borja Lorenzo, was Jason Lightfoot's answer helpful? If so, please click the "Accept" button at the bottom of their answer. Or if you still have questions, add a comment and we'll continue the conversation.

If we haven't heard back from you within 3 business days we'll auto-accept an answer, but you can always comment back to reopen your question.

0 ·

Borja Lorenzo Jeanette F ♦♦ commented · 6 days ago

Hello Janette

My question is not completely resolved. In the last comment, I provided a better clarification of my doubt, and unfortunately, I have not received a concrete answer.
Thanks in advance for your feedback.

0 ·

Answer 1 · 2024-10-04T11:20:54Z

Jason Lightfoot answered Oct 4, '24 Borja Lorenzo commented Oct 10, '24

It sounds like you should be using the prepared statement and binding the timestamp as a parameter.

· 3

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Borja Lorenzo commented · Oct 04 at 07:05 PM

Hello Jason,
Thank you very much for your response, I really appreciate it. I would like to clarify that:

What I need is to instantiate from this import panel of the BDConnector

Where I have the query for data import.
The output/the value generated in this other selection GUI panel

Is this possible with a prepared statement

Thanks in advance

0 ·

1728068753314.png (99.3 KiB)

Jason Lightfoot ♦♦ Borja Lorenzo commented · Oct 08 at 10:04 AM

This is a question of timing and persistence. Can you guarantee the GUI is open and that we have a pointer to it? When is the import going to be run?

From the GUI you probably want to set a persistent value somewhere for the those that the import needs - either nodes on a known object or global variables. You can do this using the regular Apply method or OnKillFocus which will make sure they are mostly up to date.

You could try adding a button to the GUI to initiate the import if that makes for a better user workflow and control the timing.

0 ·

Borja Lorenzo Jason Lightfoot ♦♦ commented · Oct 10 at 01:39 PM

Hello Jason
Thank you for your comments, but I don't fully understand the approach explained.
I will try to be more concise:
I already have a GUI configured where the user selects a TimeStamp, and this TimeStamp is stored as a string in a Global Variable.
What I would like is to be able to execute a query from the DBConnector tool that imports the data for the selected TimeStamp.

Currently, I have solved it with an additional button in the GUI that runs code with the Database.preparestatement object you mentioned...

// Variable para activar/desactivar el modo de depuración
int mododebug = 1; // Cambia a 1 para activar los mensajes de depuración


// Conexión a la base de datos
if (mododebug) print("Connecting to database...");
Database.Connection con = Database.Connection("DatabaseConnector1");
con.connect();
if (mododebug) print("Database connected");


// Captura del timestamp seleccionado por el usuario
string selectedTimestamp = EstadoSimular; // Implementa esta función según tu GUI
if (mododebug) print("Selected Timestamp: " + selectedTimestamp);


// Obtener la tabla global
Table globalTable = Table("BD_CPLAB");


// Limpiar la tabla antes de insertar nuevos datos
globalTable.setSize(0, 1); // Ajustar el tamaño de la tabla a 0 filas y 1 columna


// Preparación de la consulta SQL con el timestamp dinámico
string sqlQuery = "WITH SignalData AS ("
                  + "SELECT *, "
                  + "SUBSTRING_INDEX(_NAME, '.', -1) AS SignalType, "
                  + "SUBSTRING_INDEX(SUBSTRING_INDEX(_NAME, '.', 3), '.', -1) AS Station, "
                  + "DATE_FORMAT(_TIMESTAMP, '%Y-%m-%d %H:%i:%s.%f') AS _FECHAHORA "
                  + "FROM OPC) "
                  + "SELECT *, "
                  + "(CASE "
                  + "WHEN _NAME = 'plcOut.Device1.Out.Plataforma_Final_Cinta' THEN 1 "
                  + "WHEN Station = 'MagFront' THEN 2 "
                  + "WHEN Station = 'Meas' THEN 3 "
                  + "WHEN Station = 'Drill' THEN 4 "
                  + "WHEN Station = 'MagBack' THEN 5 "
                  + "WHEN Station = 'Press' THEN 6 "
                  + "WHEN Station = 'Out' THEN 7 "
                  + "END) AS SignalOrderStation, "
                  + "(CASE "
                  + "WHEN SignalType = 'Plataforma_Inicio_Cinta' THEN 1 "
                  + "WHEN SignalType = 'Plataforma_Final_Cinta' THEN 2 "
                  + "END) AS SignalOrderInicioFin "
                  + "FROM SignalData "
                  + "WHERE _TIMESTAMP = :timestamp "
                  + "ORDER BY _NUMERICID, "
                  + "CASE "
                  + "WHEN _NUMERICID = 0 THEN SignalType "
                  + "WHEN _NUMERICID = 1 THEN SignalOrderStation "
                  + "END, "
                  + "CASE "
                  + "WHEN _NUMERICID = 0 THEN SignalOrderStation "
                  + "WHEN _NUMERICID = 1 THEN SignalOrderInicioFin "
                  + "END;";


// Crear la declaración preparada
Database.PreparedStatement statement = con.prepareStatement(sqlQuery);


// Vinculación del parámetro del timestamp
statement.bindParam("timestamp", selectedTimestamp, Database.DataType.VarChar);


// Ejecución de la consulta y obtención del ResultSet
Database.ResultSet resultSet = statement.execute();


// Procesamiento de los resultados y volcado en la tabla global
int rowIndex = 1; // Iniciar en la primera fila


// Obtener el número de columnas y sus nombres desde el ResultSet
int numCols = resultSet.numFields;
if (mododebug) print("Global table BD_CPLAB resized to 0 rows and " + numCols + " columns");
globalTable.setSize(0, numCols); // Ajustar el tamaño de la tabla a 0 filas y el número de columnas obtenidas


// Asignar nombres a las columnas dinámicamente
for (int colIndex = 1; colIndex <= numCols; colIndex++) {
    string colName = resultSet.getFieldName(colIndex);
    globalTable.setColHeader(colIndex, colName);
}


while (resultSet.fetchNext()) {
    // Verificar que los campos importantes no estén vacíos
    if (resultSet["id"] != "" && resultSet["_NAME"] != "" && resultSet["_NUMERICID"] != "" && resultSet["_VALUE"] != "" && resultSet["_TIMESTAMP"] != "") {
        globalTable.setSize(rowIndex, numCols); // Ajustar el tamaño de la tabla para añadir una nueva fila
        globalTable.cell(rowIndex, 1).value = resultSet["id"]; // Escribir el ID en la primera columna
        // Asignar valores de la base de datos a las celdas correspondientes dinámicamente
        for (int colIndex = 2; colIndex <= numCols; colIndex++) {
            string colName = resultSet.getFieldName(colIndex);
            globalTable.cell(rowIndex, colIndex).value = resultSet[colName];
        }
        globalTable.setRowHeader(rowIndex, "" + rowIndex); // Asignar nombre incremental a la fila
        rowIndex++; // Incrementar el índice de fila
    }
}


if (mododebug) print("Número de filas importadas: " + globalTable.numRows);


// Desconexión de la base de datos
con.disconnect();

Is there any way to execute code from this DBConnector import window, or can only SQL code be executed from here?

0 ·

question