[fpc-pascal] Re: GetTableNames in TSQLConnection / Postgresql

[John]

On 12/21/2012 10:37 PM, Tomas Hajny wrote:
> On Fri, December 21, 2012 11:59, Reinier Olislagers wrote:
>> On 18-12-2012 10:49, Tomas Hajny wrote:
>>> On Tue, December 18, 2012 09:35,
>>> michael.vancanneyt-0Is9KJ9Sb0A at public.gmane.org wrote:
>>>> On Tue, 18 Dec 2012, John wrote:
>>>>> you can't!  That brings me back to the original point that either
>>>>> gettablenames has to operate on a known schema only (so you can supply
>>>>> the
>>>>> schema afterwards), or it has to supply the schema with the table
>>>>> name.
>>>> I would opt for the latter, hence the soPrependSchemaName.
>>> Isn't something like a FullTableName property/method
>>> (connection-specific)
>>> a better solution (more universal, also catering for differences in
>>> construction of the full name with different database engines)?
>> No, because then you won't be able to get metadata using the same
>> mechanism for all connections.
>> If we create a FullTableName method (or property, with corresponding
>> tablenames collection etc), we should do the same for stored procedures,
>> views, and any other object a db supports.
>> Switching dbs would then be very problematic.
>>
>> The current mechanism has the advantage that it is extendable for
>> various dbs without too much effort... and is like other mechanisms
>> (e.g. the ADO.Net method of doing things).
> I must have missed something, because my thoughts went exactly into the
> direction of simplifying switching from one database to another. Indeed,
> doing the same for all database objects would be IMHO beneficial too, but
> that's another story. My proposal was exactly this - whatever naming the
> respective DB provider requires natively when addressing objects, it would
> be reflected in this property (implemented in DB provider specific way).
> If it is 'SCHEMA_NAME.TABLE_NAME' with Oracle, so be it. If it is just
> 'TABLE_NAME' with MySQL - OK, no problem. Obviously, simple TableName
> field would still exist, but use of that field would then become DB
> provider specific (meaning that you could combine it with another
> selection condition like schema when querying the database dictionary or
> not depending on your needs). Obviously, accessing (querying) information
> about the database dictionary data is then DB provider specific too, but
> completely transparent to the end user.
>
> Then again, I might have missed something. I know Oracle fairly well
> (hopefully ;-) ), but certainly not all the other supported DB engines
> equally so (some of them not at all, with some of them just bare basics).
>
> Tomas
>
What you are suggesting is more-or-less one of the options I originally 
suggested, so I guess I have to agree.

Another option that seems quite simple to me would be to add a 
'GetSchema' method, and then add a parameter Schema: String  = '' to 
each of the other get... methods.  Each dbConnection could then ignore 
or implement that parameter as appropriate, with each of the get... 
methods would then operating only on a single schema, when the db used 
schemas and one was supplied.  It would mean iterating through the 
schemas if you wanted a complete list, but usually I would operate only 
on one schema in a given application, and even if not I think that would 
be better that having to separate them from a combined list.  I suppose 
a final rule could be that if a db used schemas and no schema was 
supplied, THEN the full, schema qualified list would be given.  Having 
said all that, the option that was suggested, I think by Michael, will 
also work, though I think a little more clumsily, at least for my use case.

(Finally, when I started all this, I thought I had to fix the 
sqlconnection components, not realising I could get the metadata 
directly from sqlquery.  As I was planning to derive a descendant of 
TSQLQuery anyway, as long as the schema data is filled in, which I 
understand has already been done, then I can work from there.)

This will probably be my last chance to post for a few days, so Merry 
Christmas to all those who celebrate it!

cheers,
John Sunderland