Question about character data lost when writing external oracle database by Native SQL

[Former Member]

When I update constans data to external oracle database, data in specified table will update correctly, But when I update variables to the same table, character data will lost. Below is the code.

Code with constans:

EXEC SQL.

   INSERT INTO TRAVEL_01 VALUES( 'ABCDEFG'

                                                          '123ABC')

ENDEXEC.

data will insert into table sucessfully.

Code with varables:

data l_var1 type char20 value 'ABCDEFG'.

data l_var2 type char10 value '123ABC'.

EXEC SQL.

  INSERT INTO TRAVEL_01 VALUES(:l_var1,

                                                        :l_var2 )

ENDEXEC.

result in database is as below:

Col1(char20)	Col2(char10)
AB	123

I don't know how solve this problem,  please give me some suggestion.

Thanks

Ivan

[stefan_koehler · Accepted solution]

Hello Ivan,

i am sorry, but where is the coding for writing to an external database? This code looks like it writes into the SAP oracle database itself by using native SQL.

Could you also post the table definition on database level?

Have you already done a DBSL trace?

Best Regards

Stefan

[Former Member · Accepted solution]

Thanks for your attention to my question.

In my description above, I have omitted the code connecting external database.

Below is the code conneting to external database, and writing to it.

try.

  exec sql.

     connect to:  conn_name

  endexec.

exec sql.

     set connection: con_name

endexec.

catch cx_sy_native_sql_error into exc_ref.

  write 'failed'.

endtry.

try.

  exec sql.

     insert into travel_test values ( ......................)

  endexec.

catch cx_sy_native_sql_error into exc_ref.

   write: 'failed'.

endtry.

I have checked the connection between SAP and external oracle, it work well.

Below is the table structure in external database.

colunm name	data type	Is key or not
COL1	CHAR10	Yes
COL2	CHAR20	NOT

[stefan_koehler · Accepted solution]

Hello Ivan,

ok so you are using DBCO. I requested the table description "desc <TABNAME>" on the external database. What kind of semantic did you use (byte or character length)?

However have you considered the character settings as well (page 3 and 4)?

http://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/60627aef-1ff8-2e10-a497-a38b7fbca...

Please provide all the necessary information. Such a behavior can have several reasons.

Regards

Stefan

[Former Member · Accepted solution]

The external database is a single byte character set, but SAP is unicode characterset.

According to the document you referred. I can only insert byte character set into external database,and have to transfer character type data to xstring data in my ABAP code.

Is there anyway to write character data into external DB directly without tranfering data type ?

Thanks

Ivan

[stefan_koehler · Accepted solution]

Hello Ivan,

once more .. what is the exact table definition on your external database (run a desc <TABNAME> and post the exact output here).

Please perform a DBSL trace and attach it here (with both cases - literals and binds). If you can not provide that information you need to open a SR, because anything else is like looking into a crystal ball.

Thanks.

Regards

Stefan

[Former Member · Accepted solution]

http://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/60627aef-1ff8-2e10-a497-a38b7fbca...

According  to the Doc you refered, the problem has been solved.

Thanks

----------

ivan

[Former Member]

Hi Ivan,

If you are able to insert the record without any problem by static values in the insert statement in the ABAP code, as you noted at your first message, there shouldn't be a structure problem on the database. In this case, you can download and install the latest kernel and dbsl library and try again.

I tested the same case in my test environment, by the program below and didn't face any problem.

data l_var1 type char20 value 'ABCDEFG'.
data l_var2 type char10 value '123ABC'.
data con_name LIKE dbcon-con_name.

move 'TST' to con_name.

exec sql.
  connect to :con_name
endexec.

exec sql.
  set connection :con_name
endexec.

exec sql.
  INSERT INTO TRAVEL_01 VALUES(:l_var1, :l_var2 )
endexec.

Please note that in my test environment, both systems are running on Oracle 11g.

Best regards,

Orkun Gedik

[Former Member]

Hi,

I recommend you use ABAP Database Connectivity in order to connect to the external database. You can find the working sample, named "ADBC_DEMO" program on SE38. This problem might be handled in the ABAP class.

Best regards,

Orkun Gedik

[Former Member]

I have read the code in program "ADBC_DEMO" and run the program,

and copied it to the new program. and delete the subform delete and drop table from my program.

and run the program.

but the table in external database has no change. no lines was inserted in to table ADBC_DEMO_

So, I think the code in demo program can not solve my problem.

But  thanks for your suggestion.

Ivan

[Former Member]

Hi Ivan,

I wanted to say take this program as a reference to use ADBC ABAP class. You can find the further information about ADBC class in the document, below;

http://help.sap.com/abapdocu_702/en/abenadbc_ddl_dml.htm

Then modify the ABAP code, by using these ABAP classes. As I noted in my previous message that kind of type casting problems might be handled in the standard SAP classes.

Best regards,

Orkun Gedik

[Former Member]

Hi,Orkun.

As I said, I have made a copy of the demo program, and changed the copy one, delete the form "delete_rows" and "drop_table" from the copy one.

After excuting the copy program, that is no row insert into table in external database.

But if I change the data type of structure "abdc_demo_t" from char to xstring, and run the program again, there will be new rows inserted into external database.

So, using the class can't handle the casting problem

Best regards.

Ivan

[Former Member]

Hi,

>> If you are able to insert the record without any problem by static values in the insert statement in the ABAP code, as you noted at your first message, there shouldn't be a structure problem on the database. In this case, you can download and install the latest kernel and dbsl library and try again.

Did you try to update kernel and dbsl library?

Best regards,

Orkun Gedik

[Former Member]

Problem has been solved.

http://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/60627aef-1ff8-2e10-a497-a38b7fbca...

Best regards

-------------------

Ivan