Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: FireDAC FB query: SELECT RDB$INDEX_NAME FROM RDB$INDICES; fails to open



Permlink Replies: 4 - Last Post: Oct 18, 2016 6:36 PM Last Post By: Michal Mucha Threads: [ Previous | Next ]
Michal Mucha

Posts: 6
Registered: 2/8/98
FireDAC FB query: SELECT RDB$INDEX_NAME FROM RDB$INDICES; fails to open
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 8, 2016 8:24 PM
Problem with system table query:
SELECT RDB$INDEX_NAME FROM RDB$INDICES;

Windows 10 64bit
C++ Builder XE7 Enterprise

Database server Firebird 1.56

The query fails to open. The message:
[FireDAC][DatS]-32. Variable lenght column [RDB$INDEX_NAME] overflow ...
appears and exception is thrown.

Try the code below:

TFDConnection *TmpDatabase; // created with designer
TFDTransaction *TmpTransact; // created with designer

void __fastcall TTestDm::GetIndicesLst(TStringList *indices_lst)
{
TFDQuery *wq;
String resStr, qry;

indices_lst->Clear();
wq = new TFDQuery(this);
if (wq)
{
wq->Connection = TmpDatabase;
wq->Transaction = TmpTransact;

qry = String(L"SELECT RDB$INDEX_NAME FROM RDB$INDICES;");
wq->SQL->Add(qry);
if (!TmpDatabase->Connected)
TmpDatabase->Open();
if (!TmpTransact->Active)
TmpTransact->StartTransaction();
try
{
wq->Prepare();
wq->Open(); // here the error message:
// [FireDAC][DatS]-32. Variable lenght column [RDB$INDEX_NAME] overflow ... appears
wq->First();
while (!wq->Eof)
{
resStr = wq->FieldByName(String(_T("RDB$INDEX_NAME")))->AsString.Trim();
indices_lst->Add(resStr);
wq->Next();
}
wq->Close();
wq->Unprepare();
}
catch (Exception &E)
{
TmpTransact->Rollback();
MessageDlg(E.Message, mtInformation, TMsgDlgButtons() << mbOK, 0);
wq->Close();
wq->Unprepare();
}
if (TmpTransact->Active)
{
TmpTransact->Commit();
}
delete wq;
}
}

There is nothing wrong with the query.
So why TFDQuery is getting the wrong column size? How to get around?

Regards

Michal Mucha

PS
Today I found that the query:
select RDB$USER usr, RDB$PRIVILEGE priv, RDB$RELATION_NAME arole from RDB$USER_PRIVILEGES where RDB$PRIVILEGE = 'M';

also fails. This time exception message concerns the field aliased as arole. Are all system table queries plagued with inability to determine
column size?
The query
SELECT COUNT(*) ct FROM RDB$PROCEDURES WHERE RDB$PROCEDURE_NAME = 'PlaceHereYourProcedureName';
works ok.

Regards

Michal Mucha

Edited by: Michal Mucha on Oct 9, 2016 6:40 PM

PS 2

While investigating the problem I found an article:
http://www.devsuperpage.com/search/Articles.aspx?G=2&ArtID=87548

Where the problem was discussed (and is known), and clearly there is
problem in the library, with proper decoding of length of character type columns
(bytes per character) in the system tables. Looks like the FireDAC library
supplied with C++Builder XE7 does not deal with it properly. Are there
any patches available?

Regards

Michal Mucha

Edited by: Michal Mucha on Oct 11, 2016 6:56 AM
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: FireDAC FB query: SELECT RDB$INDEX_NAME FROM RDB$INDICES; fails to open [Edit]
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 11, 2016 10:39 AM   in response to: Michal Mucha in response to: Michal Mucha
Michal Mucha wrote:

PS 2

While investigating the problem I found an article:
http://www.devsuperpage.com/search/Articles.aspx?G=2&ArtID=87548

Where the problem was discussed (and is known), and clearly there is
problem in the library, with proper decoding of length of character type columns
(bytes per character) in the system tables. Looks like the FireDAC library
supplied with C++Builder XE7 does not deal with it properly. Are there
any patches available?

Regards

Michal Mucha

Edited by: Michal Mucha on Oct 11, 2016 6:56 AM

It is not a problem in the library it is a bug in FB the the library must not be
working around. FB is lying about the character set of that data. It claims it
is UNICODE_FSS (which has a bytes per character of 3), when in reality it is
ANSI (bytes per character of 1).

I tested your code in XE7 and it worked so not sure why you are seeing this
issue (I thought it was fixed in XE6). A workaround should be to actually have
the the DB return the character set is says it is like

SELECT cast(RDB$INDEX_NAME as Varchar(67) character set UNICODE_FSS)
RDB$INDEX_NAME FROM RDB$INDICES

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)
Michal Mucha

Posts: 6
Registered: 2/8/98
Re: FireDAC FB query: SELECT RDB$INDEX_NAME FROM RDB$INDICES; fails to open [Edit]
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 15, 2016 8:35 PM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Jeff Overcash (TeamB) wrote:
Michal Mucha wrote:

PS 2

While investigating the problem I found an article:
http://www.devsuperpage.com/search/Articles.aspx?G=2&ArtID=87548

Where the problem was discussed (and is known), and clearly there is
problem in the library, with proper decoding of length of character type columns
(bytes per character) in the system tables. Looks like the FireDAC library
supplied with C++Builder XE7 does not deal with it properly. Are there
any patches available?

Regards

Michal Mucha

Edited by: Michal Mucha on Oct 11, 2016 6:56 AM

It is not a problem in the library it is a bug in FB the the library must not be
working around. FB is lying about the character set of that data. It claims it
is UNICODE_FSS (which has a bytes per character of 3), when in reality it is
ANSI (bytes per character of 1).

I tested your code in XE7 and it worked so not sure why you are seeing this
issue (I thought it was fixed in XE6). A workaround should be to actually have
the the DB return the character set is says it is like

SELECT cast(RDB$INDEX_NAME as Varchar(67) character set UNICODE_FSS)
RDB$INDEX_NAME FROM RDB$INDICES

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)

Hi Jeff,
Thank you for the explanation and the solution. Queries with casting work properly.

You write
I tested your code in XE7 and it worked so not sure why you are seeing this
issue (I thought it was fixed in XE6).
I have the FireDAC version 21.0.17707.5020 and the issue exist for that version.

You explain
... it claims it
is UNICODE_FSS (which has a bytes per character of 3), when in reality it is
ANSI (bytes per character of 1).
But there is still something I don't understand. If the FB provides wrong information that
text columns use 3 bytes per character and actually one byte per character is used then
I would expect that buffers allocated for the data received from a server (XSQLDA) would
be oversized, and thus could easily accommodate shorter strings. (Another problem then
would be with proper conversion of such stings to two byte unicode characters used
by the system.) But the error message concerns columns sizes.

Anyway thank you once again for the hint. The solution works fine.

Regards

Michal Mucha
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: FireDAC FB query: SELECT RDB$INDEX_NAME FROM RDB$INDICES; fails to open [Edit]
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 16, 2016 8:13 PM   in response to: Michal Mucha in response to: Michal Mucha
Michal Mucha wrote:
You explain
... it claims it
is UNICODE_FSS (which has a bytes per character of 3), when in reality it is
ANSI (bytes per character of 1).
But there is still something I don't understand. If the FB provides wrong information that
text columns use 3 bytes per character and actually one byte per character is used then
I would expect that buffers allocated for the data received from a server (XSQLDA) would
be oversized, and thus could easily accommodate shorter strings. (Another problem then
would be with proper conversion of such stings to two byte unicode characters used
by the system.) But the error message concerns columns sizes.

No, the XQLSVar does not return the character length, what it returns is the
number of bytes in its buffer and the character set of that data. To create the
TField's size property the calculation is buffer size / bytes per character. So
in the case of the wrong character set for system table char fields this reduces
by 1/3 the number of characters it can hold. So instead of getting 67
characters for the field ( 67 buffer bytes / 1 char per byte) you get 22 (67
buffer bytes divided by 3 bytes per char). The internal buffer is then setup to
hold the max number of characters that field can contain.

Anyway thank you once again for the hint. The solution works fine.

Regards

Michal Mucha

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)

Michal Mucha

Posts: 6
Registered: 2/8/98
Re: FireDAC FB query: SELECT RDB$INDEX_NAME FROM RDB$INDICES; fails to open [Edit]
Click to report abuse...   Click to reply to this thread Reply
  Posted: Oct 18, 2016 6:36 PM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Jeff Overcash (TeamB) wrote:
Michal Mucha wrote:
You explain
... it claims it
is UNICODE_FSS (which has a bytes per character of 3), when in reality it is
ANSI (bytes per character of 1).
But there is still something I don't understand. If the FB provides wrong information that
text columns use 3 bytes per character and actually one byte per character is used then
I would expect that buffers allocated for the data received from a server (XSQLDA) would
be oversized, and thus could easily accommodate shorter strings. (Another problem then
would be with proper conversion of such stings to two byte unicode characters used
by the system.) But the error message concerns columns sizes.

No, the XQLSVar does not return the character length, what it returns is the
number of bytes in its buffer and the character set of that data.
Now its clear.

To create the
TField's size property the calculation is buffer size / bytes per character. So
in the case of the wrong character set for system table char fields this reduces
by 1/3 the number of characters it can hold. So instead of getting 67
characters for the field ( 67 buffer bytes / 1 char per byte) you get 22 (67
buffer bytes divided by 3 bytes per char). The internal buffer is then setup to
hold the max number of characters that field can contain.

Anyway thank you once again for the hint. The solution works fine.

Regards

Michal Mucha

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)


Thanks

Michał Mucha
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02