Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 916
  • Last Modified:

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
0
petershaw8
Asked:
petershaw8
  • 6
  • 4
  • 4
  • +1
1 Solution
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now