Solved

Delphi 7 :: MS SQL 2005 :: INSERT SQL Statement & Params

Posted on 2007-11-15
6
5,957 Views
Last Modified: 2013-11-23
Hi Experts,

I am building a Delphi 7 app using MS SQL 2005 Express db.
Can you please show me how a correct INSERT, UPDATE and SELECT statement should look if
using ADO components?

I know the SQL syntax, more interested in whether one should use transactions etc?
Please show me complete Delphi code example when inserting a new row using ParamByName() etc...

All advice welcome.

Thank you
0
Comment
Question by:Marius0188
  • 3
  • 2
6 Comments
 

Author Comment

by:Marius0188
ID: 20287656
What I would like to do is exactly as when using TIBQuery component:


IBQuery.SQL.Text := 'INSERT INTO TBLTABLE1 (Name, Surname, BirthDate) VALUES(:Name, :Surname, :BirthDate)';
IBQuery.ParamByName('Name').AsString := 'Marius';
IBQuery.ParamByName('Surname').AsString := 'NA';
IBQuery.ParamByName('BirthDate').AsDateTime := Now;
IBQuery.ExecSQL;

With ADO Query components you don't have "ParamByName().AsDateTime" etc...
How do you insert a date etc type?

And then please remember to answer whether your should use transactions etc...

Thanks!
0
 
LVL 25

Accepted Solution

by:
imitchie earned 250 total points
ID: 20287941
pretty much same as TIBQuery. except parameters.parambyname, and use Value (variant) for everything.  You can even specify datatype if required, just parameters.parambyname('fkd').datatype... (or fieldtype, drawing from memory now)

ADOQuery.SQL.Text := 'INSERT INTO TBLTABLE1 (Name, Surname, BirthDate) VALUES(:Name, :Surname, :BirthDate)';
ADOQuery.Parameters.ParamByName('Name').Value := 'Marius';
ADOQuery.Parameters.ParamByName('Surname').Value := 'NA';
ADOQuery.Parameters.ParamByName('BirthDate').Value := Now;
ADOQuery.ExecSQL;

make the same changes for select, update.  borland has done a good job at keeping things the same.  i would use transactions wherever you use them for ib, because they're both transactional dbs.
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20287949
you don't really have to specify parameter types. they are usually self discovered.  as with tdatabase, tibdatabase (tibconnection?), the transaction is managed at the tadoconnection level.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 21

Assisted Solution

by:developmentguru
developmentguru earned 250 total points
ID: 20288774
 Usually I only worry about using transactions when you need to be sure that more than one update succeeds (or fails) as a group.  If you are writing a purchase order to the database it would do no good to store the header without the items, the items without the header, a partial line item list, etc.  Wrap such a set of record updates in a transaction.

  As far as the query parameters go, I have had my own issues using them.  For one thing, you can't see the query that results from the parameter replacement, this can make it more difficult to debug when you have an issue.  I have run into the problem of having a colon inside text I want to insert into a table that gets recognized as a parameter.  I have run into issues where only one parameter is replaced when I needed it multiple places (you need to use a separate routine to replace multiple).  In general I have started using my own parameter replacement routine.  This gives me the opportunity to examine the resulting query before it gets handed to the datatbase and gets me around some of the sticky parameter issues that arise from using the built in functionality.  One other thing this has allowed me to do is do partial parameter replacement in certain routines, and finish the parameter replacement in others (Object hierarchy where some handle the basic paramters and the parent objects handle the table name).

  As far as using the ADO components, the syntax of the statements is ruled by the database you are connecting to, not the ADO components.

Let me know if you need more, I hope this helps.
0
 

Author Comment

by:Marius0188
ID: 20311613
Can one please show me an example of starting and ending a transaction with MS SQL 2005?

Thanks a lot!
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20311628

ADOQuery.Connection.BeginTrans;

try

  ADOQuery.SQL.Text := 'INSERT INTO TBLTABLE1 (Name, Surname, BirthDate) VALUES(:Name, :Surname, :BirthDate)';

  ADOQuery.Parameters.ParamByName('Name').Value := 'Marius';

  ADOQuery.Parameters.ParamByName('Surname').Value := 'NA';

  ADOQuery.Parameters.ParamByName('BirthDate').Value := Now;

  ADOQuery.ExecSQL; 

  ADOquery.Connection.CommitTrans;

except

  ADOquery.Connection.RollbackTrans;

  raise;

end;

Open in new window

0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now