[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Properly using Interbase Dataset.InsertSQL in Delphi

Posted on 2007-10-18
7
Medium Priority
?
1,069 Views
Last Modified: 2013-12-09
Hello, All
I use interbase and delphi fairly often. I am trying to insert a record into a table using a dataset and InsertSQL. The primary key (MID) uses a generator and trigger
      begin new.MID = Gen_ID(QANES_GENERATOR, 1); end
to behave as an autoincremented value.
I created a dataset in delphi with the INSERTSQL statement reading
"INSERT INTO MYTABLE (FIELD01) VALUES  ('TEST')"
and this has been added to the MyDataset.InsertSQL.
When I try to use the following statments
     MyDataset.Open;
     MyDataset.Insert;
     MyDataset.Post;
    MyDataset.Close;
Interbase responds with MID must have a value. Interbase is supposed to assign the value automatically, no? I suppose I could figure out the number by querying the generator and assigning it manually, but I figure I'm doing something wrong.

Perhaps the question should be: Can someone give me an example of inserting a blank, new record with the primary key ID set automatically?

Thanks


0
Comment
Question by:dkeene
  • 4
  • 2
7 Comments
 
LVL 13

Expert Comment

by:rfwoolf
ID: 20101668
Only trying to help...
In your dataset, if you create a persistent field for FIELD01 and set its field type fkCalculated. ...
Other than that I don't know this one...
0
 
LVL 19

Expert Comment

by:Gary Benade
ID: 20102024
Not familiar with the IB componets, I prefer IBObjects, but you could try setting insertsql to:

INSERT INTO MYTABLE (MID,FIELD01) VALUES  (Gen_ID(QANES_GENERATOR, 1),'TEST')

0
 
LVL 19

Expert Comment

by:Gary Benade
ID: 20102185
If my previous suggestion didnt work, it should have, you could also try changing it to
INSERT INTO MYTABLE (MID,FIELD01) VALUES (:MID,:FIELD01)
This probably wont work, but check out page 35 of this document
http://grdelin.phy.hr/~ivo/Nastava/Baze_podataka/ekstra/firebird/DevGuide.pdf

updatesql would look something like
UPDATE MYTABLE SET FIELD01=:FIELD01 WHERE MID = :OLD_MID
and deletesql
DELETE FROM MYTABLE WHERE MID = :OLD_MID

HTH
Gary

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 19

Expert Comment

by:Gary Benade
ID: 20106827
Solved, sorry for the initial wild goose chase

MyDataset.Open;
MyDataset.Insert;
MyDataset.FieldByName('MID').Required := False;
MyDataset.Post;
MyDataset.Close;

Regards
Gary
0
 

Author Comment

by:dkeene
ID: 20109282
Hobbit
Thanks for your help. It works, but the Generator number was incrementing by two each time I added a record. i changed the Gen_ID function to increment by zero and it worked. Is this because calling the GEN_ID function once during the SQL and once by Interbase in the normal insert process causes incrementing by two?

THanks
0
 
LVL 19

Accepted Solution

by:
Gary Benade earned 2000 total points
ID: 20110367
You dont need to make any of the other changes I mentioned, the trigger will do its job without futher changes, leave the tigger as you had it and change your insertsql back to:

INSERT INTO MYTABLE (FIELD01) VALUES  ('TEST')
or
INSERT INTO MYTABLE (FIELD01) VALUES  (:FIELD01)
to allow the proper use of ParamByName to populate field values


0
 

Author Comment

by:dkeene
ID: 20111390
Hobbit you're the greatest thanks for your help!
Doug
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

834 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