Select into using TOracleQuery

[LGEAI Developers]

Select into using TOracleQuery

We are using Delphi 7 and DOA 4.0.3 and PL/SQL Dev 9.0.2.6.0 under Win XP SP2 .

In a Delphi button we need to get fields to pre-fill a TOracleDataSet that is already in Insert mode. The test SQL (which worked fine) we used as a model in PL/SQL Dev tool looks like this:

Declare
dbeSSN Varchar(9);
dbeLastName Varchar(30);
dbeAcn Varchar(9);
Begin
SELECT first_report_of_injuries.ssn, first_report_of_injuries.ee_l_name
INTO dbeSsn, dbeLastName
FROM first_report_of_injuries, claims
WHERE first_report_of_injuries.fri_number = claims.fri_fri_number
AND claims.agency_claim_number = &dbeAcn;
dbms_output.put_line(dbeSSN);
dbms_output.put_line(dbeLastName);
End;

The TOracleQuery looks like this:

SELECT FIRST_REPORT_OF_INJURIES.SSN, FIRST_REPORT_OF_INJURIES.EE_L_NAME
INTO :dbeSsn, :dbeLastName
FROM FIRST_REPORT_OF_INJURIES, CLAIMS
WHERE FIRST_REPORT_OF_INJURIES.FRI_NUMBER = CLAIMS.FRI_FRI_NUMBER
AND CLAIMS.AGENCY_CLAIM_NUMBER = :dbeAcn

Button code looks like this:

procedure TFRIA0033frm.btnGetExistingInfoClick(Sender: TObject);
begin
FRIA0033dm.oqGetFroiInfo.Close;
FRIA0033dm.oqGetFroiInfo.SetVariable('DBEACN', dbeAcn.Text);
FRIA0033dm.oqGetFroiInfo.Execute;
end;

When button is clicked the TDBedit fields contain no data and there are no error messages. Does DOA not transfer the selected fields back to the TDBEdit fields on the form?

 

[Marco Kalter]

A "select into" statement is PL/SQL (not SQL). Therefore you need to embed the statement within an anonymous PL/SQL Block:

begin
  select field1, field2
    into :var1, :var2
    from table;
end;

I'm not sure why there are no error messages, but this is probably the solution.

 

[LGEAI Developers]

Thank you for your prompt reply!

Begin
SELECT FIRST_REPORT_OF_INJURIES.SSN, FIRST_REPORT_OF_INJURIES.EE_L_NAME
INTO :dbeSsn, :dbeLastName
FROM FIRST_REPORT_OF_INJURIES, CLAIMS
WHERE FIRST_REPORT_OF_INJURIES.FRI_NUMBER = CLAIMS.FRI_FRI_NUMBER
AND CLAIMS.AGENCY_CLAIM_NUMBER = :dbeAcn;
End;

After adding the Begin and End in SQL property of TOracleQuery component, we get the following errror message:

'ORA-01008: not all variables bound'

Any ideas?

 

[Marco Kalter]

In that case you need to declare the :dbeSsn, :dbeLastName, and :dbeAcn variables. Either at design-time, or at run-time through DeclareVariable.

 

[LGEAI Developers]

Thank you for your reply.

The help on Declare Variable was very informative. We declared variables at design time. GetVariable retrieved the requested values. Button code was modified as follows:

FRIA0033dm.oqGetFroiInfo.Close;
FRIA0033dm.oqGetFroiInfo.SetVariable('DBEACN', dbeAcn.Text);
FRIA0033dm.oqGetFroiInfo.Execute;
dbeSsn.Text := FRIA0033dm.oqGetFroiInfo.GetVariable('DBESSN');
dbeLastName.Text := FRIA0033dm.oqGetFroiInfo.GetVariable('DBELASTNAME');

Thanks again.

 

[LGEAI Developers]

Our attempt to prefill TDbedit controls from a TQuery is not working the way we thought it would. The dataset and dbedit controls are in insert mode and the GetVariable assignment populates the control and the user can see the data. If you save the record the contents of the dbedit controls are saved to the database. But if a user goes back and clicks on any control before saving the previous contents that were prefilled from the query are now erased. The dbedit controls expects data to be entered in the fields. Is there any way to modify the behavior of the control so that a user either can set the focus to any dbedit control (via tab key or mouse click) that was prefilled so the user does not erase the data contents and also allow the user to over write prefilled data in controls before saving?

 

[Marco Kalter]

I'm not sure I understand the situation. Can you send me a little demo project (including any tables) to demonstrate things.

 

[ldsandon]

1) I see no reason to use a SELECT...INTO... Can't you use a simple select and read values via the Field() method instad of GetVariable()?
2) How do you fill the dbedit? You should write into the linked TField.

 

[LGEAI Developers]

Thank you for your replies.

Delphi code completion allows us to specify Text which says type TMaskedText.

As it turns out the Text property of a dbedit control appears to be unidirectional. What we mean by that is that you can set the text field to a value in either insert or edit mode but it does not replace the Tfield value at the data set level unless you do a save. If you change the Tfield value at the dataset level the changes will be reflected in the dbedit control and values will not disappear if the control gets focus, even if you are still in insert mode. Here is the final resolution:

FRIA0033dm.oqGetFroiInfo.Close;
FRIA0033dm.oqGetFroiInfo.SetVariable('DBEACN', dbeAcn.Text);
FRIA0033dm.oqGetFroiInfo.Execute;
dbeSsn.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('DBESSN');
dbeLastName.Field.Value := FRIA0033dm.oqGetFroiInfo.GetVariable('DBELASTNAME');

Thanks all.