?
Solved

problem with ibDataSet

Posted on 2005-03-06
15
Medium Priority
?
890 Views
Last Modified: 2008-01-09
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
Comment
Question by:petershaw8
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 4
  • +1
15 Comments
 
LVL 12

Expert Comment

by:esoftbg
ID: 13474333
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
 
LVL 10

Expert Comment

by:_Katka_
ID: 13476315
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
 
LVL 17

Expert Comment

by:TheRealLoki
ID: 13479235
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
Technology Partners: 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!

 

Author Comment

by:petershaw8
ID: 13481464
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
 
LVL 17

Expert Comment

by:TheRealLoki
ID: 13481685
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
 
LVL 12

Expert Comment

by:esoftbg
ID: 13481699
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
 

Author Comment

by:petershaw8
ID: 13481939
Do you mean for non select sql, sql statement must contain parameters?
0
 
LVL 12

Expert Comment

by:esoftbg
ID: 13482048
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
 
LVL 12

Expert Comment

by:esoftbg
ID: 13482095
> 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
 

Author Comment

by:petershaw8
ID: 13503223
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
 
LVL 12

Expert Comment

by:esoftbg
ID: 13503902
> 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
 
LVL 17

Expert Comment

by:TheRealLoki
ID: 13510253
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
 
LVL 17

Expert Comment

by:TheRealLoki
ID: 13510266
oops ibsql1.ParamByName('ParamPracnam').AsString   not ParamPractname
0
 

Author Comment

by:petershaw8
ID: 13511568
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
 
LVL 12

Accepted Solution

by:
esoftbg earned 160 total points
ID: 13514964
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question