Date, Int and Boolean null setting in db

anuragkumar
5 years ago #16405

Hi Team,

I am getting some sql error when I try inserting a record which is backed by a simplifier struct (all string fields) and has some fields like Date, Int and Boolean passed as empty strings in the struct.

Is there any way I can pass null in place of empty string in the struct if no data is passed for a field?

I tried typing in ‘NULL’ in for the fields but I think here NULL is getting passed as string. And then the error is coming because of data-type mismatch between ‘String’ and SQL{‘Date’, ‘Int’, ‘Boolean’}.

Regards

Anurag
Chris Bouveret
Has successfully completed the online course Introduction
Has successfully completed the online course Intermediate (200)
Has successfully completed the online course Advanced (300)
Has successfully completed the online course Basics (100)
Has successfully completed the online course Advanced (320)
Has successfully completed the Intermediate Certification
Has successfully completed the Advanced Certification
5 years ago #16493

Up
0
Down
::
Hi there,

to solve this the best way is to create a new data type that is “nullable” and assign it to the fields of your structure that shoulde NULL instead of an empty String.

Hope that helps
anuragkumar
5 years ago #16494

Up
0
Down
::
Hi @Chris,

Thanks for your inputs. Where can I create a datatype that is nullable. I don’t see that option in the struct creation screen attached below.

Regards

Anurag

Attachments:
You must be logged in to view attached files.
Chris Bouveret
Has successfully completed the online course Introduction
Has successfully completed the online course Intermediate (200)
Has successfully completed the online course Advanced (300)
Has successfully completed the online course Basics (100)
Has successfully completed the online course Advanced (320)
Has successfully completed the Intermediate Certification
Has successfully completed the Advanced Certification
5 years ago #16496

Up
0
Down
::
Hi there,

You can create a new Domain Type under “Data Types” that is nullable. So first create your custom datatype as a Domain Type and afterwards you ca assign this datatype to the field in your Structure (currently String). Please see attached Screenshots

Hope this helps

Attachments:
You must be logged in to view attached files.
anuragkumar
5 years ago #16501

Up
0
Down
::
Hi @chris,

I am using below insert query in my connector:

INSERT INTO person (name, age,graduatedOn,birth_date_time,isAlive)
VALUES (:name:, :age:Integer:, :graduatedOn:Date:,:birth_date_time:Date:, :isAlive:);

Here, I am passing value from a struct having all string fields.

There is no problem, when I have set all the fields in the struct and none is empty. Insert query runs fine then.

Problem is in below conditions:

1) when age is empty, then I get below error:

{
“message”: “Invalid payload for connector type SQL: Parameter age had invalid format: JSON Literal for type Integer malformed”,
“success”: false
}

2) When age is filled, then I get error for the date field ‘graduatedOn’, error is below:

{
“message”: “Data truncation: Incorrect date value: ” for column ‘graduatedOn’ at row 1″,
“success”: false
}

Please find below the table description:

How can I deal with this situation, as I can have empty fields in my struct if user has not entered any value for it.

Regards

Anurag

Attachments:
You must be logged in to view attached files.
anuragkumar
5 years ago #16503

Up
0
Down
::
I have tried creating a domain type for nullable string, still I get error while inserting.
Christian Kleinschroth
Has successfully completed the online course Introduction
Has successfully completed the online course Intermediate (200)
Has successfully completed the online course Advanced (300)
Has successfully completed the online course Basics (100)
Has successfully completed the online course Advanced (310)
Has successfully completed the online course Advanced (320)
Has successfully completed the Intermediate Certification
Has successfully completed the Advanced Certification
5 years ago #16511
Up
0
Down
::
Hello @anuragkumar,

we don’t support Date for SQL Queries – so please adopt your query parameter to String see example below:

INSERT INTO person (name, age,graduatedOn,birth_date_time,isAlive) VALUES (:name:, :age:Integer:, :graduatedOn:String:,:birth_date_time:String:, :isAlive:);

and ensure that the Date will be formatted correctly. You can do this with moment.js in server side business objects like

moment().format("YYYY-MM-DD")

For further questions, don’t hestitate to reply
Christian
Has successfully completed the online course Introduction
Has successfully completed the online course Intermediate (200)
4 years ago #19648

Up
0
Down
::
Hello @Christian,

today I tried to create my own Data Type with a nullable String. But as soon I used it in my insert-transaction in the Connector, I was not able to test it.

The program does not react at all and no error message appeared.
Armin Winkler
Has successfully completed the online course Introduction
Has successfully completed the online course Intermediate (200)
Has successfully completed the online course Advanced (300)
Has successfully completed the online course Basics (100)
4 years ago #21779

Up
0
Down
::
Hello all,

passing on null values to a connector is currently only possible in a sort of indirect manner: You simply leave out that parameter whenever you execute the connector call from within a business object. If you pass on a whole struct to the connector and only want to have certain fields set as null whenever no user input was made, you need to handle that explicitly by setting the value of such fields in the struct as undefined. This will only work however, when the column of your table has its default value set to NULL since the undefined coming from the field of the struct will result in that field being ignored from the JDBC driver alltogether. Looking at your table configuration @anuragkumar you should be fine though.

Regards,

Armin