neat - use extfile instead of OVRDBF

[gcraill]

Re: neat - use extfile instead of OVRDBF

If you are designing a system from scratch, then you should be able to design it to take advantage of of SQL.

Exactly our situation ... it's old and SQL was not as snappy back yonder when it was first created. But it keeps on trucking ...

[bryce4president]

Re: neat - use extfile instead of OVRDBF

Ok, so on older systems you may need overrides. But so far I have not seen an example on newer systems where it will be neccessary or even better.

[kitvb1]

Re: neat - use extfile instead of OVRDBF

Even though almost everything can be done with static SQL, it is not possbile to use host variables for libraries/schemas and/or files/tables/Views and fill them on the fly.
With embedded SQL you either need dynamic SQL or an override.

Birgitta

Oh geez... I hate to be the one to say this... but that's not quite right Biggi.
Now that I've said it - quick, run, save yourselves ... the sky is falling.

Done it... coming in ChangefIT v2.31

But as for member... you'd still need an override.

** edit ** Darn, Biggi is right - I just read the last sentence
You can only do it with dynamic SQL.

You can do:

select ?, ? from ?
where ? = :hostvar

But you can't use ?, you must trim a field's contents.

[kitvb1]

Re: neat - use extfile instead of OVRDBF

Just 'cos my last post was as clear as mud... this is what I meant:

This is allowed:

dSqlStmt = 'SELECT ' + UsedFld + ' from ' +         
          UsedTbl  + ' where ' + UsedFld + ' = ' + 
          q + %trim(srchsf) + q;

[B.Hauser]

Re: neat - use extfile instead of OVRDBF

Something like this should work either:

dSqlStmt = 'Values(Select ?, ? From ' + %Trim(MyFile) +
                    ' where ? = ? +
                    ' Fetch first row only) into ?, ?';

Exec SQL prepare DynSQL from :dSqlStmt;
Exec SQL Execute DynSQL using :FldName1, :FldName2
                              :CondFld1, :CondFld2,
                              :CondVal, :Result1, Result2;

The advantage of the example abouve is, the file can be set once (Prepare) , but the execute can be performed several times with different hostvariables.

The example is not tested, because I've currently no acces to an iSeries.

Birgitta

[kitvb1]

Re: neat - use extfile instead of OVRDBF

Unluckily the SQL compiler might** give you an error on that: the compiler said: ?? huh ?? wtf do you want ??

Translation: Parameter marker used in incorrect position.

It's what I tried first - **except that I used a ? for the table name as well.

[B.Hauser]

Re: neat - use extfile instead of OVRDBF

... it seems a SQLDA (Descriptor Area) must be added, because of the parameter markers within the select-clause. Because they are used dynamically (and the data type and length may vary), pointer will be returned and the information must be returned through the descriptior area.
... I did not try it out
... and I would not program in this way

Birgitta

[Ben]

Re: neat - use extfile instead of OVRDBF

One advantage I see over OVRDBF is that it allows for a convenient way to externalize the assignment of 15 to 'LBIQUOTES/OEHDORDM' if 15 is the key to a reference file and the value returned from the data field is 'LBIQUOTES/OEHDORDM'. Doing something like this provides for a way to change assignments without recompiling the code. One example is where the reference file is in a developer's library and the values returned referenced files in the developer's lib. A CL containing the OVRDBF would have to be changed and recompiled for the tests. This would mitigate a potential disaster where one of the files was omitted from the test library. At startup time, the RPG code would not find the file in the test library and would fail rather than picking up the file from a library lower in the list. It may be a less then likely scenario with better library list practices, but you see the idea.