JDBC query with dynamic variables

💬 Queries and Resources
1

Hi,
in a JDBC SQL I am running the following SQL and all is working fine:
SELECT dtdodt, dtrefx
FROM myfile
WHERE DATE(SUBSTR(CHAR(dtdodt), 1, 4) || '-' ||
SUBSTR(CHAR(dtdodt), 5, 2) || '-' ||
SUBSTR(CHAR(dtdodt), 7, 2)) >= DATE(2024-12-31)
FETCH FIRST 200 ROWS ONLY

However when I replace the date with the variable like you can see next I am getting an error:
SELECT dtdodt, dtrefx
FROM myfile
WHERE DATE(SUBSTR(CHAR(dtdodt), 1, 4) || '-' ||
SUBSTR(CHAR(dtdodt), 5, 2) || '-' ||
SUBSTR(CHAR(dtdodt), 7, 2)) >= DATE({{ Dateperiod6 }})
FETCH FIRST 200 ROWS ONLY

It seems like the variable is NULL. But the variable contains exactly the same: 2024-12-31.

I think the SQL cannot access the variable content.

I am new in Retool. Can someone guide me?

Thanks in advance

Dynamically query resources

3

I just wanted to update you on what I did more.
I have created a Transformer like you can see on the right of the printscreen:

image
image1033×344 38.7 KB

The JDBC SQL is now only what you have on the left of the image: {{ AR_Aging_trf.value }} which contains exactly the SQL statement that I want to run:
image

This is still not working.

I may be wrong but I think that what is being sent to the JDBC driver is this and the ? is not replaced:
image

Like I said I am new in Retool and I would really appreciate if any one can give me any help.

Best

4

Hi @zepaulosilva,

Thanks for reaching out & welcome to Retool!

For the transformer approach, I would not expect this to work in Retool due to prepared statements. For example, prepared statements prevents you from putting the table name inside double curly brackets.

You can try disabling prepared statements on the resource settings, but we generally don't recommend this as it could have security risks and it could break other queries for this resource that are already working.

With prepared statements enabled, we're more likely to get your original query with just the dynamic variable working. What is {{Dateperiod6}}? If it is a Retool variable, you'll need to change it to {{Dateperiod6.value}}. I'd try that next and see if it works or if the error is any different. For troubleshooting, you could also try {{ '2024-12-31'}} to see if that works before we explore other syntax changes

5

Hi Tess,

Thank you for reaching out!

I did what you suggested and it still doesn't work:

image
image754×410 30.9 KB

I also did your other suggestion that did not work:

image
image779×406 29.8 KB

Let me remind you that this is a JDBC SQL.

Thanks in advance for all the support you can give me.

6

Thanks for the updated screenshots! What is the error message? Do you have any logs for this query on the resource side (outside of Retool)?

1 Like
7

You can see the error below:

image
image375×930 63.5 KB

Based on this "Use of parameter marker or NULL not valid" I think the issue is that what is being sent to the JDBC driver is the actual parameter marker ? instead of the variable content.

Could this be what's happening?

Thanks again for your help.

8

Hmm, since it happens when hardcoded, I think it's probably a syntax issue over sending null or ? instead of the date :thinking:

What about switching it to DATE({{'"2024-12-31"'}})? If that works, you may need to change the dynamic version to: DATE({{'"'+Dateperiod6.value+'"'}})

9

I tried what you suggested but it didn't work.

10

@Tess , what do you think I can do?
I don't think I'm doing anything wrong but I might.

11

Hi @zepaulosilva,

This is a tricky one :thinking: especially since I don't have the exact same resource to test.

Were you able to track down any external logs? Did you confirm if you have prepared statements enabled on the resource settings?

It may help to discuss this live if you can join office hours on Tuesday

12

I have sent you the error messages I get from the server.
As for the Resource, yes, I have prepared statements enabled.
image

I am travelling and I will not be able to join you for a live discussion on Tuesday. Maybe Thursday or Friday.

Do you know if anyone is using JDBC queries with parameter markers to be replaced?

13

I don't see any known issues with JDBC queries with parameter markers :thinking: we'd love to take a closer look tomorrow if you can join office hours (at 11:00 am PT)!

14

Hey @zepaulosilva

The issue seems to be with the transformer not properly fetching the value for the SQL query. To resolve this, consider using a JavaScript query (jsquery) to fetch the value first. Run the jsquery, and upon its success, trigger the SQL query. Instead of relying on the transformer data, use the data from the jsquery in your SQL query. Because some time on page load transformer not get data perfectly and passing null. Please let me know if this is issue or not.

3 Likes
15

I was not able to join. Sorry.
I was thinking if this can be related with the elements tree of the app:
image
Or with the variable not being correctly setup:

image
image885×529 55.3 KB

Let me just put here the screen and the Query so you have a better idea of what I have:
image
image917×793 24.1 KB

image

16

Hey @zepaulosilva

Please verify the data type of the date field in the database (e.g., string, ISO format, or another type), and then adjust the condition accordingly.

4 Likes
17

Thank you @WidleStudioLLP for your help.

It turns out though that the issue is that the variable replacement works different when inside the DATE.
The following SQL errors out:
image

While this one doesn't and it returns the correct result:
image

It may be that @Tess had suggested it before but if she did I didn't get it.

Anyway I still don't know if this is the intend behavior but I was able to make my query to work.

Thank you all for your support

1 Like
18

Thanks so much for sharing how you got this working!

19

Hi again,

I'm sorry but I'm facing more issues with the parameters replacement in the SQL JDBC.

This is what I'm doing:

image
image1059×286 42.7 KB

This is the Prepared SQL:
INSERT INTO S10A4EBB.XD1492BFDV.Z1OSHVLG
(SLUSER, SLDATE, SLTIME, SLSHPA, SLSHDE, SLDCID, SLDESC, SLZ1DOCN, SLFRDT, SLTODT, SLACCS, SLVALI, SLLRDE)
SELECT 'PROTELO',
(SELECT DIGITS(DECIMAL(CURRENT DATE, 8, 0)) FROM SYSIBM.SYSDUMMY1), (SELECT DIGITS(DECIMAL(CURRENT TIME, 6, 0)) FROM SYSIBM.SYSDUMMY1),
SDSHPA,
(SELECT shdesc FROM S10A4EBB.XD1492BFDV.srosha
WHERE shshpa = {{ ShipAgent_Collection.selectedItem.shipcode }}),
SDDCID, SDDESC, SDZ1DOCN, SDFRDT, SDTODT, 'IN', 'N', {{ ShipAgentInOut_NotesTextInput.value }}
FROM S10A4EBB.XD1492BFDV.z1oshd
WHERE sdshpa = {{ ShipAgent_Collection.selectedItem.shipcode }}

And this is the prepared SQL with the variables replaced:
INSERT INTO S10A4EBB.XD1492BFDV.Z1OSHVLG
(SLUSER, SLDATE, SLTIME, SLSHPA, SLSHDE, SLDCID, SLDESC, SLZ1DOCN, SLFRDT, SLTODT, SLACCS, SLVALI, SLLRDE)
SELECT 'PROTELO',
(SELECT DIGITS(DECIMAL(CURRENT DATE, 8, 0)) FROM SYSIBM.SYSDUMMY1), (SELECT DIGITS(DECIMAL(CURRENT TIME, 6, 0)) FROM SYSIBM.SYSDUMMY1),
SDSHPA,
(SELECT shdesc FROM S10A4EBB.XD1492BFDV.srosha
WHERE shshpa = D842),
SDDCID, SDDESC, SDZ1DOCN, SDFRDT, SDTODT, 'IN', 'N', Notes
FROM S10A4EBB.XD1492BFDV.z1oshd
WHERE sdshpa = D842

Son in Retool all is looking correct but then when it runs on the server it throws the following error: Use of parameter marker or NULL not valid.

I am unsure about why the variable replacement is not working.
I have replaced the variable in this statement: WHERE sdshpa = {{ ShipAgent_Collection.selectedItem.shipcode }} and the SQL still doesn't work.

20

Hmm :thinking: so the second, hardcoded query works, but if you put the dynamic variables in, it doesn't work?

If hardcoding WHERE sdshpa = {{ ShipAgent_Collection.selectedItem.shipcode }} doesn't help, I'm guessing the issue is with {{ ShipAgentInOut_NotesTextInput.value }}

Does it help to add single quotes around the input, when you hover over the input does it evaluate to Notes, how is the dynamic input evaluated in external logs, etc?