Board index » delphi » cant update from tibdataset

2007-09-07 03:56:51 PM
delphi263
i can not update a statement using tibdataset,
the selectsql have no where clause:
select
b.nombre_platillo,a.nombre_producto,
c.costo_producto,e.gramaje_prod,d.nombre_unidad,
a.cve_producto, b.cve_platillo, c.cve_cto_prod,
e.cve_ctoc_plat,d.cve_unidad
from productos a, platillos b, costos_prods c,
unidades d, cto_comp_plat e
where a.cve_producto=c.cve_producto and
b.cve_platillo=e.cve_platillo and
c.cve_cto_prod=e.cve_cto_prod and
d.cve_unidad=e.cve_unidad
group by b.nombre_platillo,a.nombre_producto,
c.costo_producto,e.gramaje_prod,d.nombre_unidad,
a.cve_producto, b.cve_platillo, c.cve_cto_prod,
e.cve_ctoc_plat,d.cve_unidad
having max(fecha_real_ctopr)=max(fecha_real_ctopr)
order by b.nombre_platillo, a.nombre_producto
and the update statement is:
update cto_comp_plat
set
GRAMAJE_PROD = :GRAMAJE_PROD,
CVE_CTO_PROD = :CVE_CTO_PROD,
CVE_UNIDAD = :CVE_UNIDAD
where
CVE_CTOC_PLAT = :OLD_CVE_CTOC_PLAT
generated by de dataseteditor.
When i try to update the table i do the following
if siactocmplat.Locate('cve_platillo;cve_cto_prod',
varArrayOf([platillose.keyvalue, siactocmplat.fieldbyname('cve_cto_prod').AsInteger]),[locaseinsensitive]) then
..
siactocmplat.Edit;//siactocmplat=ibdataset name
updatefunction(siactocmplat,params);
..
function updatefuncion(qryp:tobject;...)
..
with qryp as tibdataset do
begin
params[0].Value:=par;//in this line the error mentioned belos raises
params[1].value:=par2;
params[2].value:=par3;
params[3].value:=par4;
post;
applyupdates;
..
the error XSQLDA index out of range is raised when trying to
fill params..
i already set paramcheck to true and false.
i first generate the modifiysql manually, but it isn't works, so
i then generated from dataset editor.
why it doesn't link those parameters?
d7, ib2007
best regards
 

Just assign field values then post:
with qryp do
begin
FieldByName('RAMAJE_PROD').Value := par;
FieldByName('CVE_CTO_PROD').Value := par2;
...
Post;
end;
Regards.
--- posted by geoForum on delphi.newswhat.com
 

jorge novoa writes:
first and you do not need to call ApplyUpdates.
with qryp as tibdataset do
begin
Edit;
FieldByName('GRAMAJE_PROD').Value:=par;
FieldByName('CVE_CTO_PROD ').value:=par2;
FieldByName('CVE_UNIDAD').value:=par3;
FieldByName('CVE_CTOC_PLAT').value:=par4;
post;
end;
You should also avoid using the Value property of the fields, use the AsXXX
properties instead, e.g.
FieldByName('CVE_CTOC_PLAT').AsInteger:=par4;
...(if par4 is an integer).
--
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"We've all heard that a million monkeys banging on a million
typewriters will eventually reproduce the entire works of Shakespeare.
Now, thanks to the Internet, we know this is not true." ?Robert
Wilensky
 

"ssamayoa" <XXXX@XXXXX.COM>writes:
above) to get a lot of
dbcomponents(tibdataset,tibquery,tzquery), thats why i use the
index params and perform operations(select,insert,update,etc)
REgards.
 

jorge novoa writes:
any other TDataset descendant I know of. You insert/edit/update the record and
then on posting the Dataset transfers the changes you made to the local record
to the DB. IBX just gives you better control on how that update is done, but
you do not manipulate the parameters directly you always manipulate the fields
and then IBX will handle transferring that change to the correct parameters
during the post phase.
--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
And so I patrol in the valley of the shadow of the tricolor
I must fear evil. For I am but mortal and mortals can only die.
Asking questions, pleading answers from the nameless
faceless watchers that stalk the carpeted corridors of Whitehall.
(Fish)
 

"Wayne Niddery [TeamB]" <XXXX@XXXXX.COM>writes:
because i recieve different components in that function like
dbcomponents(tibdataset,tibquery,tzquery), thats why i use the
index params and value insted of the as.. and then perform operations(select,insert,update,etc).
Regards.
 

jorge novoa writes:
Edit and assigning the field values no matter what the the actual underlying
descendant is.
allowed nor have access to the ModifySQL's parameter list. You can only access
the SelectSQL's parameters. You are fighting IBX here and you will lose if you
continue to do so. Treat it just like you would treat TQuery or TClientDataset.
Put the current record in Edit mode, change the field values you want to
change and post.
should with any C/S backend.
--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
And so I patrol in the valley of the shadow of the tricolor
I must fear evil. For I am but mortal and mortals can only die.
Asking questions, pleading answers from the nameless
faceless watchers that stalk the carpeted corridors of Whitehall.
(Fish)
 

ssamayoa writes:
compatible with TIBQuery, TQuiery, TClientDataset etc. You call
Edit,Insert,Append,Delete followed by assigning field vaules (except for delete)
and calling post (again except for delete). How you setup a TIBDatset is
different, but actually using it is no different than any other TDataset descendant.
to each dataset descendant you use if there is anything special (but for
updating there really shouldn't be).
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
And so I patrol in the valley of the shadow of the tricolor
I must fear evil. For I am but mortal and mortals can only die.
Asking questions, pleading answers from the nameless
faceless watchers that stalk the carpeted corridors of Whitehall.
(Fish)
 

TZQuery because TIBDataSet contains 5 SQL statements: select, insert,
update (modify), delete and refresh (record). You just cant do waht you
want to do with TIBDataSet the way you want.
Why just dont rely on component behaviour instead of "saving" some lines
of code writing generic functions?
Regards.
--- posted by geoForum on delphi.newswhat.com
 

"Jeff Overcash (TeamB)" <XXXX@XXXXX.COM>writes:
When i execute delete, insert or update statements, i alwayas
use tibxx.params[n].asxx or tibxx.params[n].value, then call
tibxx.execsql, well i almost use tibquery, but looks that
tibdataset haven't the same behaviour. i make this because i
have most control over the parameters, because if i change the
name of the field i will have to change it in the code too, but
from this way using indexed params i do not need to re write
code if i change something in the db
 

params on tibdataset you must be able to use it as you like.
Besides each time you begin develop an app. you will write the
same code the same logic one and another and so on times, i
don't how you design your apps., and i try tu saving time more
than lines
Regards
 

time, well design, and savin the same programmating logic and
why do the same task hundred of times, if i have in my app. 15
tibquerys or tibdatasets o tibsql, and all have 4 parameters
and another 20 with 8 params, and 10 with 2 params, etc. why
write the same code fif{*word*249}th or more times, i really don
understand you point of view.
Besides i use those function as a library each time i begin
develop an app. Software engineering.
Please correct me if my appretiation is wrong.
Best regards.
 

You never use TIBDataSet to execute INSERT, UPDATE or DELETE statements
directly. The only parameters you ever set in TIBDataSet are the
parameters in the SELECT. TIBDataSet is designed to allow interactive
editing of data using data aware controls. It is not designed to let
you execute INSERT, UPDATE or DELETE statements. If you want to execute
INSERT, UPDATE or DELETE statements in code use TIBSQL. You can also
execute INSERT, UPDATE and DELETE statements using TIBQuery but doing
so imposes unnecessary overhead that you avoid if you use TIBSQL.
--
Bill Todd (TeamB)
 

different behaviours.
I often use tibquery or tquery or tzquery, and the way i save,
delete or update records are something like this:
first write sqlstatement on the componenent(tibquery,..) sql
property(insert into table values(:f1,..,:fn) for example) then
set the params to it corresponding data type. After that i code
the necesary lines to save, delete or update records like this:
..
ibquery1.close;
ibquery1.params[0].asinteger:=x;
ibquery1.params[1].asstring:=editx.text;
..
ibquery1.params[n].asxx:=something;
ibquery1.execsql//because are not select statement
as you see i didn't use append or insert or post, firstly
because this component just contains one statement and it
aren't open, and if it was open it it retrieve readonle
dataset, besides i thought this way you do the things in the
server side, and using edit, poset, etc. you do tasks at client
side. I almost didn't use tibdataset or tdataset, etc., because
this kind of components leave freedom the user to manipulate
data more than a tibquery for example. I like to be restrictive
with the users and control what exactly ther are or want to do.
However if this way of coding are wrong, then why tquery,
tibquery, etc. allow developers as me to access params option
or property to manipulate it.
I thought tibdataset, ttable, tibtable, are good components
when you use one table or tow nested tables and when you want
to leave all work to client app. and leave the user free to do whatever they want, but for complex querys and
client/server or multitier apps. enviroments its better trying
to avoid the use of this components.
Just my point of view, if I am wrong please let me know i'm
always willing to learn and if is necesary to change the way i
programming to improve my skills and develop better
applications which has better performance and been faster.
Best Regards.
 

"Bill Todd" <XXXX@XXXXX.COM>writes:
sqlstatement its a complex query, i mean the sqlselect contains
a complex joined select statement(could be a view), but inserts
or update sql statements are just for one table. for example:
Selectstatement:
select
b.nombre_platillo,a.nombre_producto,
c.costo_producto,e.gramaje_prod,d.nombre_unidad,
a.cve_producto, b.cve_platillo, c.cve_cto_prod,
e.cve_ctoc_plat,d.cve_unidad
from productos a, platillos b, costos_prods c,
unidades d, cto_comp_plat e
where a.cve_producto=c.cve_producto and
b.cve_platillo=e.cve_platillo and
c.cve_cto_prod=e.cve_cto_prod and
d.cve_unidad=e.cve_unidad
group by b.nombre_platillo,a.nombre_producto,
c.costo_producto,e.gramaje_prod,d.nombre_unidad,
a.cve_producto, b.cve_platillo, c.cve_cto_prod,
e.cve_ctoc_plat,d.cve_unidad
having max(fecha_real_ctopr)=max(fecha_real_ctopr)
order by b.nombre_platillo, a.nombre_producto
this statement retrieve a set of rows filtered by the last date(suppose this is the selectsql on tibdataset), and if the user want to modify or insert a record, in this case just one table will be affected(cto_comp_plat):
Table cto_comp_plat fields=CVE_CTOC_PLAT,CVE_PLATILLO,
CVE_CTO_PROD,ENTERO,GRAMAJE_PROD,CVE_UNIDAD
cve_ctoc_plat is the primary key for this table and its an
autogenerated field(by a generator), the oter fields are
secondary indexes belonging to different tables except the
field GRAMAJE_PROD that its a decimal value set it by the user.
how can i do interactive user records fill with something like this
i have a tquery for insert and a tibsql for update:
insert into cto_comp_plat(cve_platillo,cve_cto_prod,gramaje_prod,cve_unidad)
values (:cvepla,:cvctoprd,:gramprd,:cvuni)
update cto_comp_plat
set
CVE_CTO_PROD = :CVECTOPR,
GRAMAJE_PROD = :GRAMPROD,
CVE_UNIDAD = :CVUNIAD
where
CVE_CTOC_PLAT = :CVCTOCPL