[bug_fixed] GetCatalogNames returns nothing...

Forum related to MS SQL Server

Moderators: gto, cipto_kh, EgonHugeist

Choppers
Fresh Boarder
Fresh Boarder
Posts: 19
Joined: 01.02.2008, 18:42

[bug_fixed] GetCatalogNames returns nothing...

Postby Choppers » 24.03.2008, 18:58

Using 6.6.1 - beta, I can successfully connect to a MSSQL 2005 server, but GetCatalogNames returns empty.

I can get a list using SQL, but it's not my preferred option.

Does anyone know if this *should* work, or am I barking up the wrong tree?

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby mdaems » 24.03.2008, 23:42

Hi Choppers,

Can you provide some more details?
Which Protocol are you using?
Does your code pass by the TZMsSqlDatabaseMetadata.GetCatalogs procedure? There 'exec sp_databases' should be used to get the catalog names from your database.

Mark
Image

Choppers
Fresh Boarder
Fresh Boarder
Posts: 19
Joined: 01.02.2008, 18:42

Postby Choppers » 25.03.2008, 00:20

Hi Mark,

I'm connecting using ado. GetCatalogs appears to run fine, but with no results. I double checked by changing

ResultSet.GetStringByName('TABLE_CAT')

to

ResultSet.GetString(0)

but still no results.

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby mdaems » 25.03.2008, 00:38

Hi,

Do you know what statement is sent to the server exactly? (eg. by using TZSQLMonitor) What resultset do you get from the server when you send this query using an other query tool? Are the column names exactly the same?

Is the 'ResultSet.GetString(0)' statement actually executed after your change? If not this means the query didn't return a resultset at all.

Mark
Image

Choppers
Fresh Boarder
Fresh Boarder
Posts: 19
Joined: 01.02.2008, 18:42

Postby Choppers » 25.03.2008, 02:54

I''ll try with the TZSQLMonitor tomorrow, but I can confirm that 'ResultSet.GetString(0)' returns nothing, as I also tried adding quotes to it e.g

List.Add('""+ResultSet.GetString(0)+'"');

with no result. Using SQL, I get correct answer of 5.

Choppers.

Choppers
Fresh Boarder
Fresh Boarder
Posts: 19
Joined: 01.02.2008, 18:42

Postby Choppers » 25.03.2008, 11:59

Hi Mark,

Running with a TZSQLMonitor suggests nothing is sent to the server when i run GetCatalogNames, only the connection statement is logged :

2008-03-25 09:41:11 cat: Connect, proto: ado, msg: CONNECT TO "bob" AS USER ""

I can't trace through the code as the MsSQL server is not reachable from my machine, but so far, i've worked out that the line

'if Assigned(AdoRecordSet) then'

in 'TZAdoDatabaseMetadata.GetCatalogs', is as far as it gets, 'AdoRecordSet' seems to be returning unnassigned from 'AdoOpenSchema'


I'm not sure where to go from here, but i'm willing to try anything you can suggest.

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby mdaems » 25.03.2008, 13:44

Have a look at TZAdoDatabaseMetadata.SchemaSupported function. I have no idea if the ADO 'Catalogs' schema is supported for Mssql. That would explain why AdoRecordSet is unassigned.

You're sure it's not the result of GetSchema's you want? Or doesn't that work either? Or does it give an other kind of data. (I don't know how Schema's and catalogs are used in mssql, so I'm just guessing.

Mark
Image

Choppers
Fresh Boarder
Fresh Boarder
Posts: 19
Joined: 01.02.2008, 18:42

Postby Choppers » 25.03.2008, 15:30

I've checked GetSchemaNames, with the exact same results.

The schema is supported, as 'TZAdoDatabaseMetadata.SchemaSupported' returns ok.

i've found an exception is raised in 'TZAdoDatabaseMetadata.AdoOpenSchema', on :

' Result := (GetConnection as IZAdoConnection).GetAdoConnection.
OpenSchema(Schema, Restrictions, EmptyParam);'

although this disappears if i rem out the previous line :

Restrictions := BuildRestrictions(Schema, Args);

which then gives me a valid list of databases for GetCatalogNames().

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby mdaems » 25.03.2008, 22:11

Strange... Is it possible the error is raised because the Args array is empty, resulting in a High(Args) value of -1? Most other procedures calling AdoOpenSchema do provide Arguments, maybe that explains why most users have no trouble.

Mark
Image

Choppers
Fresh Boarder
Fresh Boarder
Posts: 19
Joined: 01.02.2008, 18:42

Postby Choppers » 26.03.2008, 11:15

Mark,

As you suggest, the error is probably caused by the High(Args) value of -1.

In 'TZAdoDatabaseMetadata.BuildRestrictions', if I change the line

Result := VarArrayCreate([0, High(Args)], varVariant);

to

Result := VarArrayCreate([0,0], varVariant);

then problem is resolved. I don't know exactly why though, -1 is a valid parameter for this, perhaps it's my ADO?, As you say, no-one else seems to have this problem.

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby mdaems » 26.03.2008, 21:50

Are you sure VarArrayCreate([0, -1], varVariant); is a valid call?
What about starting th procedure like this :

Code: Select all

function TZAdoDatabaseMetadata.BuildRestrictions(SchemaId: Integer;
  const Args: array of const): Variant;
var
  SchemaIndex: Integer;
  I: Integer;
begin
  Result := Null;
  if High(Args) = -1 then
    Exit;
  SchemaIndex := FindSchema(SchemaId);
  if SchemaIndex = -1 then
    Exit;


Maybe nobody has problems because nobody uses this with ADO?

Mark
Image

Choppers
Fresh Boarder
Fresh Boarder
Posts: 19
Joined: 01.02.2008, 18:42

Postby Choppers » 26.03.2008, 22:12

I've checked my mdac, with Microsofts own tools, and it checks out ok.

I've been working with this solution:

Code: Select all

function TZAdoDatabaseMetadata.AdoOpenSchema(Schema: Integer; const Args: array of const): ZPlainAdo.RecordSet;
var
  Restrictions: Variant;
begin
  Result := nil;
  if not FSupportedSchemasInitialized then
    InitializeSchemas;
  if not SchemaSupported(Schema) then
    Exit;
  try
    if High(Args)>-1 then
      Restrictions := BuildRestrictions(Schema, Args);
 


which is similar to your suggestion. Without being able to step through the code, I can't tell if VarArrayCreate([0, -1], varVariant) is valid, although googling it suggests it's fairly common :wink:

Choppers

Michael
ZeosLib's Handyman :o)
ZeosLib's Handyman :o)
Posts: 189
Joined: 15.08.2005, 16:08
Location: Wehrheim
Contact:

Postby Michael » 19.09.2008, 22:56

Hi Mark, hi Choppers,

quite half a year passed since the last post in this thread but suddenly I encountered the same problem tonight while I tried to retrieve the schemas and catalogs of my test database (SQLExpress 2005). Both method calls (GetSchemaNames() and GetCatalogNames()) ran into an exception :? . By implementing the fix, posted by Mark both retrievals ran perfectly. So I would suggest to put Marks solution into trunk and 6.6 patch branch (as backport).

@Mark: Suppose you were right: Nobody must have used these methods with ado, before :oops: , so nobody encountered this bug ... :|

Good Night!

M. :zzz:
:prog2: Use the source, dude!

[align=right]..::.. ZeosLib on SourceForge ..::..[/align]

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby mdaems » 20.09.2008, 10:20

Committed to SVN. (Testing Rev. 253, Trunk 255)
Will be moved tot 6.6 patches in about a week.

Mark
Image

Michael
ZeosLib's Handyman :o)
ZeosLib's Handyman :o)
Posts: 189
Joined: 15.08.2005, 16:08
Location: Wehrheim
Contact:

Postby Michael » 20.09.2008, 19:56

perfect :up:
:prog2: Use the source, dude!



[align=right]..::.. ZeosLib on SourceForge ..::..[/align]


Return to “MS SQL Server”

Who is online

Users browsing this forum: No registered users and 1 guest