Oracle XE with Delphi 7 ! No insert

Forum related to version 6.5.1 (alpha) and 6.6.x (beta) of ZeosLib's DBOs

Moderators: gto, cipto_kh, EgonHugeist

thiagofmam
Fresh Boarder
Fresh Boarder
Posts: 3
Joined: 01.11.2008, 18:47

Oracle XE with Delphi 7 ! No insert

Postby thiagofmam » 01.11.2008, 20:24

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

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

Postby mdaems » 01.11.2008, 20:56

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.
You do not have the required permissions to view the files attached to this post.
Image

thiagofmam
Fresh Boarder
Fresh Boarder
Posts: 3
Joined: 01.11.2008, 18:47

Postby thiagofmam » 02.11.2008, 21:14

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

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

Postby mdaems » 02.11.2008, 22:32

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.

:idea: 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
Image

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

Postby mdaems » 08.11.2008, 10:24

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

Mark
Image

hilam
Fresh Boarder
Fresh Boarder
Posts: 5
Joined: 22.08.2008, 14:28
Location: João Pessoa/PB

Postby hilam » 04.08.2009, 01:25

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
Zeos Dev Team *
Zeos Dev Team *
Posts: 385
Joined: 04.06.2008, 19:50
Contact:

Postby seawolf » 04.08.2009, 08:56

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

hilam
Fresh Boarder
Fresh Boarder
Posts: 5
Joined: 22.08.2008, 14:28
Location: João Pessoa/PB

Postby hilam » 05.08.2009, 00:14

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?


Return to “6.5.1 (alpha) - 6.6.x (beta)”

Who is online

Users browsing this forum: No registered users and 1 guest