An error with the 7.7 ODBC on the Linux

[Former Member]

Dear Gentlmen,

I have some problems with a new version (7.7.06.09) of the MaxDB database.

My application is a web application on the ExtJSApachePHPZendFrameWorkMaxDB platform.

The MaxDB and other parts are installed on the Fedora Core Linux server.

The PHP is connecting to the MaxDB via ADO+unixODBC interfaces (unixODBC 2.2.14).

I had no problems with the 7.6 version of the database.

But, the problems are appear then I was install the 7.7 version.

A sample query for selecting all children for the current parent have an error now.

In my PHP application I have no answer from the database. If I execute this query

via isql program (DSN with 7.7 ODBC) (from unixODBC) then I see a segmentation fault error.

But, if I connect to the MaxDB 7.7 via ODBC 7.6 driver (from Version = 7.6.06 Build 003-121-202-135)

I have no problem in my PHP application and isql too.

If I execute this query from another computer with Database Studio and Winodws, I have no error too.

-

---

Install info:

INSTALLER_INFO: Version = 7.7.06 Build 009-121-202-944

INSTALLER_INFO: Changelist = 202944

INSTALLER_INFO: MakeId = 360684

INSTALLER_INFO: Type = kit

SYS_INFO: Host name = myhost.com

SYS_INFO: System = Linux

SYS_INFO: Architecture = I386

SYS_INFO: Version = 2.6.15

SYS_INFO: Subversion = 1.2054_FC5smp

SYS_INFO: C Runtime = GLIBC 2.4

-

---

/root/.odbc.ini

[DSN6]

ServerDB=NTL

ServerNode=127.0.0.1

Driver=/opt/sdb/interfaces/odbc/lib/libsqlod.so

[DSN7]

ServerDB=NTL

ServerNode=127.0.0.1

Driver=/opt/sdb/programs/lib/libsdbodbc.so

-

---

table fr_form

create table "NADMIN"."FR_FORM"(

"ID" FIXED (10) not null default SERIAL (1),

"ID_TYPE" INTEGER not null,

"ID_GROUP" INTEGER,

"ID_USER" INTEGER not null,

"ID_PARENT" INTEGER,

"SORTORDER" INTEGER default 10,

"PNAME" VARCHAR (36) ASCII,

"NAME" VARCHAR (512) ASCII,

"REMARK" VARCHAR (1024) ASCII,

"RVALUE" VARCHAR (1024) ASCII,

"ISARCH" INTEGER default 0,

"ISCPAQ" INTEGER,

"RCOUNT" INTEGER,

"ISSUBACCESS" INTEGER,

"SUBTYPE" INTEGER default 0,

"ISNUMBER" INTEGER default 0,

"IS_HAVE_CHILD" SMALLINT default 0,

constraint SYSPRIMARYKEY primary key ("ID"),

foreign key "SYS_USER_FR_FORM" ("ID_USER") references "NADMIN"."SYS_USER" ("ID") on delete restrict,

foreign key "FR_FORM_FR_FORM" ("ID_PARENT") references "NADMIN"."FR_FORM" ("ID") on delete restrict,

foreign key "FR_GROUP_FR_FORM" ("ID_GROUP") references "NADMIN"."FR_GROUP" ("ID") on delete restrict,

foreign key "FR_TYPE_FR_FORM" ("ID_TYPE") references "NADMIN"."FR_TYPE" ("ID") on delete restrict)

-

---

The query for selecting all children for the current parent with id = 1244

declare c cursor for with recursive tree (node_name, node_id, node_id_parent, node_level)

as ( select name, id, id_parent, 0

from fr_form where id = 1244

union all select name, id, id_parent, node_level+1

from fr_form, tree where tree.node_id = fr_form.id_parent)

select node_id from tree order by node_level desc

-

---

The ODBC trace file

SQLGetFunctions 2009-03-17 13:35:55.322085

ConnectionHandle [in] : 0x92b8e7a

FunctionId [in] : unknown function id: 23

SupportedPtr [out] : 1

SQLRETURN : SQL_SUCCESS

SQLGetFunctions 2009-03-17 13:35:55.322111

ConnectionHandle [in] : 0x92b8e7a

FunctionId [in] : SQL_API_SQLGETDIAGREC

SupportedPtr [out] : 0

SQLRETURN : SQL_SUCCESS

SQLGetInfo 2009-03-17 13:35:55.322147

ConnectionHandle [in] : 0x92b8e7a

InfoType [in] : SQL_DRIVER_ODBC_VER

BufferLength [in] : 20

enc : ASCII

InfoValuePtr [out] : 03.51

StringLengthPtr [out] : (null)

SQLRETURN : SQL_SUCCESS

SQLGetDiagRec 2009-03-17 13:35:55.322215

HandleType [in] : SQL_HANDLE_DBC

Handle [in] : 0x92b8e7a

RecNumber [in] : 1

BufferLength [in] : 512

enc : ASCII

SQLRETURN : SQL_NO_DATA

SQLRETURN : SQL_NO_DATA

SQLAllocHandle 2009-03-17 13:36:09.768751

HandleType [in] : SQL_HANDLE_STMT

InputHandle [in] : 0x92b8e7a

OutputHandlePtr [out] : &(0x92c0290)

SQLRETURN : SQL_SUCCESS

SQLPrepare 2009-03-17 13:36:09.768863

StatementHandle [in] : 0x92c0290

StatementText [in] : 'declare c cursor for with recursive tree (node_name, node_id, node_id_parent, node_level) as ( select na

m fr_form where id = 1244 union all select name, id, id_parent, node_level+1 from fr_form, tree where tree.node_id = fr_form.id_\

parent) select node_id from tree order by node_level desc '

TextLength [in] : SQL_NTS

enc : ASCII

::PARSE 'SQLCURS_2' 2009-03-17 13:36:09.769094 [0x0x92da5f8]

SQL COMMAND:'declare c cursor for with recursive tree (node_name, node_id, node_id_parent, node_level) as ( select name, id, id_pare

m fr_form where id = 1244 union all select name, id, id_parent, node_level+1 from fr_form, tree where tree.node_id = fr_form.id_\

parent) select node_id from tree order by node_level desc '

^

<CURRENT WRITE POSITION>.

Pavel

[Former Member · Accepted solution]

Hallo all,

there is a bug in the kernel.

This causes trouble if

- a recursive select is used

- the final select (select node_id from tree order by node_level desc)

has a smaller number of output columns than the intermediate select (where you have 4 output columns)

Thank you for reporting. This bug will be fixed as soon as possible.

As workaround, please add 3 further output columns to the final select (and throw the values away as you do not need them in your application).

Sorry for any inconveniences.

Elke

[Former Member · Accepted solution]

Yes.With such numbers of arguments in result select, the error is not appear. Thanks.

Pavel

[Former Member]

I am using MaxDB with unixODBC and php5 also and got some other problems with update to 7.7.06.09.

Using ASCII mode for Columns

With new libsdbodbc.so i got conversion errors with each update or insert from php so i switched back to libsqlod.so from latest 7.6 version and everything works fine.

[TTK]

Hello Pavel

Actually the problem is not in the ODBC driver, which was my first guess, but lies deeper.

Can you please check, what happens, if the query has a non empty result set.

What happens with the old ODBC driver, what in JDBC?

Regards Thomas

[Former Member]

Hello Thomas!

The segmentation fault error is appear in a PHP and isql programms with ODBC 7.7.06.09.

I test it with an empty and not empty result set.

With ODBC from 7.6.06.03 database all querys are execute OK.

But, I found that this error was appear in the sqlcli 7.7.06.09 program. Is the sqlcli program uses ODBC mechanism too?

I can not check with the JDBC, because I don't use Java. But, If Your write my, how I can do it. I can do it.

Regards,

Pavel

[TTK]

The cause of the crash of ODBC resp. the layer below is meanwhile discovered. Below ODBC (77ff) an internal interface layer denoted as SQLDBC is used. This is also the base for sqlcli.

Database Studio uses JDBC while SQL Studio uses ODBC 7.6 (which is not based on SQLDBC).

So, are the results in Database Studion (i.e. JDBC) and in your application with ODBC 7.6 the same?

Regards Thomas

[Former Member]

SQL Studio 7.6.0.21, Windows XP, mode Internal.

1) If not empty result set - result OK

2) If empty result set (condition with ID is not in table) - result OK (null)

SAP MaxDB Database Studio7.7.06.09, Build: 009-123-202-944

1) If not empty result set - result error: Error Executing 'declare c cursor for with recursive tree (node_name, node_id, node_id_parent, node_level) as ( ...' [-4000]: Unknown result table.

2) If empty result set (condition with ID it not in table) - result: Statement 'declare c cursor for with recursive tree (node_name, node_id, node_id_parent, node_level) as ( ...' successfully executed in 0 ms. - No result

BUT, if I set the SQL mode to the Oracle mode, then the query is executed OK:

Statement 'declare c cursor for with recursive tree (node_name, node_id, node_id_parent, node_level) as ( ...' successfully executed in 0 ms.

Regards Pavel

[TTK]

Hello Pavel

FYI, I can reproduce the problem.

Please excuse the delay.

I'm going to analyse this and let you know as soon as possible.

Regards Thomas