<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<a class="moz-txt-link-abbreviated" href="mailto:michael.vancanneyt@wisa.be">michael.vancanneyt@wisa.be</a> wrote / napísal(a):
<blockquote cite="mid:alpine.DEB.2.00.1212171039200.6939@fsb.wals.be"
type="cite"><br>
<br>
On Mon, 17 Dec 2012, LacaK wrote:
<br>
<br>
<blockquote type="cite"><a class="moz-txt-link-abbreviated" href="mailto:michael.vancanneyt@wisa.be">michael.vancanneyt@wisa.be</a> wrote /
napísal(a):
<br>
<blockquote type="cite"><br>
<br>
On Mon, 17 Dec 2012, LacaK wrote:
<br>
<br>
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">
<blockquote type="cite">2) The simple way to get the
schemas would be to simply 'select ... schemaname||'.'||relname as
table_name ... from pg_stat_user_tables. This would be a change ONLY to
the sql in pqconnection. (If worst comes to worst, I dare say could
create my own pqconnection derivative with this change.)
<br>
</blockquote>
Hm, if you want get also schema_name then you should use this approach:
<br>
sqlquery1.SchemaType:=stTables;
<br>
sqlquery1.Open;
<br>
and in loop fill TStrings using
sqlquery1.FieldByName('schema_name')+'.'+sqlquery1.FieldByName('table_name')
</blockquote>
<br>
At present, the schema isn't read into the query (so we have to change
the db connection), and the above code would have to be put into
TSQLConnecion, where (1) it would only apply to stTables, and (2) would
not work if any database species did not return the schema. Probably
to do this it would be necessary to be able to select multiple fields
in the AReturnField of GetDBInfo, all of which would be far more
complicated than what I suggested as a "simple" solution!
<br>
</blockquote>
*Yes it will be more complicated.
<br>
<br>
I did short comparasion in Delphi and in case of BDE table names are
returned as "schema.table" in case of ADO,DBX only "table" without
schema is returned. So again inconsistency between various DB client
technologies.
<br>
</blockquote>
<br>
IMHO all the more reason to use 2 different fields, and keep table in 1
field.
<br>
<br>
<blockquote type="cite">If others agree, IMO we can do it like
this:
<br>
1. use PG specific system view query, where we add SCHEMA_TABLE_NAME
column (other columns like SCHEMA_NAME, TABLE_NAME, TABLE_TYPE etc
remains) which will be as you suggested: schemaname||'.'||relname
<br>
</blockquote>
<br>
I would use 2 separate fields. This way the table field always only
contains
<br>
the table. People interested in the schema, can read the schema field.
<br>
</blockquote>
Yes. May be, that I was not clear. My suggestion was 3+ fields in
query:
<br>
SCHEMA_NAME, TABLE_NAME, SCHEMA_TABLE_NAME (SCHEMA_NAME || '.' ||
TABLE_NAME)
<br>
<br>
But original question AFAIU was about: what should GetTableNames return
(schema query can return multiple columns, but what column use when
fill list of table names)?
<br>
GetTableNames return list of table names into TStrings.
<br>
Now only TABLE_NAME is returned. John requested, that also schema name
should be prefixed (if there is any)
<br>
</blockquote>
<br>
Ah, that was not clear to me :-)
<br>
<br>
But why the SCHEMA_TABLE_NAME ? It's redundant information, as the
<br>
information is present in SCHEMA_NAME and TABLE_NAME anyway ?
<br>
</blockquote>
<b>Yes is redundant<br>
<br>
</b>
<blockquote cite="mid:alpine.DEB.2.00.1212171039200.6939@fsb.wals.be"
type="cite"><br>
If you need the schema name, why not just access the SCHEMA_NAME field
?
<br>
<br>
</blockquote>
<b>SCHEMA_TABLE_NAME only as helper for GetDBInfo (look how is
implemented ;-))<br>
<br>
</b>
<blockquote cite="mid:alpine.DEB.2.00.1212171039200.6939@fsb.wals.be"
type="cite">A different story is the GetTableNames call, which will -
presumably - use
<br>
the above information.
<br>
<br>
</blockquote>
<b>Yes it is point of story ... GetTableNames call GetDBInfo which
calls SetSchemaInfo and in loop fills TStrings with provided
AReturnField Field.<br>
<br>
</b>
<blockquote cite="mid:alpine.DEB.2.00.1212171039200.6939@fsb.wals.be"
type="cite">There I think some extra options are needed:
<br>
<br>
TSchemaOption = (soPrependSchemaName,soIncludeSystemObjects);
<br>
TSchemaOptions = set of TSchemaOption;
<br>
<br>
Procedure GetTableNames(List : TStrings; Options : TSchemaOptions =
[]);
<br>
<br>
</blockquote>
<b>possible, but introduces incompatibility with Delphi, where
GetTableNames is declared like now in FPC (GetTableNames(List:
TStrings; SystemTables: Boolean) + other overloads)<br>
So SystemTables is there already ...<br>
<br>
</b>
<blockquote cite="mid:alpine.DEB.2.00.1212171039200.6939@fsb.wals.be"
type="cite">I am not interested in schema information,</blockquote>
<b>ok, then we can leave things as are now and do only:<br>
1. update PG GetSchemaInfoSQL to correctly fill schema_name (now it is
always blank)<br>
2. users who are interested in schema name must use instead of
GetTableNames own but simple loop:<br>
</b> sqlquery1.SchemaType:=stTables;
<br>
sqlquery1.Open;
<br>
while not sqlquery1.Eof do begin<br>
List.Items.Append(sqlquery1.FieldByName('schema_name')+'.'+sqlquery1.FieldByName('table_name'));<br>
sqlquery1.next;<br>
end;<br>
<br>
<b>-Laco.</b><br>
<blockquote cite="mid:alpine.DEB.2.00.1212171039200.6939@fsb.wals.be"
type="cite"> but I can imagine some people are
<br>
(the original poster, obviously). The above caters for everyone and
keeps
<br>
backwards compatibility.
<br>
<br>
Michael.<br>
<pre wrap="">
<hr size="4" width="90%">
_______________________________________________
fpc-pascal maillist - <a class="moz-txt-link-abbreviated" href="mailto:fpc-pascal@lists.freepascal.org">fpc-pascal@lists.freepascal.org</a>
<a class="moz-txt-link-freetext" href="http://lists.freepascal.org/mailman/listinfo/fpc-pascal">http://lists.freepascal.org/mailman/listinfo/fpc-pascal</a></pre>
</blockquote>
<br>
</body>
</html>