Delphi 6 and Interbase 6

[delphiman]

Can someone please tell me why a change to data in an SQL
Database table , made using the IBConsole, does not reflect
when I run a Delphi 6 Enterprise project otherwise succesfully accessing the same database and Table. (Except that my updates are not working - which I still have to unravel the reason for.)

 

[Svanhooft]

hi,

i donn't exactly know what your problem is bur perhaps you have turned cached updates on. If you do that you have to do IBdatabase1.applyupdates and IBtransaction.commit to actualy write the data to the database.

Steph

 

[delphiman]

Thanks Steph. I think my problem would be better put as follows:-

I am trying to get to grips with using InterbaseSQLServer and 3-Tier developments.
The plan being to have the Interbase SQLServer and 1st ThinClient on the main system.
(Which I understand to be a 2-Tier development.)

Thereafter I intend to develop further to 3-Tier whereby subsequent ThinClients on
will be remote machines.

So far I have a test project working wherein I have the following components.

IBTransaction1
IBDatabase1
DataSource1 With DBGrid1 and DBNavigator1 hooked to it.
IBDataSet1.

The project compiles and at RunTime I can ADD new records which are obviously
Posting to the SQLTable because if I CLOSE and EXIT the Project and return - the
addition is there.

But EDIT and DELETE of a record does NOT work as established by the above method.

I might mention that I have the following code in the relevant properties of IBDataSet1

In SelectSQL

SELECT *
FROM MYTABLE


In RefreshSQL

Select
P_NO,
PNAME
from MYTABLE
where
P_NO = _NO and
PNAME = NAME

In InsertSQL

insert into MYTABLE
(P_NO, PNAME)

values
P_NO, NAME)

In ModifySQL

update MYTABLE
set
P_NO = _NO,
PNAME = NAME,
where
P_NO = :OLD_P_NO and
PNAME = :OLD_PNAME

In Delete SQL

delete from MYTABLE
where
P_NO = :OLD_P_NO and
PNAME = :OLD_PNAME


What am I missing?

Thanks in advance.

TERRY

 

[Svanhooft]

hi,

The code you use looks ok. Their is a differnce on the way we implemented SQL and the way you use it. Normaly we use a TIBquery for the Select statement an TIBupdateSql for the updates.
After every post we do an IBDatabase1.applyupdates(IBQuery1)
after the apply we do an IBTransaction1.commit
The apply and the commit are necessary for the actual writing of the data to the database. If you donn't use them nothing happens and the database will do a rollback and you are back where you started.

steph

 

[delphiman]

Thanks Steph!

>There is a difference on the way we implemented SQL and the way you use it.

I am following an example by Marco Cantu' in Chapter 15 (Interbase and IBX) of his book "How to Master Delphi 6" and refer to a statement in the final paragraph of Page 672 wherein he says:

"An alternative to using the IBQuery and IBUpdateSQL components is to use the IBDataSet component, which combines the two. An InterBase dataset, in fact, is a live query with a complete set of SQL statements for all the main operations. The difference between using the two components and the single one are minimal. Using IBQuery and IBUpdate is probably better when porting an existing application based on the two equivalent BDE components, even if porting the program directly to the IBDateSet component doesn't really require a lot of extra work."

Since I am creating a brand-new application (not an "exisiting one&quot I would actually prefer to use the IBDataSet component.

>After every post we do an IBDatabase1.applyupdates(IBQuery1)after the apply we do an IBTransaction1.commit.

Nevertheless and in order to get a better understanding of things I dropped a TIBquery (for the Select statement) and TIBupdateSql (for the updates) components onto my form. Naturally I added the required SQL code as applicable.

I then created an IBQuery.AfterPost eventHandler wherein I inserted your "IBDatabase1.applyupdates(IBQuery1)" statement.

>after the apply we do an IBTransaction1.commit

Whilst I understand what is supposed to happen I don't understand where this statement is supposed to go. I tried inserting it immediately below the above "IBDatabase1.applyupdates(IBQuery1)" in the IBQuery.AfterPost event-handler.

Although the project compiles without incident it makes no difference to my problem.

Thanks again for your interest and time!

TERRY

 

[delphiman]

Hi Steph!

Any further suggestions on this ...

>after the apply we do an IBTransaction1.commit

Whilst I understand what is supposed to happen I don't understand where this statement is supposed to go. I tried inserting it immediately below the above "IBDatabase1.applyupdates(IBQuery1)" in the IBQuery.AfterPost event-handler.

Although the project compiles without incident it makes no difference to my problem. I still don't get an update.

TERRY

 

[Oppenhiemer]

Hi -

Im not sure if these 2 problems are related - but I seem to have problems using the IBDataset component for anything else but adding records!

Having experienced some odd behaviour while using the database to attempt to change some data - I created a test app.

I used an IBDataset component (with all the automatically generated SQL statements) a datagrid component,Transaction and Database component.

I found that using a dbnavigator control, it seemed to let me make all sorts of changes to the data - but when I came out of the program and went back in, all the data changes made were lost!

All that is from new records - that it would happily let me create. I could not retain any alterations or deletions to the existing database.

Could anyone help me in this ? It may be that Delphiman and I are both experiencing the same problems.

I have tried ...
1. Putting the TBDataset component in "edit " mode when form opens.
2. Explicitally issuing commands to the vaious components to try force some results (such as "post" to the IBDataset copmponent and "commit" to the Transaction component.)

Any help would be grately appreciated.

Cheers..
Opp.

P.S I know its no suprice - but using the IBTable component in place of the IBDataset coponent solves all of the problems (it does what it says on the tin and changes are saved.)

 

[Oppenhiemer]

I think I may have solved my problem. Well.. here goes for my take on the situation..

1. When you select the "Generate SQL" option in the IBDataset component, it creates a statement like so..
===========================================
update PERSONNEL
set
INITITALS = :INITITALS,
FULL_NAME = :FULL_NAME,
LOG_IN_NAME = :LOG_IN_NAME,
PRIVALAGE = RIVALAGE,
USER_PASS = :USER_PASS
where
INITITALS = :OLD_INITITALS and
FULL_NAME = :OLD_FULL_NAME and
LOG_IN_NAME = :OLD_LOG_IN_NAME and
PRIVALAGE = :OLD_PRIVALAGE and
USER_PASS = :OLD_USER_PASS
===========================================

As you can see, it generated many "WHERE" clauses - when all thats needed is one that points to the unique keys (to identify the record in question.)

So...
===========================================
update PERSONNEL
set
INITITALS = :INITITALS,
FULL_NAME = :FULL_NAME,
LOG_IN_NAME = :LOG_IN_NAME,
PRIVALAGE = RIVALAGE,
USER_PASS = :USER_PASS
where
INITITALS = :OLD_INITITALS
===========================================

Here "INITITALS" is my key field for this table. I think the reason it failed with the default SQL statements is related to the WHERE clause

Anyway, Im probably wrong - but it seems to work fine for me now I have made the changes to the default SQL.

Cheers..
Opp.

 

[delphiman]

Thanks for your input Opp.

I am having a very careful look at what you're doing. Meanwhile I have also discovered there is more to Marco Cantu's example than I first noticed and which may or may not solve my problem.

I'll keep this thread posted.

TERRY

 

[delphiman]

SUCCESS!!

When I was in the RRAF some years ago we had a slogan
up on walls all over the place which read "When all else
fails try the instrucion manual!"

From which I learned nothing apparently!

After some head-banging and THEN studying Marco Cantu's
example PROPERLY I have succeeded in getting a simple
model to work using only
A Form with a Grid and a ToolBar

A DataModule on which I have
IBDatabase
IBTransaction
IBDataSet
TDataSet.

If anyone would like the model zipped up and sent to
them contact me at <delphiman@bigpond.com>

TERRY