On Dec 10, 2007, at 3:57 PM, Mike McGonagle wrote:

On 12/10/07, Hans-Christoph Steiner <hans@eds.org> wrote:

(What is the "hold" inlet, by the way?)

It's a hot inlet that has been misspelled...

It would be really nice to be able to have inlets for the sql placeholders, but I couldn't think of a clean way to do it. We could try the dynamic allocation like you say here, I don't know if I have a specific objection to it, but it could get weird. Since the SQL inlet has to be cold to support the comma hack, then these dynamic inlets shouldn't be hot since they are the right of a cold inlet. But that's not a big deal.

And if you want placeholders to have inlets, then we are back to putting the SQL into the object creation arguments, like:

[sqlite insert into table (id, name) values (:id, :name)]

While I don't think it is a bad idea, it does restrict a particular instance of a database object to only a single type of message, and it would have to be done at creation time of the object.

Should we allow both methods of entering SQL? One done at creation time, the other one done on the fly?

Also, I don't know if any objects do this kind of dynamic inlet creation after the object itself has been instantiated. It might not even be possible. I figure that you can easily make a subpatch or abstraction around the SQL message generation, then manage the inlets there. Perhaps not ideal, but it's not difficult to do and follows existing practice.

I would think that an object having the ability to change its inlets on the fly would NOT be possible, as the inlets may change so much, as to lose a connection when it is recreated.

OR, could we possibly do something like this

[sqlite :id(f) :name(s)]

Which would create an instance with 4 inlets, the first 'hot' inlet would accept all the control messages, the next (x number) would represent the bound inlets for the placeholders, and the last would be the inlet for the incoming SQL statement. Would it be an error if the incoming SQL statement didn't have the same placeholders defined in it? Or should it be allowed to operate as any other?

This kind of thing could be done in a separate "query" object, like what's included in the "sqlwrappers", and [sqlite]/[psql] would still represent the database itself. Having the objects structured this way means that Matju could write his [expr] SQL query too, and they would work with the different SQL database objects (sqlite, psql, mysql).

As for the SQL binding, I was thinking it would happen inside of the object, binding the placeholder names to the selectors. As long as there wasn't any new input on the SQL inlet, then the SQL statement wouldn't need to be recompiled.

My thought too...

Another question I just thought of it how to handle returning multiple results from s single query. If it followed the [textfile] style, then you would have to bang to get each individual result, then keep banging until you get a bang on the status/second outlet, meaning the query was done. I think this could work.

This is already what I have been doing. Basically, as we are working now, the SQL would get input on the right inlet, then if any binding were needed, that would be done, and when the next bang occurs, the query is submitted, and the first result set is returned. Any subsequent bang would return the next result set. And when the result sets have been exhausted, it would send a bang out the "status" outlet to indicate the sets are done.

Cool, that sounds like a good idea. I probably got it from you. :) Maybe you are already doing this, but a query could also send a message out of the status outlet to say how many results were found (i.e. [results 5(). This number could be routed to trigger an [until] or other such behaviors. (using the completion bang to stop [until] probably makes more sense though).

.hc

Mike

.hc

----------------------------------------------------------------------------

There is no way to peace, peace is the way. - A.J. Muste

--
Peace may sound simple—one beautiful word— but it requires everything we have, every quality, every strength, every dream, every high ideal.
—Yehudi Menuhin (1916–1999), musician