mORMot Open Source

delphinium

Member

Registered: 2013-11-05

Posts: 11

SynDBExplorer: ERROR: relation "tablename" does not exist - ZEOS-PG

greeting everyone,

I am at 12 - SynDBExplorer and added FireDAC and ZeosDBO into the connection selection. It seems Zeos-PG metadata does not return the exact table names (sometime double quotes needed) from postgresql.
Due to postgresql is quite particular in that I have modified SynDBZeos to always use double quotes and normal case. It is just a workaround (the least changes in mormot that my limited brain can think of) and hopefully the gurus can
share their solution though i think the best way is Zeos/ZDBC metadata returns the exact case with/without double quotes.

SynDBZeos.pas
procedure TSQLDBZEOSConnectionProperties.GetTableNames(var Tables: TRawUTF8DynArray);
var meta: IZDatabaseMetadata;
    res: IZResultSet;
    TableTypes: Types.TStringDynArray;
    n: integer;
begin
  if GetDatabaseMetadata(meta) then begin
    ...
    ... 
    while res.Next do
      if fDBMS = dPostgreSQL then  // added line: 341
        AddSortedRawUTF8(Tables,n, '"' + SynUnicodeToUtf8(res.GetUnicodeString(3)) + '"')  // line 342 
      else
        AddSortedRawUTF8(Tables,n,UpperCase(SynUnicodeToUtf8(res.GetUnicodeString(3))));  // default
    SetLength(Tables,n);
  end else
    inherited;
end;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: SynDBExplorer: ERROR: relation "tablename" does not exist - ZEOS-PG

I'm afraid it will break the whole ORM core of mORMot, which uses GetTablesNames() and expect them without quotes...

We need a fix at SynDBExplorer level.
And also something to fix the case-sensitivity problem.

I'll take a look at this.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: SynDBExplorer: ERROR: relation "tablename" does not exist - ZEOS-PG

This conversion to uppercase was a problem.
Please take a look at http://synopse.info/fossil/info/d2bce50700

Could you please explain the particularities of PostgreSQL about the quotes?

delphinium

Member

Registered: 2013-11-05

Posts: 11

Re: SynDBExplorer: ERROR: relation "tablename" does not exist - ZEOS-PG

Sorry for my english, let me try

Table/field/function names are identifiers in PG (http://www.postgresql.org/docs/current/ … DENTIFIERS). PG treats identifiers case insensitively when not quoted
(PG folds them to lowercase internally), and case sensitively when quoted; hence it is advisable to always use lowercase during schema creation and you are safe. Comes back to our case, unless Zeos metadata
can return exactly the schema namely with/without quotes, we might get the "ERROR: relation "xx" does not exist", so what I did was just a workaround with the assumption that the identifier letters' retrieved by
Zeos metadata engine were correct and treat all with quotes hence case-sensitive check. It was not the best way as I had stated and because of that i reported the issue in hope that expert like you could comes
in . On your uppercase removed fix, the issue is still there for identifier created with quotes. Hmpph, I think the issue is more to Zeos than mormot, maybe you can just put as a note in the documentation to inform
user. Just to share with you, FireDAC metadata returns exactly the schema with/without quotes and don't have the issue (SynDBExplorer); and err.. SynDBFireDac.GetTablenames does store quotes into the tables var
parameter (if quotes are used in the schema).

Last edited by delphinium (2013-11-20 18:45:44)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: SynDBExplorer: ERROR: relation "tablename" does not exist - ZEOS-PG

I suspect I got it...
I put a note - see http://synopse.info/fossil/info/4b0ec45947

Note that you can specify the SQL table name when registering the external TSQLRecord table to the mORMot server model.
I suspect this is where you can put your quotes, if needed.

For SynDBExplorer we may need to quote the table names.
Perhaps http://synopse.info/fossil/info/1c830102ba (and http://synopse.info/fossil/info/e93758fc1e BTW) are enough.

delphinium

Member

Registered: 2013-11-05

Posts: 11

Re: SynDBExplorer: ERROR: relation "tablename" does not exist - ZEOS-PG

For SynDBExplorer we may need to quote the table names.
Perhaps http://synopse.info/fossil/info/1c830102ba (and http://synopse.info/fossil/info/e93758fc1e BTW) are enough.

Yup changes in both procedure are enough for SynDBExplorer. I have slightly change it and your input is always welcome.

SynDB.pas 
function TSQLDBConnectionProperties.SQLTableName(const aTableName: RawUTF8): RawUTF8;
var
  BeginQuoteChar, EndQuoteChar: RawUTF8;
  bUseQuote: boolean;
begin
  BeginQuoteChar := '"';
  EndQuoteChar := '"';
  bUseQuote := False; // flag to always use quote for tablename - 
  case fDBMS of
    dPostgresql:
      if PosEx('.', aTablename) = 0 then  // if schema.identifier format - then dont quote
        bUseQuote := True;
    dMySQL: begin
      BeginQuoteChar := '`';  // backtick/grave accent
      EndQuoteChar := '`';
    end;
    dJet: begin  // ,dMSSQL begin // MSSQL can SET IDENTIFIER ON to use doublequotes 
      BeginQuotechar := '[';
      EndQuoteChar := ']';
    end;
  end;

  if bUseQuote and (PosEx(BeginQuoteChar, aTableName) = 0) then
    result := BeginQuoteChar + aTableName + EndQuoteChar
  else if (PosEx(' ', aTableName) > 0) and (PosEx(BeginQuoteChar, aTableName) = 0) then  // not quoted tablename with space
    result := BeginQuoteChar + aTableName + EndQuoteChar else
    result := aTableName;
end;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: SynDBExplorer: ERROR: relation "tablename" does not exist - ZEOS-PG

See http://synopse.info/fossil/info/c0e6012df8

EgonHugeist

Member

From: Germany

Registered: 2013-02-15

Posts: 190

Re: SynDBExplorer: ERROR: relation "tablename" does not exist - ZEOS-PG

Hi AB,

i was reading this post.. Your're hardly checking against Spaces and ofther WildCards.

Another Example:

table XyZ, xyz, XYZ

if the 3 table do have differnt columns it leads to pain again. Not only spaces are required to check. You should also check mixed cases etc.

Zeos has already an automation object for the identifiers:

IZConnection.GetMetadata.GetIdentifierConvertor: IZIdentifierConvertor;

  IZIdentifierConvertor = interface (IZInterface)
    ['{2EB07B9B-1E96-4A42-8084-6F98D9140B27}']

    function IsCaseSensitive(const Value: string): Boolean;
    function IsQuoted(const Value: string): Boolean;
    function Quote(const Value: string): string;
    function ExtractQuote(const Value: string): string;
  end;

That interface is able to decide if a identifer-quoting is required and you simply can use this api for ALL drivers instead of this case () checking.

Hope it helps.

Michael

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: SynDBExplorer: ERROR: relation "tablename" does not exist - ZEOS-PG

Nice.

Our code above is at SynDB.pas level, so it should be abstracted from DB library....

Thanks for the feedback.
See http://synopse.info/forum/viewtopic.php?pid=9119#p9119