| Subject | Re: [firebird-support] Re: CAST() string as BLOB for use as a memo field in VFP |
|---|---|
| Author | Helen Borrie |
| Post date | 2007-11-19T22:20:42Z |
At 02:29 AM 20/11/2007, you wrote:
Write a view that returns the string from the dynamic data. Include the pertinent key field(s) in the view and hey presto! you have a virtual table that you can join to or subselect from.
In Fb 2 and above you can also vary this option by using a derived table instead of a view.
A stored procedure is self-defining as to the sets it operates on. The input parameters you define for it include the values for the WHERE clauses of the SELECT statements within the SP. Although selectable stored procedures are handy for fetching virtual sets, the main purpose of executable sql (PSQL) is for database consistency, i.e., providing a single intrinsic point through which complex related DML operations (often many of them in an interdependent suite) are executed, regardless of the calling client. In Foxpro you don't have anything equivalent.
Triggers are modules of executable sql that "belong" to a table or view, that run whenever the pertinent DML (insert, update, delete) is performed on that table or view. Again, Foxpro has no equivalent.
And, all that said, never ignore the fact that all operations in Firebird occur inside transactions that isolate one bunch of work from another. This area is often ignored by people using desktop utilities like Foxpro and Access as the front-end to external database engines. One transaction can't see the uncommitted work of other transactions and, except for transactions with Read Committed isolation, one transaction (call it A) can't see work that another transaction committed *after* transaction A began.
./heLen
>Hi HelenIt would take a lot of logic and function calls to unravel. In relational databases you keep data items separate and use queries to put them together in the required ways when they are needed.
>
>--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:
>> The above example (assuming NoFld is a BIGINT) would go something like:
>
>Thanks a lot for the complete example.
>
>
>> 'Invoice no. ' ||
>> RTRIM(CAST (NoFld as char(19))) ||
>> ' dtd. ' ||
>> CAST (EXTRACT (DAY from DtFld) as char(2)) || '/' ||
>> CAST (EXTRACT (MONTH from DtFld) as char(2)) || '/' ||
>> CAST (EXTRACT (YEAR from DtFld) as char(2)) ||
>> ' Party ' ||
>> PartyNameFld
>
>Now, is it possible to have such a string stored in a separate
>mNarrations table and converted to a (textmerged?) string...
>
>>> 'Invoice no. 123 dtd. 19/11/20007
>> >Party A.B.C. and Co.'
>
>in the returned VARCHAR on the fly just as the SELECT statement is
>executing. If so I would love to join the mNarrations table to the
>invoice table and lo presto the narrations are there.
Write a view that returns the string from the dynamic data. Include the pertinent key field(s) in the view and hey presto! you have a virtual table that you can join to or subselect from.
In Fb 2 and above you can also vary this option by using a derived table instead of a view.
>> >SELECT MySP() AS mNarration FROM InvTable WHERE ...No. A function is a device that operates on supplied arguments directly and returns a scalar result. A procedure operates on data and optionally takes input parameters and optionally returns *a set*. In Firebird we have "selectable" SPs that return multi-row sets and "executable" SPs that return a single-row set.
>
>> Not like that. You can't execute the SP by selecting it from the
>table. ;-)
>
>Oh! SP is not used like one would a FireBird UDF
>In a VFP UDF I have to have all the Parameters passed to it, it doesInput arguments for SPs are optional. If input arguments are defined, they take parameters and you have to pass valid parameters. As to whether the SP "knows which is the current record being evaluated", you indicate here a lack of understanding of the difference between a UDF and a stored procedure.
>not know which record the SELECT is on. Is it so with FireBird SP, or
>does SP know which is the current record(s) being evaluated by
>FireBird and use the values straight (to generate such and more
>complicated narration as in above examples)
A stored procedure is self-defining as to the sets it operates on. The input parameters you define for it include the values for the WHERE clauses of the SELECT statements within the SP. Although selectable stored procedures are handy for fetching virtual sets, the main purpose of executable sql (PSQL) is for database consistency, i.e., providing a single intrinsic point through which complex related DML operations (often many of them in an interdependent suite) are executed, regardless of the calling client. In Foxpro you don't have anything equivalent.
Triggers are modules of executable sql that "belong" to a table or view, that run whenever the pertinent DML (insert, update, delete) is performed on that table or view. Again, Foxpro has no equivalent.
And, all that said, never ignore the fact that all operations in Firebird occur inside transactions that isolate one bunch of work from another. This area is often ignored by people using desktop utilities like Foxpro and Access as the front-end to external database engines. One transaction can't see the uncommitted work of other transactions and, except for transactions with Read Committed isolation, one transaction (call it A) can't see work that another transaction committed *after* transaction A began.
./heLen