Subject Re[2]: [IBO] View, filter or ... ?
Author Helen Borrie
At 12:24 PM 04-03-01 +0200, you wrote:
>That's because right here
>I'm programatically building the query (like q.sql.text='select * from
>table where (somecondition)') and instead of using parambyname I'd
>rather use something like q.sql.text=format('select * from table where
>(someconditions) and (month=%d) and (year=%d)',[month,year]).

I think you should definitely avoid it. Have you ever known a monster like this to **work** ?

>This is
>exactly what I was trying to avoid but it seems there's no easy way
>around it... This way, I only need to pay attention that all queries
>have these "where" clauses :)

Assuming you know the table you are querying, you can make the query something like

select Field1, Field2, Field3 from TheTable (or a joined set)

Then, at runtime, pick up the conditions and make them into SQLWhereItems, which you define in the OnPrepareSQL event.

>BTW, I've got some 20 forms and I wonder what's the best solution to
>use queries: put one query in a DataModule (which I'm not using right
>now; I've put there only the connection,transaction... and the queries
>in each form) or put in
>every form a different query. What's the impact in
>performance/resources ?

Normally, it's convenient to have one transaction for each form and then just have whatever queries you want being controlled in a single, form-based work unit. I'm not a fan of mdi and will do anything to avoid my forms are usually very few. I like datamodules and will use one for each form; others prefer to put all of the assets for a particular part of the application together on the form. Mainly, it's a matter of taste; although each datamodule does use a form handle. (Back in Win16 days and 8 megs of RAM, we used to care about such things!)

> >>Note: the problem with this is that only one user could use the
> >>database at one time...or more users using only the selected month and
> >>year. In fact this is way I'm trying to find an alternative :)
>HB> Why ?
>The problem was that creating a view as "select ... where month=3 and
>year=2001" whould force all users to use only that month,year or
>dropping the view and creating another one (which can't be done if the
>view is already in use by somebody else, of course).

Oh my!! that is NOT what views are for! and you *certainly do not* drop and create objects in client/server. Forget view altogether, except for a static, perhaps denormalized view of some data that a lot of people use frequently.

However, that said, a view is just like a table. In fact, tables in client/server *are* views, since there are no physical tables at all. If you want to try a view - or a suite of views - which can be parameterized at run-time, just like a select on a table, try it. Each user can select the view s/he wants, select or enter some parameters, and then you can apply SQLWhereItems to it, just like a select on a table.

Oh, and disavow yourself of the belief that if one user is "using" a view, others can't. In client/server, users never physically touch the database; and InterBase's versioning engine takes care of who is looking at what.

All for Open and Open for All
InterBase Developer Initiative ยท