null fields in oracle

[jrheisler]

I am using both SQL Server and Oracle in an application. The user chooses which one on login, and they are connected to their database.

We have the same virtually identical tables and fields in both sql server and oracle. Many fields are set to NOT NULL. I store a ' ', blank space to the fields that are empty, to make them not null. If I do so with an insert statement, it works fine in both products. If I do so with a TUniQuery, and set the field (qry.FieldByName('myfield').asString := ' ') the field get's trimmed anyway.

This happens in both sql server and Oracle. Sql Server considers the field not null, but Oracle doesn't and throws up an error ora-01400 cannot insert NULL into ... basically the same field I just tried to set to ' ' a blank space.

Is there a setting I need to set some where to keep the blank space?

Thanks!

[AlexP]

hello,

I cannot reproduce the problem.
Please specify the exact version of the UniDAC and script for creating table.

[jrheisler]

This is the table it is happening on:


Column Name ID Pk Null? Data Type Default Histogram Encryption Alg Salt

CHG_TYPE 1 1 N VARCHAR2 (4 Byte) Yes
CHG_NUM 2 2 N VARCHAR2 (30 Byte) Yes
CHG_CAGE 3 3 N VARCHAR2 (6 Byte) Yes
NOR_NUM 4 4 N VARCHAR2 (32 Byte) Yes
CLASS 5 N VARCHAR2 (2 Byte) Yes
ACTION 6 N CHAR (1 Byte) Yes
PART_NUM 7 5 N VARCHAR2 (32 Byte) Yes
PART_CAGE 8 6 N VARCHAR2 (6 Byte) Yes
END_NUM 9 7 N VARCHAR2 (32 Byte) Yes
END_CAGE 10 8 N VARCHAR2 (6 Byte) Yes
CI_NUM 11 9 N VARCHAR2 (32 Byte) Yes
EFF_FROM 12 10 N VARCHAR2 (15 Byte) Yes
TO_AND 13 11 N VARCHAR2 (4 Byte) Yes
EFF_TO 14 12 N VARCHAR2 (15 Byte) Yes
EVENT_FROM 15 N VARCHAR2 (15 Byte) Yes
EV_TO_AND 16 N VARCHAR2 (4 Byte) Yes
EVENT_TO 17 N VARCHAR2 (15 Byte) Yes
DESCRIPTION 18 N VARCHAR2 (36 Byte) Yes
ON_ORDER 19 N CHAR (1 Byte) Yes
IN_PROCESS 20 N CHAR (1 Byte) Yes
ORDER_COMP 21 N CHAR (1 Byte) Yes
ASSEMBLED 22 N CHAR (1 Byte) Yes
DELIVERED 23 N CHAR (1 Byte) Yes
MFR_NUM 24 N VARCHAR2 (32 Byte) ' ' Yes
MFR_NAME 25 N VARCHAR2 (80 Byte) ' ' Yes
MFR_CAGE 26 N VARCHAR2 (6 Byte) ' ' Yes


After further examination, it looks like it happens to any "CHAR" field.

Sorry, I am a little dense this am, where can I find the version number for UniDac?

[AlexP]

Hello,

When working with fields of the CHAR type, we cut trailing spaces in a string by default. To avoid this, you should set the TrimFixedChar property to False

Code: Select all

UniQuery1.Options.TrimFixedChar := False;

In this case, the value you entered will be inserted instead of NULL.