Home » Archived » EGL Development Tools » Insert Record with Identity (auto-generate)
| Insert Record with Identity (auto-generate) [message #998366] |
Wed, 09 January 2013 16:40  |
Eclipse User |
|
|
|
Anyone familiar with inserting a record into a table (MS SQL) that is defined with an identity (autogenerate) key?
I can't seem to get the syntax right.
I added the @GeneratedValue to my record definition:
record Suspension_seq { @table{name = "suspension_seq"}}
seqnum int{@id, @GeneratedValue, @Column { name="seqnum" } };
dummy int?;
end
Here is the syntax of my database add function:
Function addPatronSuspensionSeq(newRecord suspension_seq out)
newRecordin suspension_seq;
logEntry("addPatronSuspensionSeq");
try
add newRecordin to dataSource with
#sql{
insert into dbo.suspension_seq
(dummy)
values
(0)
};
get newRecord from dataSource with
#sql{
select max(seqnum) from dbo.suspension_seq
};
onException(ex sqlException)
logException(ex);
end
end
I can't get past this error, which I think is just a general syntax error:
Exception: SQLService, addPatronSuspensionSeq, SQLSTATE = S1093, message = The index 1 is out of range.: [sqlstate:S1093][sqlcode:0]
Thanks,
Gregory
|
|
|
| Re: Insert Record with Identity (auto-generate) [message #998679 is a reply to message #998366] |
Thu, 10 January 2013 08:50  |
| Eclipse User |
|
|
|
Gregory,
I can't say why your example didn't work but I have managed to get this working using the sample application that is built as part of the 'Access a database with EGL Rich UI' tutorial on the main EDT project site.
This creates a new table in a Derby DB, using the following SQL ...
CREATE TABLE PAYMENT (
PAYMENT_ID INT
PRIMARY KEY NOT NULL
GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
CATEGORY INT,
DESCRIPTION CHAR(30),
AMOUNT DECIMAL(10,2),
FIXED_PAYMENT SMALLINT,
DUE_DATE DATE,
PAYEE_NAME CHAR(30),
PAYEE_ADDRESS1 CHAR(30),
PAYEE_ADDRESS2 CHAR(30))
I then created a test service part ...
package services;
import records.paymentRec;
service TestSQLService
ds SQLDataSource?{@Resource{uri="binding:Derby"}};
function addTestPayment(newPayment paymentRec in)
//logEntry ( "addPayment" ) ;
try
add newPayment to ds
with #sql{
insert into PAYMENT
(CATEGORY, DESCRIPTION, AMOUNT, FIXED_PAYMENT, DUE_DATE,
PAYEE_NAME, PAYEE_ADDRESS1, PAYEE_ADDRESS2)
values
(?, ?, ?, ?, ?, ?, ?, ?)
};
onException(ex sqlException)
SysLib.writeStdout(ex.message);
end
end
end
And a test handler part ...
package handlers;
// RUI Handler
import org.eclipse.edt.rui.widgets.Button;
import org.eclipse.edt.rui.widgets.GridLayout;
import org.eclipse.edt.rui.widgets.GridLayoutData;
import records.paymentRec;
import services.SQLService;
handler TestUI type RUIhandler{initialUI =[ui], onConstructionFunction = start, cssFile = "css/PaymentClient.css", title = ""}
ui GridLayout{columns = 3, rows = 4, cellPadding = 4, children = [ CreateRecord ]};
CreateRecord Button{ layoutData = new GridLayoutData{ row = 2, column = 2 }, text = "Create", onClick ::= CreateRecord_onClick };
dbService SQLService?; // EDT 0.8.0
dedicatedServiceBinding HttpProxy; // EDT 0.8.0
function start()
end
function CreateRecord_onClick(event Event in)
call dbService.addPayment(new paymentRec)
using dedicatedServiceBinding
returning to recordAdded
onException serviceExceptionHandler;
end
function recordAdded()
end
function serviceExceptionHandler(ex anyException)
end
end
When I pressed the Create button it successfully created an empty record in the Derby table with an auto incremented value in the PAYMENT_ID field.
If this small sample doesn't shed any light on to the problem then could it something to do with the DB table definition or possibly the driver being used?
Richard
|
|
| |
| Re: Insert Record with Identity (auto-generate) [message #1006030 is a reply to message #998366] |
Tue, 29 January 2013 10:31 |
| Eclipse User |
|
|
|
fyi...
Just in case someone needs the syntax of adding the value of an auto-generated column into another column of the same or another table, this is what worked for me...
Function addPatronSuspension(newRecord suspension in)
logEntry("addPatronSuspension");
try
add newRecord to dataSource with
#sql{
declare @ID int;
insert into dbo.suspension_seq
(dummy)
values
(?);
set @ID = SCOPE_IDENTITY();
insert into dbo.suspension
(suspension_key, user_key, catalog_key, call_sequence,
copy_number, library, date_of_resumption,
date_suspended, number_of_units, suspension_reason,
date_time_charged, date_time_due, date_recalled,
date_time_item_renewed, date_time_item_returned,
circulation_rule, suspension_type,
linked_suspension_key, unsuspension_reason)
values
(@ID, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
};
onException(ex sqlException)
logException(ex);
end
end
|
|
|
Goto Forum:
Current Time: Sat May 17 17:30:23 EDT 2025
Powered by FUDForum. Page generated in 0.03417 seconds |
Home