Checking to see if a varaible is bound

[phelton]

I am trying to convert some code from using a TQuery to use the TOracleDataSet. And I am stuck.

I need to check to see if the variables are bound, and if not, prompt the user to enter a value. And I can't find anything about how to check to see if a variable is bound. What is the command I am looking for?

Here is the code I was using on the TQuery:

function SetQueryParameters(qry:tquery) : boolean;
var
i:integer;
// value :array[0..1] of string; -- declared at top
begin
result := false;
for i:=0 to qry.params.count-1 do
begin
if not qry.params.bound then
begin
if not InputQuery('Enter Parameter',qry.params.name, value) then
exit;
qry.params.asstring := value;
end;
end;
result := true;
end;

Thank you in advance for your help!

------------------
Phyllis Helton
Information Services Manager
The JESUS Film Project
www.jesusfilm.org

 

[Marco Kalter]

Here is the equivalent:

function SetQueryVariable(Qry: TOracleDataSet): Boolean;
var i: Integer;
    Value: array[0..10] of string;
begin
  Result := False;
  for i := 0 to Qry.VariableCount - 1 do
  begin
    if VarIsEmpty(Qry.GetVariable(i)) then
    begin
      if not InputQuery('Enter Parameter', Qry.VariableName(i), Value[i]) then Exit;
      Qry.SetVariable(i, Value[i]);
    end;
  end;
  Result := True;
end;

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

 

[phelton]

Thank you so much!! I was looking everywhere to find the command to see if the variable was bound yet, and couldn't find it. I appreciate your help

 

[Jens Fudickar]

Hello Marco,

did you have any code to parse the statement dynamicly at runtime to recreate all unknown bind variables.

Greetings
Jens

 

[phelton]

I have just finished spending hours and hours working on getting code to find the variables so I can create them. I'm not very experienced in coding with Delphi, so forgive any bad coding, and feel free to make recommendations. This code will search for variables in your SQL statement, and then prompt the user for values where necessary. The arrays are global, so the variable values are remembered between the queries. I wanted to have the values of variables remembered between queries, so if I run a query with one set of variables, then a query with another set, then the first set again, the values I entered in are still remembered. The code for finding the variables in the SQL was given to me to Jim McDaniel, aka the Toadman. I still had to make some adjustments to work with my code, but want him to get the credit for his work.

I hope this helps you.

Type
TVarRec = Record
var_name, var_value: String;
var_idx: integer;
End;

Var
ds_variables, ds_var_values: Array[0..10] Of TVarRec;
i_var_count : integer;

Function SetDataSetVariables(ds: TOracleDataSet) : Boolean;
Var
i_set_var_values_idx, i_var_values_idx, i_variable_idx, i_var_name_idx:
Integer;
Begin
Result := False;
FindVariables(ds) ;
ds.DeleteVariables;

For i_set_var_values_idx := 0 To i_var_count - 1 Do
// for each variable that is set, loop through the values to find matches
With ds_variables[i_set_var_values_idx] Do
if var_name '' then
Begin
ds.declareVariable(var_name, otString);
For i_var_values_idx := 0 To 10 Do
If var_name = ds_var_values[i_var_values_idx] .var_name Then
var_value := ds_var_values[i_var_values_idx] .var_value;
End;

For i_variable_idx := 0 To ds.VariableCount - 1 Do
Begin
If Not InputQuery('Enter Parameter: ',
replace(ds_variables[i_variable_idx] .var_name, '_', ' ') ,
ds_variables[i_variable_idx] .var_value)
Then Exit;
ds.SetVariable(i_variable_idx, ds_variables[i_variable_idx] .var_value) ;
ds_var_values[i_variable_idx] := ds_variables[i_variable_idx];
End;
Result := True;
End;

Function FindVariables(ds: TOracleDataSet) : boolean;
Var
s_query_text : String;
i_sql_string_idx :
Integer;
Mode : Char; // S - String, V - Variable, C - Comment
quoted_string : boolean;
VarName, Char_Type,
EndCharacters : String;
Begin
s_query_text := ds.SQL.Text;
i_var_count := 0;
Mode := 'S';
char_type := 'String';
EndCharacters := '';
quoted_string := false;
result := false;

s_query_text := Replace(s_query_text, #9, ' ') ; // #9 is tab
For i_sql_string_idx := 1 To Length(s_query_text) Do
Begin
If ((s_query_text[i_sql_string_idx] = #39) And (char_type 'Comment') )
Then // #39 is single quote
quoted_string := Not quoted_string
Else
If Not quoted_string Then
Case Mode Of
'S':
Begin
If (s_query_text[i_sql_string_idx] = ':') Then
Begin
i_var_count := i_var_count + 1;
Mode := 'V';
char_type := 'Variable';
VarName := '';
End;
If (s_query_text[i_sql_string_idx] = '/') And
(i_sql_string_idx < length(s_query_text) )
And (s_query_text[i_sql_string_idx + 1] = '*') Then
Begin
Mode := 'C';
char_type := 'Comment';
EndCharacters := '*/';
End;
If (s_query_text[i_sql_string_idx] = '-') And
(i_sql_string_idx < length(s_query_text) ) And
(s_query_text[i_sql_string_idx + 1] = '-') Then
Begin
Mode := 'C';
char_type := 'Comment';
EndCharacters := #13#10; // line feed and return
End;
End;
'V':
Begin
If (i_sql_string_idx = length(s_query_text) ) Or (Not
(s_query_text[i_sql_string_idx] In ['a'..'z', 'A'..'Z',
'0'..'9', '_', '#', '$'] ) ) Then
Begin
ds_variables[i_var_count - 1] .var_Name := varName;
Mode := 'S';
char_type := 'String';
End
Else
VarName := VarName + s_query_text[i_sql_string_idx] ;
End;
'C':
If (s_query_text[i_sql_string_idx] = EndCharacters[1] ) And
(i_sql_string_idx < length(s_query_text) ) And
(s_query_text[i_sql_string_idx + 1] = EndCharacters[2] )
Then
Begin
mode := 'S';
char_type := 'String';
End;
End;
End;
End;

------------------
Phyllis Helton
Information Services Manager
The JESUS Film Project
www.jesusfilm.org

 

[Marco Kalter]

To find bind variables in a piece of SQL, you can use the FindVariables function in the Oracle unit:

function FindVariables(const SQL: string; IncludeDuplicates: Boolean): TStringList;

The SQL parameter is the SQL you want to analyze (duh). If a bind variable appears more than once in the SQL, the IncludeDuplicates parameter controls if it also appears more than once in the result. The result is a TStringList instance (which you must free later). The Objects property of the result contains and integer value that indicates the position in the SQL where the corresponding variable is located.

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