J John Naylor Active Member Licensed User Longtime User Jun 12, 2020 #1 I've recently been using JRDC2 quite a bit and it is excellent but I often find I need to reuse parameters in queries. An entry in the JRDC2 config.properties query may read something like Code: Copy Select mainstock.id, mainstock.name, mainstock.description From mainstock Where (mainstock.id = ?) Or (mainstock.name Like Concat('%', ?, '%')) Or (mainstock.description Like Concat('%', ?, '%')) So I would have to pass three parameters when only two are actually required to pull off the following alternative SQL: Copy Select mainstock.id, mainstock.name, mainstock.description From mainstock Where (mainstock.id = @p1) Or (mainstock.name Like Concat('%', @p2, '%')) Or (mainstock.description Like Concat('%', @p2, '%')) I know I could pass multiple parameters (reusing the same one multiple times) like this B4X: Copy Dim cmd As DBCommand = CreateCommand("Select_Stock", Array(ID, SearchString, SearchString)) but that gets to be a nightmare very quickly with a more complex query. I also know I could just write a stored procedure and use that instead buy hey, this is a wish list eh?
I've recently been using JRDC2 quite a bit and it is excellent but I often find I need to reuse parameters in queries. An entry in the JRDC2 config.properties query may read something like Code: Copy Select mainstock.id, mainstock.name, mainstock.description From mainstock Where (mainstock.id = ?) Or (mainstock.name Like Concat('%', ?, '%')) Or (mainstock.description Like Concat('%', ?, '%')) So I would have to pass three parameters when only two are actually required to pull off the following alternative SQL: Copy Select mainstock.id, mainstock.name, mainstock.description From mainstock Where (mainstock.id = @p1) Or (mainstock.name Like Concat('%', @p2, '%')) Or (mainstock.description Like Concat('%', @p2, '%')) I know I could pass multiple parameters (reusing the same one multiple times) like this B4X: Copy Dim cmd As DBCommand = CreateCommand("Select_Stock", Array(ID, SearchString, SearchString)) but that gets to be a nightmare very quickly with a more complex query. I also know I could just write a stored procedure and use that instead buy hey, this is a wish list eh?
Alexander Stolte Expert Licensed User Longtime User Jun 12, 2020 #2 +1 GymReaper said: but that gets to be a nightmare very quickly with a more complex query. Click to expand... I know the struggle with it... I then used StoredProcedures.
+1 GymReaper said: but that gets to be a nightmare very quickly with a more complex query. Click to expand... I know the struggle with it... I then used StoredProcedures.
J John Naylor Active Member Licensed User Longtime User Jun 12, 2020 #3 Alexander Stolte said: +1 I know the struggle with it... I then used StoredProcedures. Click to expand... I'm happy I'm not on my own ;-)
Alexander Stolte said: +1 I know the struggle with it... I then used StoredProcedures. Click to expand... I'm happy I'm not on my own ;-)
Erel B4X founder Staff member Licensed User Longtime User Jun 14, 2020 #4 The JDBC API doesn't support it. We can parse the query ourselves but it is not a simple change.