Oracle XE with Delphi 7 ! No insert

[thiagofmam]

Hello people,

I am try use oracle with zeos in Delphi 7 but i'm have problem with insert.

I add one zquery, zupdatesql. To modify and delete no problem. But when i try insert one register happen the erro: Ora-00923.

My insert code is:

INSERT INTO CLIENTE
(cli_cod,cli_nome,cli_cpf,cli_logradouro,cli_endereco,cli_numero,
cli_bairro,cli_complemento,cli_cep,cli_telefone,cli_celular,
cli_outros_contato,cli_data_cadastro,cli_ultima_compra,
cid_cod,pla_cli_chave)
VALUES (:cli_cod,:cli_nome,:cli_cpf,:cli_logradouro,:cli_endereco,
:cli_numero,:cli_bairro,:cli_complemento,:cli_cep,:cli_telefone,
:cli_celular,:cli_outros_contato,:cli_data_cadastro,
:cli_ultima_compra,:cid_cod,:pla_cli_chave);

My sqlmonitor shown:

2008-11-01 17:21:30 cat: Execute, proto: oracle-9i, msg: SELECT NULL, OWNER, TABLE_NAME, COLUMN_NAME, NULL, DATA_TYPE, DATA_LENGTH, NULL, DATA_PRECISION, DATA_SCALE, NULLABLE, NULL, DATA_DEFAULT, NULL, NULL, NULL, COLUMN_ID, NULLABLE FROM SYS.ALL_TAB_COLUMNS WHERE OWNER LIKE '%' AND TABLE_NAME LIKE 'CIDADE' AND COLUMN_NAME LIKE '%'
2008-11-01 17:21:30 cat: Execute, proto: oracle-9i, msg: select * from cliente where cli_cod = ?
2008-11-01 17:21:30 cat: Execute, proto: oracle-9i, msg: select * from cliente where cli_cod = ?
2008-11-01 17:21:40 cat: Execute, proto: oracle-9i, msg: SELECT null
, errcode: 923, error: ORA-00923: palavra-chave FROM não localizada onde esperada


Somebody can me help ?
Thank you and sorry my english

[mdaems]

Hi,

From the log I see there's a statement created like 'Select NULL' without a from-clause. I could not test this as I have no oracle installation, but I think there is a column with a default value in your table definition and you didn't fill the value for that field. So zeos calculates the default value himself.

I think I may have fixed the error by using this code (copied from the Interbase implementation)

Code: Select all

Index: ZDbcOracle.pas
===================================================================
--- ZDbcOracle.pas	(revision 510)
+++ ZDbcOracle.pas	(working copy)
@@ -61,8 +61,9 @@
 {$IFNDEF FPC}
   Types,
 {$ENDIF}
-  ZCompatibility, Classes, SysUtils, ZDbcIntfs, ZDbcConnection,
-  ZPlainOracleDriver, ZDbcLogging, ZTokenizer, ZGenericSqlAnalyser;
+  ZCompatibility, Classes, SysUtils, Contnrs, ZDbcIntfs, ZDbcConnection,
+  ZPlainOracleDriver, ZDbcLogging, ZTokenizer, ZDbcGenericResolver,
+  ZGenericSqlAnalyser;
 
 type
 
@@ -152,6 +153,12 @@
     function GetNextValueSQL: String; override;
   end;
 
+  {** Implements a specialized cached resolver for Oracle. }
+  TZOracleCachedResolver = class(TZGenericCachedResolver)
+  public
+    function FormCalculateStatement(Columns: TObjectList): string; override;
+  end;
+
 var
   {** The common driver manager object. }
   OracleDriver: IZDriver;
@@ -790,7 +797,29 @@
  result:=Format('SELECT %s.NEXTVAL FROM DUAL', [Name]);
 end;
 
+{ TZOracleCachedResolver }
 
+{**
+  Forms a where clause for SELECT statements to calculate default values.
+  @param Columns a collection of key columns.
+  @param OldRowAccessor an accessor object to old column values.
+}
+function TZOracleCachedResolver.FormCalculateStatement(
+  Columns: TObjectList): string;
+var iPos: Integer;
+begin
+  Result := inherited FormCalculateStatement(Columns);
+  if Result <> '' then begin
+    iPos := pos('FROM', uppercase(Result));
+    if iPos > 0 then begin
+      Result := copy(Result, 1, iPos+3) + ' DUAL';
+    end
+    else begin
+      Result := Result + ' FROM DUAL';
+    end;
+  end;
+end;
+
 initialization
   OracleDriver := TZOracleDriver.Create;
   DriverManager.RegisterDriver(OracleDriver);
Index: ZDbcOracleUtils.pas
===================================================================
--- ZDbcOracleUtils.pas	(revision 510)
+++ ZDbcOracleUtils.pas	(working copy)
@@ -685,7 +685,7 @@
   begin
     CachedResultSet := TZCachedResultSet.Create(NativeResultSet, SQL, nil);
     CachedResultSet.SetConcurrency(rcUpdatable);
-    CachedResultSet.SetResolver(TZGenericCachedResolver.Create(
+    CachedResultSet.SetResolver(TZOracleCachedResolver.Create(
       Statement, NativeResultSet.GetMetadata));
     Result := CachedResultSet;
   end else

Can you please test this code and confirm that this really solves the problem?

Mark

EDIT : What zeoslib version are you using? This part of the forum was intended for the alpha/beta 6.5 and 6.6 releases.

[thiagofmam]

Hi,
Thank you for your answer!!!
PERFECT!!

Really I'm using one default value column and with the code that you put here (dbc.zip) Now is WORKING !!!!

Problem solves

Thank you very much!!!

EDIT: My version is 6.6.2-RC

[mdaems]

Added to SVN (Rev 513). As this patch is simple and the bug really blocking, it will be added to the 6.6-patches branch soon.

Time for an upgrade to 6.6.4. (You'll have to do this patch again however) You might also think about using the 6.6-patches SVN branch as well. But wait a week until this patch is merged in there.

Mark

[mdaems]

The patch is merged in now... SVN rev. 516.

Mark

[hilam]

I'm using 6.6.5-stable, oracle XE, delphi 7.

I have the same problem and checked the code, it's patched.

any idea?

[seawolf]

Have you the same problem with all the tables?
Can you provide a table sql description which gives you the problem?

[hilam]

thanks by your interest. excuse my poor english.

it's in all master-detail relationship, and problem is in detail table.

Zquerys: tb_setor, tb_departamento; linkedfield: id_setor (in tb_departamento); SQL: select * from tb_setor, select * from tb_departamento
(* I try use parameters in the SQLs of Zqueries, but don't work fine the dbgrid refresh *)

in the moment when i click the '+' button in the dbgrid, or act in the keyboard, raise a ZSQLException Ora-01400 'cannot insert null'. before my OnNewRecod event in the tb_departamento query, q not occur.

if i try edit data in the table, in the moment of save/post, raise a 'stack overflow' exception.

i'm doing wrong?