#13646: Insert statement is not generated with right values for Export Data.

Diksha.kushwah(*) reported 2015-08-08T06:27:32Z · last modified 2017-01-03T14:44:40Z

Insert statement is not generated with right values for Export Data.

Dev	tariqrahiman
QA	tariqrahiman

Priority	Major
Complexity	Complex

Component	DB - Sap Hana
Version	18.0

Version: 17.0.0-alpha-7

Build #: 45104

Build Date: 2015-Aug-06 03:42:39 PM

Operating Environment: Windows 7 (6.1, amd64) / Cp1252 / en / US / Oracle Corporation 1.8.0_40-b26

Memory: Max=704,643,072; Total=381,157,376; Free=140,738,256; CPUs=8

Steps to reproduce:

Connect to SAPHANA

Create a table and insert some values in it using following script:

CREATE COLUMN TABLE "Diksha"."Large_objects" (

"c1" BLOB MEMORY THRESHOLD 1000,

"c2" CLOB MEMORY THRESHOLD 1000,

"c3" NCLOB MEMORY THRESHOLD 1000,

"c4" TEXT,

"c5" VARBINARY(5000)

)

UNLOAD PRIORITY 5

INSERT INTO "Large_objects"("c1", "c2", "c3", "c4", "c5")

VALUES('FF3311121212EE3a','abc','afsgfs','agsfghfd',CAST('wahid' AS VARBINARY(5000)))

Scenario 1:

Go to file option-> Results-> Convert binary to hex -> uncheck

Right click on created table and select Tools -> Export Data

Do not change default settings and click on next

In options tab set following things:

Destination: Preview Export

Format: INSERT Statements

Object Quoted Identifier: Double Quotes:"objects"

Click on Next

Following insert statement is generated:

INSERT INTO "Diksha"."Large_objects"("c1", "c2", "c3", "c4", "c5")

VALUES([B@3828f99c, 'abc', 'afsgfs', 'agsfghfd', 0x5b42403565363337376164)

Scenario 2:

Go to file option-> Results-> Convert binary to hex -> check

follow the same process as scenario1

Following insert statement is generated:

INSERT INTO "Diksha"."Large_objects"("c1", "c2", "c3", "c4", "c5")

VALUES(46463333313131323132313245453361, 'abc', 'afsgfs', 'agsfghfd', 0x7761686964)

In both Scenarios right insert statement is not generated for blob and varbinary datatype.

3 attachments

Import_13646.png

2016-03-23T10:25:56Z

63 KB
Varbinary_13646.png

2016-03-23T10:26:16Z

23 KB
rc-14.png

2017-01-03T14:44:22Z

28 KB

All Comments (10) Change History

ivangron 2015-08-12T20:46:06Z

I have made modifications to the scripting of insert data for hana. In the process of making and testing these changes I have discovered that hana also supports [binary] datatype with a max length of 2000. This datatype is not documented. I added the binary type to the datatypes list and column init information.

Diksha.kushwah(*) 2015-08-13T07:45:32Z

Go to file option-> Results-> Convert binary to hex -> check

Create table and insert data from given script

Export the Data in Insert Statement Format

Insert Query generated as follows:

INSERT INTO "Diksha"."Large_objects"("c1", "c2", "c3", "c4", "c5")

VALUES('46463333313131323132313245453361', 'abc', 'afsgfs', 'agsfghfd', '7761686964')

Execute this query and Execute select Query

3436343633333333333133313331333233313332333133323435343533333631 this value is display for column c1.

Diksha.kushwah(*) 2015-08-13T08:28:37Z

Create a table using binary data type and insert the data:

CREATE ROW TABLE "Diksha"."b1" (

"c1" BINARY

)

UNLOAD PRIORITY 5

INSERT INTO "Diksha"."b1"("c1")

VALUES('10101')

Execute following select command:

SELECT * FROM "Diksha"."b1"

o/p

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

010101000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

Create a table using binary data type and insert the data:

CREATE ROW TABLE "Diksha"."b1" (

"c1" BINARY

)

UNLOAD PRIORITY 5

INSERT INTO "Diksha"."b1"("c1")

VALUES('10101')

Execute following select command:

SELECT * FROM "Diksha"."b1"

o/p

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

ivangron 2015-08-13T20:22:32Z

working as designed. User is allowed to do this, though I am not sure why you would want to do this. In this case you are asking the extraction to convert the data to hex and then reinserting it into a binary field. data studio is doing what is been asked correctly, but this will not store the same data as you have converted it.

Diksha.kushwah(*) 2015-08-14T05:29:10Z

For second scenario of Binary Data type:

when we uncheck Convert binary to hex option in file options and Execute select query then value does not display in result set.

Is it also expected?

Observation: I have checked in other databases like mysql and sybase ase, they shows the converted values.

Diksha.kushwah(*) 2015-08-27T10:19:41Z

Because of this Import Export also not working for Binary Data type.

Diksha.kushwah(*) 2015-08-27T12:04:00Z

In alpha-31 import is not working for VARBINARY data type.

narendravatsaraj(*) 2016-03-23T10:26:36Z

Step 1: Create table

CREATE ROW TABLE "SYSTEM"."VarbinaryTable" (

"c1" VARBINARY(5000)

)

UNLOAD PRIORITY 5

Step 2: Insert data into table

INSERT INTO "SYSTEM"."VarbinaryTable"("c1")

VALUES(CAST('wahid' AS VARBINARY(5000)))

Step 3: Execute SELECT * query to verify inserted data.

Grid View shows value as

7761686964

Step 4: Check option "Convert Binary to Hex" in File -> Options->Results

Step 5: Observe Insert Statements in Preview Export option of Tools –> Export Data.

Step 6: Uncheck option "Convert Binary to Hex" in File -> Options->Results

Step 7: Observe Insert Statements in Preview Export option of Tools –> Export Data.

Result : For Varbinary datatype correct Insert SQL script is displayed depending upon

Check/Uncheck option "Convert Binary to Hex" in File -> Options->Results.

Refer : Varbinary_13646.png

Scenario 2: Import of Varbinary and binary

For Import in Varbinary / Binary datatypes we must check option

"Convert Binary to Hex" in File -> Options->Results

Try to import CSV or Excel file

Result : Data imported successfully.

Refer : Import_13646.png

narendravatsaraj(*) 2016-03-23T10:27:45Z

Verified in ADS 17.0.3-15

Diksha.kushwah(*) 2017-01-03T14:44:40Z

Verified in ADS 18-rc-15

Search Tips

Aqua Data Studio / nhilam

Insert statement is not generated with right values for Export Data.

3 attachments

Issue #13646

Completion

1 issue link

Issue #14343

Search Tips

Aqua Data Studio / nhilam

Title

Insert statement is not generated with right values for Export Data.

3 attachments

Issue #13646

Completion

1 issue link

Issue #14343