SMP 3.0 SP08 PL04 Integration Gateway - Create fails on SQL server with Identity Column as PK

[former_member197096]

Hello Experts,

I am running SMP 3.0 Server SP08 PL04. I have a Integration Gateway service deployed which talks to a SQL table in MS SQL Server 2008 R2 via JDBC. This table has a primary key set as an IDENTITY column (autoincremented INT32 type), similar to the one used in the blog by here:

I am able to perform GET requests on my SQL table without any problems. On Step 10, Marvin talks about a Create operation. This is done by omitting the IDENTITY column from the request body as shown in the screenshot (No ID property/value specified). I tried the same thing with my table - but I received an error.

I see this error in my Odata Services -> Troubleshooting tab:

Cannot insert the value NULL into column 'custSiteDetailTypeId', table 'Routebook.dbo.tblCustomerSiteDetailAttributesInfo'; column does not allow nulls. INSERT fails.

If I do send a value for the ID field, I get the common SQL error :

Cannot insert explicit value for identity column in table 'tblCustomerSiteDetailAttributesInfo' when IDENTITY_INSERT is set to OFF.

This error ^ basically means that I need to modify my SQL to be able to successfully INSERT the value I am sending - but this isn't my goal as I am filtering the table to bring down only a subset of the records.

I need to be able to perform a GET (Read), POST (Create) and PUT (Update) operations on this table. 

Any idea how to solve this one?

Thanks,

Aditya

[Jitendra_Kansal · Accepted solution]

Can you confirm, are you able to INSERT a new record into SQL table without providing ID value?

Have you also changed data type of ID property in OData model?

Can you share the screenshots of POST request (request header, payload, response header) if you are testing it in REST client?

Enable log component of IGW in SMP Admin cockpit, you may get more info under 'Troubleshooting' Section for your issue.

Regards,

JK

[former_member197096 · Accepted solution]

JK,

Thanks for your response.

1. Yes, I can confirm I am able to insert a new record directly in SQL server when I do not provide the ID value (in fact the column is excluded completely from the INSERT statement)

2. The Data type of the ID property is Int32 - here is the table in the service metadata.

<EntityType Name="Notes_Write">

                <Key>

                    <PropertyRef Name="custSiteDetailId"></PropertyRef>

                </Key>

                <Property Name="custSiteDetailId" Type="Edm.Int32" Nullable="false"></Property>

                <Property Name="deliveryPointID" Type="Edm.Int32"></Property>

                <Property Name="detailStatus" Type="Edm.String" MaxLength="5"></Property>

                <Property Name="custSiteDetail" Type="Edm.String" MaxLength="256"></Property>

                <Property Name="sequence" Type="Edm.Int16" Nullable="false"></Property>

                <Property Name="createdDate" Type="Edm.DateTime" Precision="8"></Property>

                <Property Name="updatedDate" Type="Edm.DateTime" Precision="8"></Property>

                <Property Name="createdBy" Type="Edm.String" MaxLength="25"></Property>

                <Property Name="updatedBy" Type="Edm.String" MaxLength="25"></Property>

                <Property Name="custSiteDetailTypeId" Type="Edm.Int32" Nullable="false"></Property>

                <Property Name="customerId" Type="Edm.Int32"></Property>

                <Property Name="routeId" Type="Edm.Int32"></Property>

</EntityType>

3. Here are the screenshots:

     a. Request Body:

     b. Request Headers:

     c. Response Headers:

My IGW logs are already set to debug levels in the Admin console - but I didn't find much more to the error than what I posted initially.

Regards,
Aditya

[Jitendra_Kansal · Accepted solution]

Looks fine to me.

When you try POST operation directly to the Odata service exposed via IGW, then also you experience same error message? (not via SMP appid)

What about PUT/DELETE ? same error?

Try with this, remove x-smp-appcid header, call a POST on this url,

http://smp3server:8080/gateway/odata/sap/intgwtest;v=1/CUSTOMER

Also, You are passing one property in payload like:

<d:updatedBy m:null="true"></d:updateBy>

How exactly it is being handled? Can you share the SQL command of inserting a record directly in database?

Regards,

JK

[former_member197096 · Accepted solution]

Hey JK,

Thanks for your quick responses - unfortunately I am not able to keep up

So first I tried to POST directly to the Odata service exposed from IGW, but I do not seem to get the X-CSRF-TOKEN back from a GET operation so am unable to test that out (this has happened before, it returns most of the time but sometimes its very stubborn) - so I'll try again tomorrow.

if you notice the metadata in my first post - the `updatedBy` field can be null. This usually translates to a 'null' value in the INSERT statement.

Now, regarding the original query - I tracked the record I was trying to insert with the backend SQL server. It seems the record is getting inserted after all! It was all there, just as I sent it from PostMan.

But from an offline OData perspective, the error would still mean a failure for the device making the flush call. I just might report this to SAP via a OSS message.

[former_member197096 · Accepted solution]

I used a workaround to resolve this issue by creating another IGW entity just to insert records into the table. I use the original table (which was built as a SQL view) for Reads and Updates.

Maybe there will be functionality in the future to help with this.

Cheers,

Aditya