"BareFeetWare" schrieb
> On 13/11/2010, at 10:33 AM, Olaf Schmidt wrote:
> >> If you have code (in either environment) that is
> >> looping or grabbing one result and sticking it in a
> >> second query, then your approach is probably flawed.
> >
> > As you say: "probably" ... because "it depends" ...
> > (maybe your application wants to show "details"
> > in a delayed fashion - e.g. when a Node in a
> > Treeview gets "expanded").
>
> Good example. At the point where the user requests expanded
> details (eg by clicking on a button), that obviously kicks
> off application code. As far as its interaction with the
> database, simplistically, it would have to do something
> like get the key values in the current row, check whether
> it exists in the details set, get the detail data. What I'm
> saying is that this should be done as one SQL call, which
> most likely doesn't require any branching logic within that call.
>
> So it should look something like this, with just one SQL call:
>
> array filteredDetailResults = execQuery(
> select "Product Code", "Product Name", Quantity, Price
> from "Invoice Items"
> join "Invoice List" on "Invoice Items"."Invoice Number" = "Invoice
List"."Invoice Number"
> left join "Item List" on "Invoice Items"."Product Code" = "Item
List"."Product Code"
> where "GUI Selection Flag" = 1
> )
> show filteredDetailResults
Nah ... come on - Joins are common things - don't
know which DB-Application-developer (who worth
its salt) is not making use of it (instead of falling back
to loops).
And in my App I would have put the above SQL
into a View-Definition beforehand (that's another
common thing, most DB-Engines share and which
is well-portable).
Ending up with something like that...
SQL = "Select * From InvoiceDetailView Where CustomerID = " & _
CurrentNode.ID
' get a new Recordset-Instance, containing the set, defined above
Set Rs = Cnn.OpenRecordset(SQL)
' GUI-Refresh over DataBinding against the Rs-Instance
Set MyGUIWidget.DataSource = Rs
--------------
And in case I'd have put the above (already View-based)
SQL-Code into a CommandObject first, then I would only
need to set the Command-Parameter (typed, and without
fiddling around with String-Concats).
InvoicesCmd.SetInteger !CustomerID, CurrentNode.ID
Set MyGUIWidget.DataSource = InvoicesCmd.Execute
Olaf
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
