Assigning Empty Strings to String Field Throws OpenSQLException

[detlev_beutner]

Hi there,

I have defined a DB table with different fields, one of these is field COMMENT, type set to string, Dim to 1024, that means real type Varchar (when controlling via SQL Studio; within the DDIC Project in Netweaver Developer Studio, it says LONGVARCHAR), code is UNICODE, Not Null not set.

When I insert a row and there is no comment, an empty string is set for COMMENT. But this throws a com.sap.sql.log.OpenSQLException: Cannot assign an empty string to host variable

Why this?

When I control the string before and set the field to null instead of the empty string, it "works".

Thanks in advance

Detlev

[katarzyna_fecht]

Hi Detlev,

Your example demonstrates excellent that Open SQL denies storing empty strings as values of table fields. Namely, an empty string is not portable across DBMS supported by SAP Web AS. For example, an empty string is implicitly converted to NULL by at least one of them.

It means, with connection pools labeled "Open SQL", you can rely on your SQL and JDBC statements run - and behave equal - on each DBMS supported by SAP Web AS. For example, you have found out that you can work with NULL values - and you know now that the NULL value is portable across all supported DBMS.

The same holds for table and type definitions in DDIC (Java Dictionary) editor. Your table (and type) definitions are portable across supported DBMS. The Java Dictionary built-in type string with length 1024 is mapped to the JDBC type longvarchar. On MAXDB, the corresponding type is varchar.

Message was edited by: Katarzyna Fecht

[detlev_beutner]

Hi Katarzyna,

ok, so far, but I have some problems to understand why the conversion from an empty string to NULL ist not done by OpenSQL. If I have no possibility to store an empty string as an empty string, why not converting it within OpenSQL? If this is documented, everybody knows this and could react if he/she wants another reaction on empty strings, but the normal way will be to store empty strings as NULL, if I cannot store them as empty strings. With the situation as it is now, one million OpenSQL developers are converting empty strings to NULL. Hm, not so nice...

Best regards

Detlev

[katarzyna_fecht]

Hi Detlev,

Actually, the semantics of an empty string is not the same as of a Null value, right? This is one of the reasons why (automatically or manually) replacing each empty string with a Null is possibly not the best solution. The recommended way is to use a blank (a string with the length 1 containing one space character) instead of an empty string, for example, as an initial value of a comment field.

[detlev_beutner]

Hi Katarzyna,

the problem stays the same, the semantics of an empty string must not be the same as of a one-space-string, too. (I definitely would state that NULL is somewhat "more equal" to an empty string than a single space is.)

Some additions:

Since yesterday, storing empty strings works, and what is the best: It works as I have proposed, so NULL ist stored. Is there any documentation to be found where it is definitely stated how the behaviour should be (seems to be very unstable).

Second: Storing strings ending with a blank, but "real" char(s) in front, leads to the following OpenSQLException: "Cannot assign a blank-padded string to host variable ..."

Now one million OpenSQL-Developers have to trim the strings stored by OpenSQL instead of having OpenSQL doing so? That seems very strange...

Thanks in advance

Detlev

[Former Member]

Hi Detlev,

There is documentation on these topics. Please, if you haven't done so already, read the chapter Open SQL Data Types under Development Manual -> Developing Business Logic -> Java Persistence -> Relational Persistence at the following link:

http://help.sap.com/saphelp_nw04/helpdata/en/13/a3bb3eff62847ae10000000a114084/frameset.htm

It would be nice to know if you find it helpful as well.

Best regards,

Zornitsa

[detlev_beutner]

Hi Zornitsa,

thanks for the link, that is at least some entry point. Both problems are mentioned there, even if I cannot understand the behaviour of OpenSQL within this context.

I think, storing empty strings should result in storing NULL, and storing strings ending with spaces should result in trimming them at the end.

Nevertheless, now I know that I have to do these tasks by myself, which does not make me happy, but it's better than not knowing this at all...

Thanks & best regards

Detlev

[Former Member]

Dear all

11 years have passed, problems have been described by Detlev are still relevant to current situation. Are there any fixes insight?

cheers

[detlev_beutner]

Hi Lawrence,

Ah, what a great and funny moment to receive the mail about this updated thread - first I read the topic, thinking: Hmmm, that was something I stumbled over more than ten years ago - and then the text 🙂

Anyhow, the situation is still the same Zornitsa described 11 years ago; the relevant current documentation can be found here: Character Strings - Developing Java EE 5 Applications - SAP Library

As stated at that time, this implementation throws more question marks than everything else (even the doc realizes the contradiction between "single space = empty string" and "no blank-padded strings allowed"). As mentioned 2004, I think this could have been implemented otherwise and better. Anyhow, as this has been the way for over ten years now, the basic logic won't be changed.

Nevertheless, openSQL could handle empty strings with it's own logic, so for example by converting it to NULL or to a single space; the best would be to have a switch implemented, like a property "opensql.emptystringconversion" with three possible values "none" (like currently handled) / "null" / "emptystring". That would be not much effort to implement but would take this task out of the hand of each openSQL developer.

I have to admit that I find it sad that this has not been done for ten years, even if everyone knows about the problem. And a good design thinking in IT always should implement standard tasks at a central point...

Best regards

Detlev

[Former Member]

Hi Detlev,

thank you for coming back on this topic.

As stated at that time, this implementation throws more question marks than everything else (even the doc realizes the contradiction between "single space = empty string" and "no blank-padded strings allowed"). As mentioned 2004, I think this could have been implemented otherwise and better. Anyhow, as this has been the way for over ten years now, the basic logic won't be changed.

Let me say it in a less diplomatic way: they have simply done a quite sloppy job on this. But from my point of view, this is not the actual problem, if you have done somenthing wrong you are anytime free to correct your mistakes.  Its rather, and Im noticing similar circumstances on other SAP products also, SAP has a concern wide problem to admit a fail. I will never understand this: for my self Im more disappointed about faltering tryes to sell it as "its a feature" compared to statements like "we dont have the resources or motivation to fix this", like pretty demonstrated in this thread. Dont get me wrong, I like SAP and worked for a time in Walldorf HQ, its a great company and Im keepin my colleagues and company in good memories. But we obviuosly still need to learn to call a fail as a fail.

However, Im familiar with the docs you pointed me to, nevertheless thank you for that. At the end I will need to program helper classes and adapt my table structures to avoid mentioned problems.

cheers

[detlev_beutner]

Hi Lawrence,

> if you have done somenthing wrong you are anytime free to correct your mistakes

Yes and no. In this specific area one has to address the fact that people now have developed against this openSQL implementation, and suddenly changing it might effect these implementations. Therefore my idea with a property which might default to the old behaviour. As the implementation is just "good" or "less good", but not "wrong" by some outer definition, this would have to be accepted.

I had a different issue also 11 years ago where SAP violated the J2EE servlet standard in a specific method. The first answer was: "Correct, it's wrong, but people depend on this and have developed against this kind of implementation, so we won't change it." There I got a bit furious (I'm such a kind person, but sometimes... ;-)). Of course, they changed it in the end, and they had to. But with openSQL, it's different.

And in general I have to say that, at least if you skip first and sometimes second level support, at SAP you will find people who are glad when people point out mistakes. OK, sometimes (like the servlet example), one has to kick someone. But I have some comparisons with other vendors (bad examples: JiveSoftware, Adobe, Novell), where the situation is much worse in general. So, don't damn SAP for that, I really think it's one of the stronger companies (also a better example is IBM) concerning the willingness to correct mistakes.

From my painful experiences with openSQL, I would always advise to use native SQL in projects where the DB vendor is fixed. For products, openSQL is the better way, but much more painful to implement (I remember date/time issues, where I also could not stop shaking my head).

Best regards

Detlev

[thorsten_zielke]

Hi,

first let me say that this is an SAP Open SQL issue and not related to the SAP MaxDB database.

Historically, ABAP does not know NULL values. From MaxDB, I cannot help you further, but let me point out that...
using MaxDB in compatibility mode 'Oracle' stores 'empty strings' as NULL values (which is exactly what Oracle does...).
If you connect with SQL Studio or Database Studio against MaxDB you are by default in connect mode 'internal', which stores 'Empty strings' as 'Empty Strings'

Thorsten

[detlev_beutner]

Hi Thorsten,

> this is an SAP Open SQL issue and not related to the SAP MaxDB database

You are absolutely correct. I'm unsure if I really opened this thread 11 years ago under MaxDB or if this was a side effect of the migration a few years ago. Anyhow, it was discussed always correctly as an OpenSQL matter.

With this having said, the MaxDB hints you gave are interesting, but not relevant as long as one uses OpenSQL. But as said in my last answer in the last paragraph - from my point of view this should be avoided if possible (in general i.e.: if not developing a product which has to be able to run on all SAP supported DBs). Just yesterday I met someone which quite the same experience.

As soon as one can leave the OpenSQL path and has the situation to work on MaxDB (which I really really like, just the opposite with OpenSQL, which I really really dislike), your hints become very relevant again, and I didn't know these details so far. So, in any case, thanks for this!

Best regards

Detlev