question

Borja Lorenzo avatar image
0 Likes"
Borja Lorenzo asked Borja Lorenzo commented

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.
1728036642539.png

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

FlexSim 24.1.1
guidatabaseglobaltableimport datadinamically value
1728036642539.png (149.7 KiB)
· 2
5 |100000

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

1 Answer

Jason Lightfoot avatar image
0 Likes"
Jason Lightfoot answered Borja Lorenzo commented

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

· 3
5 |100000

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