Problems applying updates to SQL Azure

[ulfandersson]

Hi all,

we get errors when applying updates from a provider component to a SQL Azure database. First it throws "invalid object name 'sysindexes'" and then a DBXError: 65535.

We use Delphi 2007 components for the database access code, and the latest dbExpress drivers, 4.80.

It appears that one must use 'sys.indexes' on SQL Azure instead of 'sysindexes'. However, we have no references to 'sysindexes' in our own code, so this must come from elsewhere.

We were hoping that we could use the normal Delphi database components in combination with the latest dbExpress driver, and avoid rewriting our database access code by using the Azure-supported components in Delphi XE.

Is it correct that this should work, or do we need to use the XE support for Azure?

I greatly appreciate any help on this matter!

Regards,
Ulf Andersson

[ulfandersson]

Here is more information concerning the error:

In unit DBXDelegate:

function TDBXMorphicCommand.ExecuteQuery: TDBXReader;
begin
Open;
Result := FCommand.ExecuteQuery; <--- Exception
end;

with the following data for FCommand:

FCommandType: 'DBX.MetaData'
FText: 'GetIndexes "ourDataBaseName"."ourLoginName"."ourDBTable"'
FLastReader: nil
FOpen: True
FPrepared: False
FParameters: nil
FDBXContext: some pointer value

Is GetIndexes a function call in dbexpsda40.dll ? If so, could it be this function which uses "sysindexes" in the SQL statement it creates?

(I understand now that it's a difference between Windows Azure and SQL Azure. Windows Azure appears not to be relevant for the connection to the SQL Server database in the cloud, SQL Azure, so those components will not help.)

Regards,
Ulf Andersson

[ulfandersson]

I've debugged it further. In unit DBXDynalink:

function NativeDBXCallback(Handle: DBXCallbackHandle;
TraceCategory: TInt32; TraceMessage: TDBXWideString): CBRType; stdcall;
begin
Result := TDBXDynalinkConnection(Handle).DBXCallback(nil, TraceCategory, TraceMessage);
end;

where TraceMessage is:

SELECT 999999 AS RECNO,
CAST(db_name() AS VARCHAR(128)) AS CATALOG_NAME,
CAST(user_name(o.uid) AS VARCHAR(128)) AS SCHEMA_NAME,
CAST(o.name AS VARCHAR(128)) AS TABLE_NAME,
CAST(x.name AS VARCHAR(128)) AS INDEX_NAME,
CAST(c.name AS VARCHAR(128)) AS COLUMN_NAME,
xk.keyno AS COLUMN_POSITION,
CAST(NULL AS VARCHAR(1)) AS PKEY_NAME,
(CASE WHEN x.status & 0x800 0 THEN 4 ELSE 0 END) +
(CASE WHEN x.status & 0x2 0 THEN 2 ELSE 1 END) AS INDEX_TYPE,
(CASE WHEN indexkey_property(x.id, x.indid, 1, N'isdescending') 0 THEN 'D' ELSE 'A' END) AS SORT_ORDER,
CAST(NULL AS VARCHAR(1)) AS FILTER FROM sysobjects o, sysindexes x, syscolumns c, sysindexkeys xk
WHERE o.id = x.id and o.id = c.id and o.id = xk.id and x.indid = xk.indid and c.colid = xk.colid and
xk.keyno 'S' and LEFT(x.name, 8/*_WA_Sys_*/) '_WA_Sys_' AND o.name LIKE 'PRIORITET'
ORDER BY x.name, xk.keyno

So, here we see that sysindexes among others are referenced without the dot. The dot apperently is needed on SQL Azure (in contrast to for example SQL Server 2008 R2, which accepts it without the dot).

[AndreyZ]

Hello,

Thank you for the information. We will investigate this question. As soon as we have a solution to this problem we will let you know.

[ulfandersson]

Hi, how is your investigation going? Can you confirm that error is in your drivers, or should we look elsewhere?

For your information, we tried adding synonyms for some of the system tables:

CREATE SYNONYM SYSOBJECTS
FOR SYS.OBJECTS

CREATE SYNONYM SYSINDEXES
FOR SYS.INDEXES

CREATE SYNONYM SYSCOLUMNS
FOR SYS.COLUMNS

CREATE SYNONYM SYSINDEXKEYS
FOR SYS.INDEX_COLUMNS

This took the execution further on than it did before, but crashed later at:

TSQLConnection.OpenSchemaTable in unit SqlExpr

with the exception: EMSError, invalid column name keyno

and then followed a list of several column names: indid, is, status, keycnt, colid.

Obviously, the incompatibility is more serious than just replacing table names.

Hope this can be of any help to you!

Regards,
Ulf Andersson
Nilex AB

[ulfandersson]

Hello,

this problem, is it fixed in version 5.0?

Regards,
Ulf Andersson

[AndreyZ]

We will fix this problem in the next dbExpress driver for SQL Server build.

[ulfandersson]

Thanks, great news!

Can you say anything about when you plan to release it?

Regards,
Ulf

[AndreyZ]

As we are working on MacOS support now, we cannot provide the exact time when the new version of dbExpress driver for SQL Server will be available. We plan to release all dbExpress drivers approximately in two or three weeks.

[ulfandersson]

Hi!

Unfortunately, this problem is back again in version 6.0. When connecting to Azure, we get this error:

Invalid object name 'sysindexes',

when our program is trying to write something to the database for the first time.

More precisely, it crashes here:

function TDBXDevartMsSqlMetaDataReader.GetSqlForIndexes: UnicodeString;
...
if CurrentVersion >= '09.00.0000' then
Result := 'SELECT DISTINCT DB_NAME(), SCHEMA_NAME(O.uid), O.name, I.name, ' +
'CASE WHEN INDEXPROPERTY(I.id, I.name, ''IsUnique'') > 0 THEN I.name ELSE NULL END, ' +
'CONVERT(BIT, COALESCE(OBJECTPROPERTY(object_id(I.name), ''IsPrimaryKey''), 0)), ' +
'CONVERT(BIT, COALESCE(INDEXPROPERTY(I.id, I.name, ''IsUnique''), 0)), ' +
'CONVERT(BIT, 1) ' +
'FROM sysobjects O, sysindexes I, sysindexkeys IK ' +
'WHERE ' +
' O.type IN (''U'') and I.id = O.id AND O.id = IK.id AND I.indid = IK.indid ' +
' AND IK.keyno <= I.keycnt ' +
' AND (DB_NAME() = :CATALOG_NAME or (:CATALOG_NAME IS NULL)) ' +
' AND (SCHEMA_NAME(O.uid) = :SCHEMA_NAME OR (:SCHEMA_NAME IS NULL)) ' +
' AND (O.name = :TABLE_NAME OR (:TABLE_NAME IS NULL)) ' +
'ORDER BY 1,2,3,4'
...

If we replace the file DBXDevartSQLServer.pas with the one from the previous version, it works again.

Can you please look into this?

Regards,

Ulf Andersson
Nilex AB

[AndreyZ]

In the latest dbExpress driver for SQL Server we supported dbExpress 4 metadata. This causes the problem to reappear. We will investigate this question. As soon as we have any results, we will inform you.

[douglasfranciscon]

Hello!
I wanted to experiment with the DBExpress with SQLAzure.
How has the version has a bug, I would like the version 5.0.2 or 5.0.3.
Where can I download one?

What is the prediction of the new Build with patch for access to SQLAzure?


now in portuguese:
Ola!
Eu queria fazer testes com o DBExpress com SQLAzure.
Como a versão tem possui um bug, eu gostaria da versão 5.0.2 ou 5.0.3.
Onde eu consigo o download de uma delas?

Qual a previsão da nova Build com a correção para o acesso ao SQLAzure?

[AlexP]

Hello,

I have sent you an email containing the trial version of dbExpress driver for SQL Server 5.0.3