[bug_fixed] GetCatalogNames returns nothing...

[Choppers]

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?

[mdaems]

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

[Choppers]

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.

[mdaems]

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

[Choppers]

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]

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.

[mdaems]

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

[Choppers]

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().

[mdaems]

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

[Choppers]

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.

[mdaems]

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

[Choppers]

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

Choppers

[Michael]

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 , so nobody encountered this bug ...

Good Night!

M.

[mdaems]

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

Mark

[Michael]

perfect