SQL Server/Insert/ID

Q&A
1

Hey folks,

A little confused over this one. Imagine I have a load of locations and people and that people can belong to multiple locations. M:M

I’d have this set of tables in SQL server:

Person
PersonID
PersonName

Location
LocationID
LocationName

Location_Person_Link
LPID
LocationID
PersonID

Now, in bonita, I can have a process for somebody to add ‘PersonName’ to a form and to select a/list of locations that the person belongs to. The next step could be a connector to save person to the database. SQL sever would then make a new rown with the next id/auto id: PersonID.

How do I get this back from the insert connector? The next step would then be to insert into Location_Person_Link usoing that ID…

Many thanks,
Jim

2

So, I want to do an insert sql server connector in bonta and have the id of the new field returned to me.

Does bonita support: SELECT SCOPE_IDENTITY()

Code below:
INSERT INTO “dbo”.“ServiceLocations” (“ServiceName”, “Active”)
VALUES (‘${serviceLocationName}’, ‘Yes’)
SELECT SCOPE_IDENTITY()

Will this in bonita work to bring back inserted record id?

3

Anybody?

4

Does anybody have any idea on this?

I am using the following:

INSERT INTO “dbo”.“ServiceLocations” (“ServiceName”, “Active”) VALUES (‘TEST16’, ‘Yes’) SELECT @@IDENTITY as ‘myID’

Will bonita be able to get myID?