SQL bind variable find and replace

[mridley]

Is there any way to know which table.column a found bind variable belongs to - so I can then replace a found bind variable with a correct literal value in the actual SQL statement?

 

[Marco Kalter]

In the DML statements generated by the TOracleDataSet for inserts and updates, the column name and bind variable name are the same.

I'm not sure if this is what you meant though.

------------------
Marco Kalter
Allround Automations

 

[mridley]

Sorry Marco:

We Can currently use the FindVariables function to return a list of bind variables in a SQL statement.
What if a user wants you to supply potential values for the bind variables in a SQL statement? You have to know at least what column in the SQL statement each bind variable refers to first before you can try and search for potential values from the database.

Was wondering if there is any easy way we can do find bind variables AND additionally know which column each bind variable references.

i.e.
select *
from atable
where col1 = :a
and col2 = :b

Find bind variable :a, which references column col1.
Find bind variable :b, which references column col2.

I you know which column a bind variable belongs to, then you at least have some start on being able to search and supply a user with potential values for the bind variable.

 

[Marco Kalter]

If it's your SQL statement, and therefore your bind variable names, then you can simply use the convention that the variable name matches the column name.

If the SQL and bind variable names are supplied by the end-user, then you have a nice challenge. You would need to parse the SQL statement to find the usage of the bind variable. There is no functionality in Direct Oracle Access for this.

------------------
Marco Kalter
Allround Automations

 

[mridley]

OK. Thanks Marco.
Didn't think DOA did this but just thought I would check anyway.
Congratulations on a very nice component anyway - very easy to use.