Report Builder / temporary tables

rbuser

Hi there,

I'd like to setup a common model for my reports using DADE for MySQL.

I'd like the user to select what he wants to print. So my first idea was
to build a temporary table where he can select.(This way many users can
select different records to print at the same time).
This solution doesnt seem to work since ReportBuilder can't "see" the
temporary table.

So now my idea is to build a table with a unique name that would be
called from Report Builder data section.

exemple : I build my report with a real table called 'selected'.
Then at run time, I change selected to 'select192_168_1_102' for
instance. This table will have the same structure, of course.

so I have 3 questions :
1- Is there a way to force Report Builder to work with temporary tables
(I doubt this is possible)
2- If not, is my solution a correct one to do that ?
3- and if so, how can I change the report builder "table" names
dynamically ?

very best regards,

Etienne

digitalmetaphors

ReportBuilder can work with temp tables. The first option is to try to
customize the DADE plugin to retrive the temporary table information from
the database. See the GetTableNames method in your DADE plugin. Is there
such a call on the connection object for MySQL that can return temp tables?
If not, perhaps you can fire a SQL query to get back this information from
the database.

In order to change the table names, you'll have to use daMetaDataManager and
call gMetaDataManager.Clear. This will remove the known tables and field
names. Then you'll have to repopulate them the next time the query tools are
used. Perhaps a better solution is to simply use two TppDataDictionary
components instead of fooling with the meta data cache. One dictionary
should be used for the temp tables, and the other for the real tables. This
way you can switch the one that is connected at runtime. Then you'll have to
access the dataviews that get created and change their table names. For
example, you'll have to use this demo to extract the TdaSQL object from each
dataview and change the TdaSQL.TableName and TableAlias property to match
that found in the data dictionary for the real tables. Save a template as
ascii with a dataview on it just so you can see the objects as they are
stored and where the table names need to be changed on which objects. For
example, here is a simple dataview I copied from an ADO query dataview:
Change the TdaField and TppField object to use the new table names.


object daADOQueryDataView1: TdaADOQueryDataView
Report = ppReport1
UserName = 'Query_Customer'
Height = 313
Left = 10
NameColumnWidth = 105
SizeColumnWidth = 35
SortMode = 0
Top = 10
TypeColumnWidth = 52
Width = 200
AutoSearchTabOrder = 0

object Customer: TppChildDBPipeline
AutoCreateFields = False
UserName = 'Customer'

object ppField1: TppField
FieldAlias = 'Custno'
FieldName = 'CustNo'
FieldLength = 0
DataType = dtDouble
DisplayWidth = 10
Position = 0
TableName = 'customer'
end
end
...
object daSQL1: TdaSQL
DatabaseName = 'ADOConnection1'
DataPipelineName = 'Customer'
LinkColor = clMaroon
MaxSQLFieldAliasLength = 25
SQLText.Strings = (
'SELECT customer.CustNo, customer.Company, '
' customer.Addr1, customer.Addr2, '
' customer.City, customer.State, '
' customer.Zip, customer.Country, '
' customer.Phone, customer.FAX, '
' customer.TaxRate, customer.Contact, '
' customer.LastInvoiceDate'
'FROM customer customer')
SQLType = sqSQL1

object daField1: TdaField
Alias = 'Custno'
DataType = dtDouble
DisplayWidth = 10
FieldAlias = 'Custno'
FieldLength = 0
FieldName = 'CustNo'
SQLFieldName = 'CustNo'
TableAlias = 'Customer'
TableName = 'customer'
TableSQLAlias = 'customer'
end
....
end
end;

Cheers,

Jim Bennett
Digital Metaphors

rbuser

First of all thanks a lot for this really precious mail !

I think I'd better try to use the temporary tables because my aim is to
be able to change the fields at runtime... and if I understood your
code, I end up with a "code limited" number of fields.

1- Most of all I only need one temporary table (to select the records to
print). [Maybe there's a better method to let the user select what to
print but I really don't think so].

2- Now I hope I can find a way to use the tempo tables since they dont
appear when I design the report (?!?)

3- I don't understand why is the table name not enough ? (if I make a
design with a hard table then try to switch to the temporary table (with
the same name), it doesnt work.

Thanks a lot for your help !!!!!!!!!!!!!!!!

best regards,

Etienne

In article <3ed4d50a$1@dm500.>, "Jim Bennett \(Digital Metaphors\)"

digitalmetaphors

Create a little test app. All it should do is load a list of table names
(including the temp tables) into a Memo on a form. This is what you'll have
to add in the DADE plugin to be able to use the temp tables as real tables
in DADE.


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Salut!

Why not having another table with one extra column for user identification.
That way you can create views (or selects) that returns the required fields based on
a specific user. If you base your solution on CREATING/DROPING table you will have to
deal with metadata update, and in some RDMS this could be a problem in terms of
locking and resource management, not to mention that checking for an object existance
is not as trivial as checking for a rowset existance and the user must have
CREATE/DROP rights.

I am using the "extra column" aproach and in some cases 2 extra columns when I need
to maintain the rows ordered for a given user.

HTH,
Clément