Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EGL Development Tools » Insert Record with Identity (auto-generate)
Insert Record with Identity (auto-generate) [message #998366] Wed, 09 January 2013 21:40 Go to next message
Gregory Testa is currently offline Gregory TestaFriend
Messages: 28
Registered: April 2012
Junior Member
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 13:50 Go to previous messageGo to next message
Richard Moulton is currently offline Richard MoultonFriend
Messages: 92
Registered: August 2011
Location: Devon, UK
Member
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 #998837 is a reply to message #998679] Thu, 10 January 2013 19:51 Go to previous messageGo to next message
Gregory Testa is currently offline Gregory TestaFriend
Messages: 28
Registered: April 2012
Junior Member
Looks like a database table definition issue...the indentity column in this table is not a primary key. Looks like EDT doesn't know how to insert a row when there is no primary key defined.
Re: Insert Record with Identity (auto-generate) [message #1006030 is a reply to message #998366] Tue, 29 January 2013 15:31 Go to previous message
Gregory Testa is currently offline Gregory TestaFriend
Messages: 28
Registered: April 2012
Junior Member
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
Previous Topic:PhoneGap and Dojo Mobile
Next Topic:Widget Instantiability Confusion
Goto Forum:
  


Current Time: Sat Dec 20 19:26:26 GMT 2014

Powered by FUDForum. Page generated in 0.01675 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software