Enduser Master/Detail Generated SQL

rbuser

Hi,

this might be a newbie question:

- Go to the 'Data' tab
- Add two tables Invoice and InvoicePositions
- Link them via InvoiceNo

In the SQL of the InvoicePositions I see no WHERE clause that would
restrain pulled data.

The only solution I can see is to produce a joined query for invoice and
invoice positions alltogether and use grouping, which is well ok for me,
but customers will definately try the more ovious way (creating two
tables and linking them) first.

Another option (not evailable to the end-user) is to create a custom
dataview where I pre-prepare the invoice and positions datasets.

Is there any solution (i.e generating params in the detail and filling
them with fields from the master)?

regards,
Magnus

nicocizik

Hi Magnus,

When linking datasets in a master-detail relationship, you are essentially
joining the two datasets on the linking field. If you would like to see the
actual SQL sent to the database you can hold the key and click on the
dataset to see what we call the Magic SQL.

It is possible to add search criteria to the detail dataset using the Search
tab in the Query Designer. This will add the WHERE clause to the SQL
displayed in the SQL tab.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Nico Cizik (Digital Metaphors) wrote:

Well, the Magic SQL of the detail shows
SELECT ... FROM ... ORDER BY

which would in my case still pull 1 million records even if I wanted to
generate one invoice.

The 1 and the infinity symbol on the link do not have any meaning, do
they besides depicting a 1:N relationship (they cannot be changed)?

Since I'm writing a DataAbstract DADE plug-in anyways for enduser
reporting on the client, is there any way to capture the links within
the DADE plu-in before opening a certain pipeline, so I can only fetch
the needed records ?


Yes, I'm aware of that, but there is no mechanism to link these search
fields to the master as far as I can see.

I try to achieve a traditional

master: select * from invoices
detail: select * from invoice_positions where invoice_id = :invoice_id

nicocizik

Hi Magnus,

Sorry, I misread your initial question.


Correct, this is a reference for an end-user to show a typical one to many
relationship between a master and detail dataset. This is just for
informational purposes.

DADE does not support the use of parameters from within the SQL code. You
will need to use the autosearch feature to add or change the search criteria
of a SQL statement in DADE. Note that this has been made much easier with
the introduction of the TdaSQLBuilder object. I would suggest spending some
time with the Developer's Guide and the TdaSQLBuilder help topics for how
these can be utilized to give you the data you need.


This would not be the typical way of gathering data using the plugin
architecture. Using AutoSearch from within DADE would be much easier
however the beauty of the plugin is that you can customize it to retrieve
data however you would like (as long as you follow the general design). I
would suggest taking a look at some of the other plugins (daADO,
daDBExpress, etc.) for examples of how these plugins are to be implemented.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Nico Cizik (Digital Metaphors) wrote:
ok this means that the end-user has to write a few lines of script to
accomplish this, thats fine...


Ok, I got the DADE plugin for my Data Abstract server sort of working,
it sees my tablename and fieldnames, but the SQL generated by
Reportbuilder lacks quotes (i.e. the table-name 'contacts' should appear
as '...FROM "contacts" contacts...' in the select statement)

When I return the table-names already quoted in Session.GetTableNames an
exception is raised that I shall not quote my tablenames. How am I
supposed to turn the table names if they require quoting (same question
for the field names)

Does Reportbuilder support quoted identifiers at all?

Thanks for the great support by the way!

Magnus

rbuser

Magnus Jungsbluth wrote:

found the source of the problem: 'contacts' does not contain any report
builder recognized invalid chars besides being not uppercase. I changed
daSQL.daContainsInvalidChars for interbase force quotes if the names are
lower or mixed case.
The plugin for data abstract is working now and I can see my data,
thanks for the help!

nicocizik

Hi Magnus,

Excellent! Glad you got it working.

If it would be alright, we would be very interested in taking a look at your
plugin to give others the ability to connect to Data Abstract and possibly
post it on our web site for download. If you would like to share the
plugin, please send a copy of it to support@digital-metaphors.com and we'll
take care of making it available.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hi,

I'd be glad to provide the source, but since my work is based on work of
others and I first have to check with them.

The plugin behaves different depending on where it is run
- Client-side: All data is pulled into in-memory datasets over the
network. This is kind of slow but works fine for end-user reporting
- Server-side: All data is retrieved locally from the database still
using the data-abstract mechanism of retrieving the data.

I'll get back to you as soon as I retrieve an answer from the other guys.