TSQLQuery, DBNavigator and autoincrement fieldd

[thierrybo]

Hi,

first I would say I am learning sqldb in Lazarus and I have no previous Delphi experience.

I an currently testing the DBNavigator on a sample table stored in Ms Sql Server Express 2008 through ODBC and sqldb. I just want to set a sample application using DBNavigator and some DBEdit edit components to browse and edit records with as few manual code as I can. No problem for browsing.

- The table use an autoincrement field as a primary key. As I read Delphi tutorials, Delphi knows these field, and DBNavigator automatically disable DBedit field and use it for update/insert. For Lazarus it it not the case, DBNavigator let me write in DBedit field and tries to write in these fields raising an error.

I provided my own "updatesql" to remove the autoincrement field, but should Lazarus DBnavigator detect autoincrement field or not?

[Wodzu]

Hi,

DBNavigator is a graphical component and it should not have knowledge about what field is what type.

You are wrong saying that Delphi automatically disable field with ftAutoInc property. Please note the fact that with SQL Server you can manually write to the autoincrement column. It is turned off by default but you can do this. So you also can do it in Delphi.

What actually DBNavigator is doing it is issuing a commands implemented in DatSet through the DataSource.

So when you click in the DBNavigator a right arrow the DBNavigator is issuing Query.Next command.

DBNavigator should not detect the type of the fields, this is done by the Dataset object.

I do not udnerstand why you had to provide an own updatesql to remove atuocinrement field. This field should be incremented automatically when you call the Query.insert method (or click the + button on the DBNavigator).

Maybe I am missing something?

[Lacak2]

- The table use an autoincrement field as a primary key. As I read Delphi tutorials, Delphi knows these field, and DBNavigator

Not DBnavigator can not disable Field

automatically disable DBedit field

But DBEdit can detect ftAutoInc field and disable input in this case.
Problem is that TODBCConnection does not detect Autoincrement fields at all AFAIU

I provided my own "updatesql" to remove the autoincrement field,

"updatesql" and also in "insertsql" ?

but should Lazarus DBnavigator detect autoincrement field or not?

DBnavigator not, but DBedit yes

[thierrybo]

Hi,

You are wrong saying that Delphi automatically disable field with ftAutoInc property

As I wrote this Lazarus sample, I also wrote side by side the same program in Turbo Delphi 2006 on the same database and this is what I saw. I did nothing, and NumOrdre field was disabled.

I do not udnerstand why you had to provide an own updatesql to remove atuocinrement field. This field should be incremented automatically when you call the Query.insert method (or click the + button on the DBNavigator).

Maybe I am missing something?

because if i do not provide my own updatesql, I get en EODBCError.

I traced the sql in SQL Server and I get this

Code: [Select]

declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,N'@P1 int,@P2 char(9),@P3 char(4),@P4 int,@P5 int',N'update annuaire0 set  NumOrdre =@P1, Nom =@P2, Prenom =@P3, Age =@P4 where ( NumOrdre = @P5)',0,'updatesql','vide',6,0
select @p1
If I run it manually, SQL Server says

"unable to update identity column 'NumOrdre'"

.

So I provided

Code: [Select]

SQLQuery1.UpdateSql := 'UPDATE annuaire0
   SET Nom =:Nom ,
       Prenom =:Prenom ,
       Age =:Age
 WHERE NumOrdre=:NumOrdre '

[thierrybo]

But DBEdit can detect ftAutoInc field and disable input in this case.
Problem is that TODBCConnection does not detect Autoincrement fields at all AFAIU

Thanks, so it is normal  

"updatesql" and also in "insertsql" ?

See my other answer about updatesql. I traced the default insert statement :

Code: [Select]

insert into annuaire0 ( Nom , Prenom , Age ) values (@P1,@P2,@P3)
I found also

Code: [Select]

declare @p1 int
set @p1=3
exec sp_prepexec @p1 output,N'@P1 char(6),@P2 char(6),@P3 int',N'insert into annuaire0 ( Nom , Prenom , Age ) values (@P1,@P2,@P3)','insert','insert',8
select @p1

this time the default SQL statement is good, it does not add the autoincrement field.

[Wodzu]

thierrybo,

could you provide your sample project here in a form of attachment?

EDIT:

Never mind. So the problem is solved?

[Lacak2]

See my other answer about updatesql. I traced the default insert statement :

Code: [Select]

insert into annuaire0 ( Nom , Prenom , Age ) values (@P1,@P2,@P3)
this time the default SQL statement is good, it does not add the autoincrement field.

May be, that it is true in case, when you leave empty "NumOrdre" field.
But I think, when you enter some value for it, it will be included also in "insert sql" ... as I saw ATM TODBCConnection does not detect identity column, so it can not be excluded ;-)

[thierrybo]

May be, that it is true in case, when you leave empty "NumOrdre" field.
But I think, when you enter some value for it, it will be included also in "insert sql" ... as I saw ATM TODBCConnection does not detect identity column, so it can not be excluded ;-)

Yes you are right, I disabled manually 'NumOrdre' DBEdit, this explains. I have now the "whole picture".


I have a subsequent question. After an insert, the DBGrid does not show the autoincrement value added by the database. so If I try to edit this new record, I get an ODBC error because the keyfield have no value. I tried to add some commands to the AfterPost event :

DBGrid.Refresh, DBGrid.Update, SQLQuery.Refresh but it does not work. The only thing that works is   

Code: [Select]

SQLQuery1.Active:= false;
SQLQuery1.Active:= true;
Is this the proper way?

[Wodzu]

DBGrid.Refresh, DBGrid.Update, SQLQuery.Refresh but it does not work. The only thing that works is   

Code: [Select]

SQLQuery1.Active:= false;
SQLQuery1.Active:= true;
Is this the proper way?

Definitely not. In this way you are reopening a whole Dataset so the performance will drop dramatically when the table will grown.

I would suggest you to download a Zeos component package it should give you a better experience with databases.

[Lacak2]

Yes you are right, I disabled manually 'NumOrdre' DBEdit, this explains. I have now the "whole picture".

There is also another way:
FieldByName('NumOrdre').ProviderFlags:=
 FieldByName('NumOrdre').ProviderFlags - pfInWhere

This will cause, that column 'NumOrdre' will not be included into "insert" and also into "update set"

I have a subsequent question. After an insert, the DBGrid does not show the autoincrement value added by the database.

This is not good. And it should be fixed in FPC, but I am not sure how complicated it is ... You can post bug report and wait if somebody will fix it

[thierrybo]

As  side note,

I do not want to learn Zeos now without exploring SQLdb first 

Second, I did not said I did all my tests with the last "stable" release, 0.9.28-2 with FPC 2.2.4. Yesterday I compiled the Lazarus svn version with also the last fpc from svn (currently 2.5.1) and things are working much better (and related to ODBC I use).

For example with stable Lazarus typing in a DBGrid does nothing if there is not a DBNavigator on the form to click "Apply". Without the DBNavigator, I can edit DBGrid cells, but the AfterPost event it even not ran at all.

With the svn setup, with only a DBGrid, I had only to add ApplyUpdates in AfterPost  event and I can edit /add to the database.
Almost all what I have to enter manually (updatesql, flags) is not necessary now and behaves like my Delphi sample.

[Wodzu]

thierrybo,

Does the autoincrement field problem dissappeared?
If you insert a new record can you update it without refreshing the recordset?

[thierrybo]

No, this problem still persists for your two questions.

And I have a strange behaviour. If I have no AfterScroll/Post event, I can add a record with DBNavigator, but obviously it is not saved to the database. As soon as I add AfterScroll event and ApplyUpdates, the DBNavigator insert button has no effect, nor using the DBGrid.

[Lacak2]

Do not use ApplyUpdates in AfterScroll event handler. IMHO this is not necessary (and may be, that also not correct)