Insert / Update problem on MaxDB

[Former Member]

Hi,

if it used zero-length string as a value for INSERT INTO statement, it produces error.

Is it possible insert/update zero-length string? How?

Thanks.

[lbreddemann · Accepted solution]

> Hi,

> if it used zero-length string as a value for INSERT INTO statement, it produces error.

> Is it possible insert/update zero-length string? How?

What kind of error do you get?

Why shouldn't it work?

sqlcli db76=> create table aaa (col1 varchar(10), col2 varchar(10))
0 rows affected 

sqlcli db76=> insert into aaa values ('', '')
1 row affected 

sqlcli db76=> select count(*) from aaa
| EXPRESSION1        |
| ------------------ |
|                  1 |

1 row selected 

sqlcli db76=> select * from aaa
| COL1       | COL2       |
| ---------- | ---------- |
|            |            |

1 row selected

sqlcli db76=> select * from aaa where col1 =''
| COL1       | COL2       |
| ---------- | ---------- |
|            |            |

1 row selected 

And yes, it's a real zero-length string and not a NULL value as it is in Oracle.

But only if your SQL mode is INTERNAL.

sqlcli db76=> select * from aaa where col1 is null
* 0:

Don't see any errors here...

Let's switch to ORACLE sql mode:

sqlcli db76=> \m ORACLE
SQL mode changed to ORACLE
sqlcli db76=> select * from aaa where col1=''
* 0:

The empty string ('') is now handled as NULL, so the zero-length string does not match anymore.

sqlcli db76=> select * from aaa where col1 is null
* 0:
sqlcli db76=> select * from aaa
| COL1       | COL2       |
| ---------- | ---------- |
|            |            |

1 row selected (0 usec)

Still the values in the table are not NULL values now.

Let's make up a ORACLE mode NULL value:

sqlcli db76=> insert into aaa values ('', 'X')
1 row affected 

sqlcli db76=> select * from aaa
| COL1       | COL2       |
| ---------- | ---------- |
|            |            |
| ?          | X          |

2 rows selected

sqlcli db76=> select * from aaa where col1 is null
| COL1       | COL2       |
| ---------- | ---------- |
| ?          | X          |

1 row selected 

regards,

Lars

[Former Member]

x

[lbreddemann]

x ??

You get a complete explanation of your issue and you cannot even state whether this helped you or if there are further questions?

You're not too much into this "community-social-stuff" are you!

regards,

Lars