Insert SQL statement using Create Parameter Statement

Insert SQL statement using Create Parameter Statement

Post by Mick » Fri, 13 Oct 2006 18:17:30


Hi I am trying to insert records into an ado dataset using parameters that
are created at run time i have tried various methods but with each method I
hit a different brick wall can anyone help.
I using ado because the connection will either be connecting to oracle or
mssql depending on customer preference of database and i do not want to
write 2 seperate sets of routines.

My sql statement is
insert into uploadlog (added,machineid,logfile) values
(:added,'test',:logfile);

I have tried 2 ways of trying to get the parameters in each gives a
different error message.

------------------------------------
dstQry.SQL.Add('insert into uploadlog (added,machineid,logfile) values
(:added,'''+UserName+''',:logfile)');
DstQry.Parameters.CreateParameter('added',ftDateTime,pdInputOutput,-1,NOW);
DstQry.Parameters.CreateParameter('logfile',ftBlob,pdInputOutput,-1,LogTxt);
try
DstQry.ExecSQL;
except on e:exception do
begin
ShowMEssage('Error Uploading Log File! '+e.Message);
exit;
end;
end;

This gives the error message - OLEDBException multiple step OLEDB operation
generated errors. Check each OLEDB status value. No work was done.

----------------------------------------

The other method i tried is

dstQry.SQL.Add('insert into uploadlog (added,machineid,logfile) values
(:added,'''+UserName+''',:logfile)');

DstQry.Parameters.AddParameter;
DstQry.Parameters.AddParameter;
DstQry.Parameters[0].Name := 'added';
DstQry.Parameters[1].Name := 'logfile';

DstQry.Parameters.ParamByName('added').DataType := ftDateTime;
DstQry.Parameters.ParamByName('added').Direction := pdInputOutput;
DstQry.Parameters.ParamByName('added').Value := NOW;
DstQry.Parameters.ParamByName('logfile').DataType := ftblob;
DstQry.Parameters.ParamByName('logfile').Direction := pdInputOutput;
DstQry.Parameters.ParamByName('logFile').Value := LogTxt;
}
try
DstQry.ExecSQL;
except on e:exception do
begin
ShowMEssage('Error Uploading Log File! '+e.Message);
exit;
end;
end;

This gives the eroor message of - Parameter Object is improperly defined,
Inconsistant or incomplete information is provided.
------------------------------------------

I have tried rmoving the blob paraemeter and just working with the
ftdatetime but the results are still the same.

Any Help graetly appreciated!

Mick!
Forever Onwards is the Call.
 
 
 

Insert SQL statement using Create Parameter Statement

Post by Mick » Sat, 14 Oct 2006 17:19:54

hanks Brian.

Done This already. And yes it works fine. But this method is not practicle
for what i am trying to achieve.
If i explain a bit more of what i am trying to achieve that may help.

I am copying records over a wan from database to database using insert sql
statements. I need to keep bandwidth usage to a minimum so no tables. I get
a list of the tables in the database and then get a list of the fields in
the database and attempt to map them through to an identical database stored
locally.
In adavnce i do not know what fields there will be so i cannot create the
paraemeters at design time!

I have tried to copy the field types exact when creating the parameters but
i still get the same eror messages.
i.e.

SrcQry holds a single record from a table and dstqry is the insert query

for loop = number of fields -
DstQry.Parameters.CreateParameter('p'+IntToStr(loop),SrcQry.Field[loop].DataType,pdInputOutput,SrcQry.FieldByName(SrcQry.Fields[loop].FieldName).Size,SrcQry.FieldByName(SrcQry.Fields[loop].FieldName).Value);

Because i get the exact field type from the srcqry i thought this would have
worked no problem but no it doesn't Am i missing a link here in my thinking?



"Brian Bushay TeamB" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...



 
 
 

Insert SQL statement using Create Parameter Statement

Post by Vitali Kal » Sat, 14 Oct 2006 18:10:44

Why do you trying to create parameters manually in code? In most cases
Delphi wrapper for ADO will handle that for you automatically. So all that
you need is just:
DstQry.SQL.BeginUpdate;
DstQry.SQL.Clear;
DstQry.SQL.Add('insert into uploadlog (added,machineid,logfile) values
(:added,'+QuotedStr(UserName)+',:logfile)');
DstQry.SQL.EndUpdate;
DstQry.Parameters.ParamByName('added').Value := NOW;
DstQry.Parameters.ParamByName('logFile').Value := LogTxt;
DstQry.ExecSQL;

BTW TADOCommand is preffered for executing inserts, updates and deletes.

Regards,
Vitali
 
 
 

Insert SQL statement using Create Parameter Statement

Post by Mick » Sat, 14 Oct 2006 20:29:05

Thanks for that Vitali.
My mistake was assuming that if you didn't create the paraemeters at design
time then manuall code would have to be written for run time but it looks as
though the component sorts it out itself.

Nice!

My preliminary test on blob fields are not lloking too good yet but more
testing required.

MICK!