problem with ibDataSet

I try to run a query like
sSQL := 'update supplier set practname = ''A. C. Computer'' where supid = 1';
ibDataset1.ModifySQL.Text := sSQL;
ibDataset1.Active := True;

I got an error msg. 'The sql is empty'.

Does ibDataset cannot use ModifySQL?

Sorry, I only got 40 points.


Thanks

Peter
petershaw8Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

esoftbgCommented:
If you need to use only ModifySQL, then just use IBQuery.SQL
IBDataset requires all the SQL as SelectSQL, RefreshSQL, ModifySQL and DeleteSQL to be correct
I gues you fill only ModifySQL and try to use IBDataset .... this is impossible
0
_Katka_Commented:
Hi, try to use IBQuery as esoftbg suggested

sSQL := 'update supplier set practname = ''A. C. Computer'' where supid = 1';
if ibQuery1.Active then ibQuery1.Close;
ibQuery1.SQL.Clear;
ibQuery1.SQL.Add(sSQL);
ibQuery1.ExecSQL;

regards,
Kate
0
TheRealLokiSenior DeveloperCommented:
actually, if the query does not return anything, you shold use an TIBSql, and call it with ExecQuery
TIBQuery returns rows and is more than you need for an update statement
the IBDataset is a full solution whereby you provide sqls for insert, delete, select, refresh, and update.
This should not be used for a single command as you are doing.
Setting all these sqls appropriately allows you to simply drop DBAware components on the form and use it as if it were a TTable and it will take care of all the work
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

petershaw8Author Commented:
The procedure need handle delete, update, insert, select, create sql. So Use ibDataset seems the simpliest way. Because I need to know fieldcount, fieldname, but I don't know how to get the fieldcount, fieldname in TIBSQL.
0
TheRealLokiSenior DeveloperCommented:
using TIBDataset, you would set the "MODIFYSQL" property to be something like
update LOGSTABLE
set
BRANCH = :BRANCH,
LOG_DATE = :LOG_DATE,
USER_ID = :USER_ID, EVENT_CODE = :EVENT_CODE,
EVENT_DESCRIPTION = :EVENT_DESRIPTION,
EXTRACTED = :EXTRACTED
where
BRANCH = :OLD_BRANCH and
LOG_DATE = :OLD_LOG_DATE

the "Branch and Log_Date" fields are the 2 key fields in this table, so must be shown here as
:OLD_#####

hth, Loki
0
esoftbgCommented:
Example about All the SQLs you need to fill similar to

1). DeleteSQL:
 DELETE FROM GOODS WHERE ID = :ID
//.....................................................
2). InsertSQL:
 INSERT INTO GOODS
 VALUES (:ID,
         :NAME,
         :GROUP_ID
        )
//.....................................................
3). ModifySQL:
 UPDATE GOODS SET
        NAME = :NAME,
        GROUP_ID = :GROUP_ID
  WHERE ID = :ID
//.....................................................
4). RefreshSQL:
 SELECT * FROM GOODS WHERE ID = :ID
//.....................................................
5). SelectSQL:
 SELECT * FROM GOODS ORDER BY NAME
0
petershaw8Author Commented:
Do you mean for non select sql, sql statement must contain parameters?
0
esoftbgCommented:
Ofcourse INSERT and UPDATE require parameters, but they work automatically - you must correct describe all the SQLs (above example is from my working project ....)
0
esoftbgCommented:
> but they work automatically
I mean by Delphi you use something like:

IBDataSetGOODS.Edit;
IBDataSetGOODS.FieldByName('NAME').AsString := Edit_NAME.Text;
..................................................................................................
IBDataSetGOODS.Post;

you don't use:
IBDataSetGOODS.PARAMByName('NAME').AsString := Edit_NAME.Text;


You use Fields - IBDataSet knows how to work with them if all the SQLs are set correctly
0
petershaw8Author Commented:
But in my procedure, I only want to update database, I don't select, insert, etc. Do I still need to write all SQLs?
0
esoftbgCommented:
> But in my procedure, I only want to update database, I don't select, insert, etc. Do I still need to write all SQLs?
Yes, you need to write all SQLs regardless of you only want to update database ....
0
TheRealLokiSenior DeveloperCommented:
If you want to use IBdataset, then yes.
If all you want is an sql to do an update, and don'tr require data-aware components, or a "dataset" like interface, then do as I said, and just use a TIBSql,

the ibsql1.sql.text will be
'update supplier S set S.pracname = :ParamPracnam where S.supid = :ParamSUPID'


then in code you go

        ibsql1.Database.Connected := True;
        try
            ibsql1.Transaction.StartTransaction;
           
            ibsql1.ParamByName('ParamPractname').AsString := 'A.C. Computer'; //set to what you want
            ibsql1.ParamByName('ParamSUPID').AsInteger := 1; //set to what you want
            ibsql1.ExecQuery;
        finally
            ibsql1.Transaction.Commit;
            ibsql1.Database.Connected := False;
        end;


0
TheRealLokiSenior DeveloperCommented:
oops ibsql1.ParamByName('ParamPracnam').AsString   not ParamPractname
0
petershaw8Author Commented:
I read your SQLs, the selectsql, modyfysql... all access Goods table only, but have different parameters. Do these SQLs must only access only one table? Can I write sql like:

select a.*, b.* from tableA a, tableB b where a.id = b.id

update tableA set AFld = 'test' where id = 1

delete from tableA a where a.id = (select max(b.id) from tableB)

insert ...

refresh...

I mean the sql may access more than one table through table join?

 
0
esoftbgCommented:
Above code I posted is only example from my project - it shows the way about SQLs to be written (not exactly to be used).
IBDataSet componet works with only 1 table about the INSERT, UPDATE, DELETE SQLs,
IBDataSet componet could work with more than 1 tables about the SELECT and REFRESH SQLs, but it will disable the ability to work with INSERT, UPDATE, DELETE SQLs
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.