Error when I try to edit and save data without modification

silvioprog · Post by **silvioprog** » 16.08.2010, 14:17

Steps to reproduce the error:

1. Compile and open the project ;
2. Click in the edit button;
3. Click in the save button;

You will see the error as image attachment.

In Zeos 6.6.6 is normal, the error is in the current version of SVN.

Windows7, Zeos7(SVN), Lazarus 0.9.29 r27109 FPC 2.4.3 i386-win32-win32/win64, Firebird 2.1.

Thanks.

silvioprog · Post by **silvioprog** » 16.08.2010, 14:19

Patch to reproduce error in attached.

Post by **mdaems** » 30.08.2010, 00:48

Hi Silvio,

Seems like this is an SQL syntax bug. Do you have a TZSQLMonitor trace of the queries sent to the server?

Mark

silvioprog · Post by **silvioprog** » 30.08.2010, 13:50

Hi Mark,

My SQL:

Code: Select all

SELECT * FROM TABLETEST
ORDER BY OID

My table:

Code: Select all

CREATE TABLE TABLETEST
(
  OID integer NOT NULL,
  FIELD varchar(50),
  CONSTRAINT PK_OIDTABLETEST PRIMARY KEY (OID)
);

Post by **mdaems** » 01.09.2010, 23:21

Do you have a TZSQLMonitor trace of the queries sent to the server?

Without, it's almost impossible to tell what strance character he's complaining about

Mark

silvioprog · Post by **silvioprog** » 01.09.2010, 23:53

Hi Mark, please excuse me

Using this code:

Code: Select all

procedure TForm1.ZSQLMonitor1Trace(Sender: TObject; Event: TZLoggingEvent;
  var LogTrace: Boolean);
begin
  Memo1.Text := Event.AsString;
end;

I receive this:

http://pastebin.com/chfhxazt

It's the information than you need?

Post by **mdaems** » 02.09.2010, 06:39

That's exactly what I needed. Now you see the problem is in the (metadata) query

Code: Select all

SELECT a.RDB$RELATION_NAME, a.RDB$FIELD_NAME, a.RDB$FIELD_POSITION, a.RDB$NULL_FLAG, a.RDB$DEFAULT_VALUE, b. RDB$FIELD_LENGTH, b.RDB$FIELD_SCALE, c.RDB$TYPE_NAME, b.RDB$FIELD_TYPE, b.RDB$FIELD_SUB_TYPE, b.RDB$DESCRIPTION, b.RDB$CHARACTER_LENGTH, b.RDB$FIELD_PRECISION, a.RDB$DEFAULT_SOURCE, b.RDB$DEFAULT_SOURCE AS RDB$DEFAULT_SOURCE_DOMAIN,b.RDB$COMPUTED_SOURCE AS RDB$COMPUTED_SOURCE FROM RDB$RELATION_FIELDS a JOIN RDB$FIELDS b ON (b.RDB$FIELD_NAME = a.RDB$FIELD_SOURCE) LEFT JOIN RDB$TYPES c ON (b.RDB$FIELD_TYPE = c.RDB$TYPE AND c.RDB$FIELD_NAME = 'RDB$FIELD_TYPE') WHERE a.RDB$RELATION_NAME = 'TABLETEST' ORDER BY a.RDB$RELATION_NAME, a.RDB$FIELD_POSITION

I don't know exactly what's wrong however. Does this query run correctly in an other sql interface to your database? (Sorry, no FB readily available)

Mark

BTW : you don't need a line of code when setting a filename and autosave=true on the monitor component

silvioprog · Post by **silvioprog** » 02.09.2010, 12:31

In FlameRobin worked perfectly, please see:

http://imagebin.org/112383

Post by **mdaems** » 04.09.2010, 20:28

Well, I still wonder wat's going on there. If it really is that query that's wrong, because there's a 10 second time frame between that execute and the error:

Code: Select all

2010-09-01 19:49:58 cat: Execute, proto: firebird-2.1, msg:  SELECT a.RDB$RELATION_NAME, a.RDB$FIELD_NAME, a.RDB$FIELD_POSITION, a.RDB$NULL_FLAG, a.RDB$DEFAULT_VALUE, b. RDB$FIELD_LENGTH, b.RDB$FIELD_SCALE, c.RDB$TYPE_NAME, b.RDB$FIELD_TYPE, b.RDB$FIELD_SUB_TYPE, b.RDB$DESCRIPTION, b.RDB$CHARACTER_LENGTH, b.RDB$FIELD_PRECISION, a.RDB$DEFAULT_SOURCE, b.RDB$DEFAULT_SOURCE as RDB$DEFAULT_SOURCE_DOMAIN,b.RDB$COMPUTED_SOURCE as RDB$COMPUTED_SOURCE FROM RDB$RELATION_FIELDS a JOIN RDB$FIELDS b ON (b.RDB$FIELD_NAME = a.RDB$FIELD_SOURCE) LEFT JOIN RDB$TYPES c ON (b.RDB$FIELD_TYPE = c.RDB$TYPE and c.RDB$FIELD_NAME = 'RDB$FIELD_TYPE') WHERE a.RDB$RELATION_NAME = 'TABLETEST' ORDER BY a.RDB$RELATION_NAME, a.RDB$FIELD_POSITION
2010-09-01 19:50:09 cat: Execute, proto: firebird-2.1, msg:  Dynamic SQL Error SQL error code = -104 Unexpected end of command - line 1, column 1, errcode: -104, error: Invalid token

Does it always take that long?
Please, put a breakpoint inside TZInterbase6DatabaseMetadata.UncachedGetColumns to check if that query really fails or if it's something else afterwards.

Mark

silvioprog · Post by **silvioprog** » 05.09.2010, 00:12

Hi Mark,

I put the breakpoint, the error happens before it:

http://imagebin.org/112711

The error also happens in Delphi(7):

http://imagebin.org/112709

This error is present only in Zeos-7(SVN), in Zeos-6.6.6 is ok.

ps. Excuse, I could not translate this part: "Does it always take that long?"

Post by **mdaems** » 08.09.2010, 20:14

Does it always take that long?

means "Does query execution always take 10 seconds?"

That's extremely strange. Where is the query in the log coming from, then?
Can you get the call stack at the moment the error is raised? Showing the call stack in your windows example should do the job. That way we should ba able to see exactly where the 'dynamic sql error' is coming from.

Mark

silvioprog · Post by **silvioprog** » 09.09.2010, 03:07

Hi Mark,

Not always 10 seconds, sometimes faster, sometimes slower.

Call stack:

Code: Select all

#0 fpc_raiseexception at :0
#1 CHECKINTERBASE6ERROR({<IZPLAINDRIVER> = {<IUNKNOWN> = {}, }, }, #1#249#1't'#4#152#1#19#4#1#4#1#0'H'#158'X'#234'X'#152'p'#138, LCEXECUTE, 0x0) at C:\Repository\svn\zeos\src\dbc\ZDbcInterbase6Utils.pas:818
#2 PREPARESTATEMENT({<IZPLAINDRIVER> = {<IUNKNOWN> = {}, }, }, 0x294a14, 0x294a18, 3, 0x0, 0x5) at C:\Repository\svn\zeos\src\dbc\ZDbcInterbase6Utils.pas:858
#3 TZINTERBASE6PREPAREDSTATEMENT__PREPARE(<error reading variable>) at C:\Repository\svn\zeos\src\dbc\ZDbcInterbase6Statement.pas:560
#4 TZINTERBASE6PREPAREDSTATEMENT__CREATE({<IUNKNOWN> = {}, }, 0x0, 0x0, 0x6c0438, <error reading variable>) at C:\Repository\svn\zeos\src\dbc\ZDbcInterbase6Statement.pas:542
#5 TZINTERBASE6CONNECTION__CREATEPREPAREDSTATEMENT(0x0, 0x0, <error reading variable>, {<IZSTATEMENT> = {<IUNKNOWN> = {}, }, }) at C:\Repository\svn\zeos\src\dbc\ZDbcInterbase6.pas:598
#6 TZABSTRACTCONNECTION__PREPARESTATEMENT(0x0, <error reading variable>, {<IZSTATEMENT> = {<IUNKNOWN> = {}, }, }) at C:\Repository\svn\zeos\src\dbc\ZDbcConnection.pas:551
#7 TZGENERICCACHEDRESOLVER__CREATERESOLVERSTATEMENT(0x0, <error reading variable>, {<IZSTATEMENT> = {<IUNKNOWN> = {}, }, }) at C:\Repository\svn\zeos\src\dbc\ZDbcGenericResolver.pas:325
#8 TZGENERICCACHEDRESOLVER__POSTUPDATES({<IZRESULTSET> = {<IUNKNOWN> = {}, }, }, UTMODIFIED, 0x224028, 0x224068, <error reading variable>) at C:\Repository\svn\zeos\src\dbc\ZDbcGenericResolver.pas:837
#9 TZABSTRACTCACHEDRESULTSET__POSTROWUPDATES(0x224028, 0x224068, <error reading variable>) at C:\Repository\svn\zeos\src\dbc\ZDbcCachedResultSet.pas:439
#10 TZABSTRACTCACHEDRESULTSET__POSTUPDATES(<error reading variable>) at C:\Repository\svn\zeos\src\dbc\ZDbcCachedResultSet.pas:546
#11 TZABSTRACTCACHEDRESULTSET__UPDATEROW(<error reading variable>) at C:\Repository\svn\zeos\src\dbc\ZDbcCachedResultSet.pas:1610
#12 TZABSTRACTDATASET__INTERNALUPDATE(<error reading variable>) at C:\Repository\svn\zeos\src\component\ZAbstractDataset.pas:381
#13 TZABSTRACTDATASET__INTERNALPOST(<error reading variable>) at C:\Repository\svn\zeos\src\component\ZAbstractDataset.pas:470
#14 DB_TDATASET_$__TRYDOING$TDATAOPERATION$TDATASETERROREVENT$$BOOLEAN at :0
#15 DB_TDATASET_$__POST at :0
#16 TDBCUSTOMNAVIGATOR__BTNCLICK(NBPOST, <error reading variable>) at .\include\dbcustomnavigator.inc:386
#17 TDBCUSTOMNAVIGATOR__BUTTONCLICKHANDLER(0x4018fb8, <error reading variable>) at .\include\dbcustomnavigator.inc:299
#18 TCONTROL__CLICK(<error reading variable>) at .\include\control.inc:2251
#19 TCUSTOMSPEEDBUTTON__CLICK(<error reading variable>) at .\include\speedbutton.inc:115
#20 TCUSTOMSPEEDBUTTON__WMLBUTTONUP({MSG = 514, KEYS = 0, XPOS = 13, YPOS = 17, POS = {X = 13, Y = 17}, DUMMY = 1114125, RESULT = 0}, <error reading variable>) at .\include\speedbutton.inc:797
#21 SYSTEM_TOBJECT_$__DISPATCH$formal at :0
#22 TCUSTOMSPEEDBUTTON__WSREGISTERCLASS(0x270f9ec) at .\include\speedbutton.inc:748
#23 TCONTROL__PERFORM(514, 0, 1114125, <error reading variable>) at .\include\control.inc:1025
#24 TWINCONTROL__ISCONTROLMOUSEMSG(void, <error reading variable>) at .\include\wincontrol.inc:4596
#25 TWINCONTROL__WNDPROC({MSG = 514, WPARAM = 0, LPARAM = 1114349, RESULT = 0, WPARAMLO = 0, WPARAMHI = 0, LPARAMLO = 237, LPARAMHI = 17, RESULTLO = 0, RESULTHI = 0}, <error reading variable>) at .\include\wincontrol.inc:5172
#26 DELIVERMESSAGE(0x4015f98, void) at lclmessageglue.pas:110
#27 WINDOWPROC(592702, 514, 0, 1114349) at win32callback.inc:2420
#28 ?? at :0
#29 ?? at :0
#30 ?? at :0
#31 ?? at :0

When I open the unit ZPlainFirebirdDriver in the SourceEditor, the error no longer appears.

To appear the error again I must close and open the Lazarus (or Delphi).

I tested in two PCs differents.

Post by **mdaems** » 19.09.2010, 21:00

Silvio,

Took me long time to log on again. Sorry for the waiting time.

Well, from your trace I see it's the statement created by the GenericCachedResolver object that causes this error.
And this isn't visible in the log because the statement is only logged after the error is checked. (So never in case of an error).

Code: Select all


procedure TZInterbase6PreparedStatement.Prepare;
begin
  StmtHandle := nil;
  with FIBConnection do
  begin
    StatementType := ZDbcInterbase6Utils.PrepareStatement(GetPlainDriver,
        GetDBHandle, GetTrHandle, GetDialect, SQL, StmtHandle);

    if StatementType in [stSelect, stExecProc] then
      begin
        SQLData := TZResultSQLDA.Create(GetPlainDriver, GetDBHandle, GetTrHandle);
        PrepareResultSqlData(GetPlainDriver, GetDBHandle, GetDialect,
          SQL, StmtHandle, SQLData);
      end;
  end;
  CheckInterbase6Error(SQL);
  LogPrepStmtMessage(lcPrepStmt, SQL);
  inherited Prepare;
end;

Seems like moving "LogPrepStmtMessage(lcPrepStmt, SQL);" up to the top of the procedure might help here.
Can you try that and see what he's really complaining about.
A wild guess : 'OID' or 'field' might be special words to FB, which might require quoting.

Mark

silvioprog · Post by **silvioprog** » 21.09.2010, 15:26

Hi Mark, excuse for my delay.

I changed the code for:

Code: Select all

procedure TZInterbase6PreparedStatement.Prepare;
begin
  LogPrepStmtMessage(lcPrepStmt, SQL);
  StmtHandle := nil;
  with FIBConnection do
  begin
    StatementType := ZDbcInterbase6Utils.PrepareStatement(GetPlainDriver,
        GetDBHandle, GetTrHandle, GetDialect, SQL, StmtHandle);

    if StatementType in [stSelect, stExecProc] then
      begin
        SQLData := TZResultSQLDA.Create(GetPlainDriver, GetDBHandle, GetTrHandle);
        PrepareResultSqlData(GetPlainDriver, GetDBHandle, GetDialect,
          SQL, StmtHandle, SQLData);
      end;
  end;
  CheckInterbase6Error(SQL);
//  LogPrepStmtMessage(lcPrepStmt, SQL);
  inherited Prepare;
end;

And I provoked the error, and generated the log and trace:

LOG:

Code: Select all

2010-09-21 11:15:44 cat: Connect, proto: firebird-2.1, msg: CONNECT TO "C:\Users\silvioprog\Desktop\TEMP\EditBug\TEST.FDB" AS USER "SYSDBA"
2010-09-21 11:15:44 cat: Transaction, proto: firebird-2.1, msg: TRANSACTION STARTED.
2010-09-21 11:15:44 cat: Prepare, proto: firebird-2.1, msg: Statement 1 : select * from tabletest
order by oid

2010-09-21 11:15:44 cat: Execute prepared, proto: firebird-2.1, msg: Statement 1
2010-09-21 11:15:44 cat: Execute, proto: firebird-2.1, msg:  SELECT a.RDB$RELATION_NAME, a.RDB$FIELD_NAME, a.RDB$FIELD_POSITION, a.RDB$NULL_FLAG, a.RDB$DEFAULT_VALUE, b. RDB$FIELD_LENGTH, b.RDB$FIELD_SCALE, c.RDB$TYPE_NAME, b.RDB$FIELD_TYPE, b.RDB$FIELD_SUB_TYPE, b.RDB$DESCRIPTION, b.RDB$CHARACTER_LENGTH, b.RDB$FIELD_PRECISION, a.RDB$DEFAULT_SOURCE, b.RDB$DEFAULT_SOURCE as RDB$DEFAULT_SOURCE_DOMAIN,b.RDB$COMPUTED_SOURCE as RDB$COMPUTED_SOURCE FROM RDB$RELATION_FIELDS a JOIN RDB$FIELDS b ON (b.RDB$FIELD_NAME = a.RDB$FIELD_SOURCE) LEFT JOIN RDB$TYPES c ON (b.RDB$FIELD_TYPE = c.RDB$TYPE and c.RDB$FIELD_NAME = 'RDB$FIELD_TYPE') WHERE a.RDB$RELATION_NAME = 'TABLETEST' ORDER BY a.RDB$RELATION_NAME, a.RDB$FIELD_POSITION
2010-09-21 11:15:46 cat: Execute, proto: firebird-2.1, msg:  Dynamic SQL Error SQL error code = -206 Column unknown A.RDB$FIELD_POSITIONTION At line 1, column 641, errcode: -206, error: Column does not belong to referenced table

TRACE:

Code: Select all

#0 fpc_raiseexception at :0
#1 CHECKINTERBASE6ERROR({<IZPLAINDRIVER> = {<IUNKNOWN> = {}, }, }, #1#249#1't'#4'2'#1#2#1'>'#2#12#1#152#4#1#4#129#0'p'#10, LCEXECUTE, 0x0) at C:\Repository\svn\zeos\src\dbc\ZDbcInterbase6Utils.pas:818
#2 PREPARESTATEMENT({<IZPLAINDRIVER> = {<IUNKNOWN> = {}, }, }, 0x28c4a6c, 0x28c4a70, 3, 0x0, 0x5) at C:\Repository\svn\zeos\src\dbc\ZDbcInterbase6Utils.pas:858
#3 TZINTERBASE6PREPAREDSTATEMENT__PREPARE(<error reading variable>) at C:\Repository\svn\zeos\src\dbc\ZDbcInterbase6Statement.pas:560
#4 TZINTERBASE6PREPAREDSTATEMENT__CREATE({<IUNKNOWN> = {}, }, 0x0, 0x0, 0x6c2528, <error reading variable>) at C:\Repository\svn\zeos\src\dbc\ZDbcInterbase6Statement.pas:542
#5 TZINTERBASE6CONNECTION__CREATEPREPAREDSTATEMENT(0x0, 0x0, <error reading variable>, {<IZSTATEMENT> = {<IUNKNOWN> = {}, }, }) at C:\Repository\svn\zeos\src\dbc\ZDbcInterbase6.pas:598
#6 TZABSTRACTCONNECTION__PREPARESTATEMENT(0x0, <error reading variable>, {<IZSTATEMENT> = {<IUNKNOWN> = {}, }, }) at C:\Repository\svn\zeos\src\dbc\ZDbcConnection.pas:551
#7 TZGENERICCACHEDRESOLVER__CREATERESOLVERSTATEMENT(0x0, <error reading variable>, {<IZSTATEMENT> = {<IUNKNOWN> = {}, }, }) at C:\Repository\svn\zeos\src\dbc\ZDbcGenericResolver.pas:325
#8 TZGENERICCACHEDRESOLVER__POSTUPDATES({<IZRESULTSET> = {<IUNKNOWN> = {}, }, }, UTMODIFIED, 0x2854138, 0x2854178, <error reading variable>) at C:\Repository\svn\zeos\src\dbc\ZDbcGenericResolver.pas:837
#9 TZABSTRACTCACHEDRESULTSET__POSTROWUPDATES(0x2854138, 0x2854178, <error reading variable>) at C:\Repository\svn\zeos\src\dbc\ZDbcCachedResultSet.pas:439
#10 TZABSTRACTCACHEDRESULTSET__POSTUPDATES(<error reading variable>) at C:\Repository\svn\zeos\src\dbc\ZDbcCachedResultSet.pas:546
#11 TZABSTRACTCACHEDRESULTSET__UPDATEROW(<error reading variable>) at C:\Repository\svn\zeos\src\dbc\ZDbcCachedResultSet.pas:1610
#12 TZABSTRACTDATASET__INTERNALUPDATE(<error reading variable>) at C:\Repository\svn\zeos\src\component\ZAbstractDataset.pas:381
#13 TZABSTRACTDATASET__INTERNALPOST(<error reading variable>) at C:\Repository\svn\zeos\src\component\ZAbstractDataset.pas:470
#14 DB_TDATASET_$__TRYDOING$TDATAOPERATION$TDATASETERROREVENT$$BOOLEAN at :0
#15 DB_TDATASET_$__POST at :0
#16 TDBCUSTOMNAVIGATOR__BTNCLICK(NBPOST, <error reading variable>) at .\include\dbcustomnavigator.inc:386
#17 TDBCUSTOMNAVIGATOR__BUTTONCLICKHANDLER(0x3ed8fc8, <error reading variable>) at .\include\dbcustomnavigator.inc:299
#18 TCONTROL__CLICK(<error reading variable>) at .\include\control.inc:2251
#19 TCUSTOMSPEEDBUTTON__CLICK(<error reading variable>) at .\include\speedbutton.inc:115
#20 TCUSTOMSPEEDBUTTON__WMLBUTTONUP({MSG = 514, KEYS = 0, XPOS = 14, YPOS = 10, POS = {X = 14, Y = 10}, DUMMY = 655374, RESULT = 0}, <error reading variable>) at .\include\speedbutton.inc:797
#21 SYSTEM_TOBJECT_$__DISPATCH$formal at :0
#22 TCUSTOMSPEEDBUTTON__WSREGISTERCLASS(0x270f9ec) at .\include\speedbutton.inc:748
#23 TCONTROL__PERFORM(514, 0, 655374, <error reading variable>) at .\include\control.inc:1025
#24 TWINCONTROL__ISCONTROLMOUSEMSG(void, <error reading variable>) at .\include\wincontrol.inc:4596
#25 TWINCONTROL__WNDPROC({MSG = 514, WPARAM = 0, LPARAM = 655598, RESULT = 0, WPARAMLO = 0, WPARAMHI = 0, LPARAMLO = 238, LPARAMHI = 10, RESULTLO = 0, RESULTHI = 0}, <error reading variable>) at .\include\wincontrol.inc:5172
#26 DELIVERMESSAGE(0x3ed5fa8, void) at lclmessageglue.pas:110
#27 WINDOWPROC(1444364, 514, 0, 655598) at win32callback.inc:2422
#28 ?? at :0
#29 ?? at :0
#30 ?? at :0
#31 ?? at :0

A.RDB$FIELD_POSITIONTION? <<- TIONTION

Please excuse me if I misunderstood, my english is not very good.

Post by **mdaems** » 13.10.2010, 22:25

Finally, after 3 more weeks...

A.RDB$FIELD_POSITIONTION? <<- TIONTION

How the X*X*X*X the TZInterbase6DatabaseMetadata.UncachedGetColumns function can send this strange shifted string to the server? Some bad string length calculation? Can you dig deeper into the UncachedGetColumns procedure?

Mark