For this issue, we'll add the ability to edit image blob, read content from a file of any type into a table editor cell, and save the blob/clob content to a file.
Revision no. 57526
Author: harish.khattri
Revision no. 57526
Author: harish.khattri
Recent experiences have shown there is a certain size limit with INSERT/UPDATE statements. Since BLOB/CLOB can be large, we basically need to use Prepared Statements when dealing with BLOB/CLOB data.
Currently the Table Data Editor does NOT use Prepared Statements, even in Batch mode. Thus in order to support editing of BLOB/CLOB data in the Table Data Editor, we'll need to introduce Prepared Statements in the Table Data Editor.
In order to reduce the scope, we'll make the following changes for this issue:
Recent experiences have shown there is a certain size limit with INSERT/UPDATE statements. Since BLOB/CLOB can be large, we basically need to use Prepared Statements when dealing with BLOB/CLOB data.
Currently the Table Data Editor does NOT use Prepared Statements, even in Batch mode. Thus in order to support editing of BLOB/CLOB data in the Table Data Editor, we'll need to introduce Prepared Statements in the Table Data Editor.
In order to reduce the scope, we'll make the following changes for this issue:
Implementation Considerations:
Implementation Considerations:
Look at importThead->generatePreparedInsert and importthread->prepareStmt for code samples. Remember that you only have to do this prepared statement update for BLOB/CLOB.
Look at importThead->generatePreparedInsert and importthread->prepareStmt for code samples. Remember that you only have to do this prepared statement update for BLOB/CLOB.
Revision no. 57601
Author: harish.khattri
Revision no. 57601
Author: harish.khattri
@Nhi, We are able to write the cell content to a temp file with the file extension specified by "Default Image Format" and launch the program associated with the file type in the Operating System.
But current thread does not get block after opening image editor.
Could you help us to block current thread until Image editor close?
@Nhi, We are able to write the cell content to a temp file with the file extension specified by "Default Image Format" and launch the program associated with the file type in the Operating System.
But current thread does not get block after opening image editor.
Could you help us to block current thread until Image editor close?
Hi Nhi,
I implemented the BLOB/CLOB editing using prepared statements. These changes are integration of the getPreparedStatements() method. I tested in Oracle for blob data, I am able to insert new row in the table and updated blob and non-blob data together.
Right now we can edit blob data using 'Save Content to File' menu, save it to file, change the data and load data into table cell using 'Load Content from File" menu. There is no warning dialog yet to tell user about separate transactions.
Please have a look.
Revision no. 57609
Author: harish.khattri
Hi Nhi,
I implemented the BLOB/CLOB editing using prepared statements. These changes are integration of the getPreparedStatements() method. I tested in Oracle for blob data, I am able to insert new row in the table and updated blob and non-blob data together.
Right now we can edit blob data using 'Save Content to File' menu, save it to file, change the data and load data into table cell using 'Load Content from File" menu. There is no warning dialog yet to tell user about separate transactions.
Please have a look.
Revision no. 57609
Author: harish.khattri
Revision no. 57612
Author: harish.khattri
@Nhi, @Tom
We need your inputs on following points:
Thanks,
Harish
[Nhi]
Revision no. 57612
Author: harish.khattri
@Nhi, @Tom
We need your inputs on following points:
Thanks,
Harish
[Nhi]
Revision no. 57614
Author: harish.khattri
Revision no. 57614
Author: harish.khattri
Hi Harish,
Look at importThead->generatePreparedInsert and importthread->prepareStmt for code samples or perhaps we can reuse some of this code especially for different data types when creating inserts.
Thanks,
Tom
Hi Harish,
Look at importThead->generatePreparedInsert and importthread->prepareStmt for code samples or perhaps we can reuse some of this code especially for different data types when creating inserts.
Thanks,
Tom
Hi Tom,
Thanks for help. I tested below code on DB2LUW, Oracle and MS SQL Server for BLOB/CLOB and few data types.
Pending tasks:
1. Handling of identity columns
2. Edit Image action (open image editor in modal state)
Revision no. 57615
Author: harish.khattri
Hi Tom,
Thanks for help. I tested below code on DB2LUW, Oracle and MS SQL Server for BLOB/CLOB and few data types.
Pending tasks:
1. Handling of identity columns
2. Edit Image action (open image editor in modal state)
Revision no. 57615
Author: harish.khattri
Revision no. 57619
Author: harish.khattri
Working on Edit Image task
Revision no. 57619
Author: harish.khattri
Working on Edit Image task
Revision no. 57621
Author: harish.khattri
Revision no. 57621
Author: harish.khattri
Revision no. 57626
Author: harish.khattri
Revision no. 57627
Author: harish.khattri
Revision no. 57626
Author: harish.khattri
Revision no. 57627
Author: harish.khattri
Hi Harish,
I did some functional testing across all three databases and looks like it is working. In addition to the suggested changes from the meeting today, can we also change these icons. You can use the File->Open and the File->Save As icons here:
Thank you,
Tom
Hi Harish,
I did some functional testing across all three databases and looks like it is working. In addition to the suggested changes from the meeting today, can we also change these icons. You can use the File->Open and the File->Save As icons here:
Thank you,
Tom
Hi Tom,
Made changes as per suggestions and changed icons.
Revision no. 57630
Author: harish.khattri
Fixed issues related to date time related datatypes for all three databases and issue with multiple row updates for same column
Revision no. 57631
Author: harish.khattri
Hi Tom,
Made changes as per suggestions and changed icons.
Revision no. 57630
Author: harish.khattri
Fixed issues related to date time related datatypes for all three databases and issue with multiple row updates for same column
Revision no. 57631
Author: harish.khattri
Hi Lisa/Nhi/Tom,
I have attached patch for supporting Edit Image. Edit Image launches the native image editor and user can continue editing the image. When user saves the image, we detect the changes and reload the cell with the latest content. The file is temporarily stored in .datastudio/images folder and are deleted on ADS exit.
We could use this approach in future for support Edit Image option.
Thanks
Asif
Hi Lisa/Nhi/Tom,
I have attached patch for supporting Edit Image. Edit Image launches the native image editor and user can continue editing the image. When user saves the image, we detect the changes and reload the cell with the latest content. The file is temporarily stored in .datastudio/images folder and are deleted on ADS exit.
We could use this approach in future for support Edit Image option.
Thanks
Asif
Thank you Asif.
Lisa, Keep this in mind if we get a customer request to expand our current blob edit functionality.
Thank you Asif.
Lisa, Keep this in mind if we get a customer request to expand our current blob edit functionality.
Revision no. 57633
Author: harish.khattri
[Nhi] The View Cell In ... > Text Viewer problem is fixed when Convert binary to hex is OFF.
However I observe a problem with the following scenario:
Result: The butterfly image is not saved correctly in the newly inserted row. A similar blob value is saved, but viewing the blob value as image does not show the butterfly image.
Expected: Save Table Data Editor should save blob value correctly whether Convert binary to hex setting is ON or OFF.
[Harish] I found the root cause of this issue:
When the Convert binary to hex is ON, then the byte[] (read from file) is converted into the String representation of the Hex value using proper encoding. Hence it is working fine.
However, when the Convert binary to hex is OFF, then the byte[] (read from the file) is directly converted to String using String value = new String(byte[] readBytes) constructor. The byte[] read from the file is correct (I tried to write the byte[] directly into another file and it is working fine). The value is getting corrupted as soon as the String constructor statement gets executed.
I tried to add Charset as the second argument in the String constructor, but have no luck. There is no method to write byte[] value in the table cell.
Could you please suggest any other way to write byte[] (binary value) directly to table cell without converting it to String? So that, we can read the value from table cell as byte[] (or binary value) back to view in Image Viewer or Save Content to File.
Revision no. 57633
Author: harish.khattri
[Nhi] The View Cell In ... > Text Viewer problem is fixed when Convert binary to hex is OFF.
However I observe a problem with the following scenario:
Result: The butterfly image is not saved correctly in the newly inserted row. A similar blob value is saved, but viewing the blob value as image does not show the butterfly image.
Expected: Save Table Data Editor should save blob value correctly whether Convert binary to hex setting is ON or OFF.
[Harish] I found the root cause of this issue:
When the Convert binary to hex is ON, then the byte[] (read from file) is converted into the String representation of the Hex value using proper encoding. Hence it is working fine.
However, when the Convert binary to hex is OFF, then the byte[] (read from the file) is directly converted to String using String value = new String(byte[] readBytes) constructor. The byte[] read from the file is correct (I tried to write the byte[] directly into another file and it is working fine). The value is getting corrupted as soon as the String constructor statement gets executed.
I tried to add Charset as the second argument in the String constructor, but have no luck. There is no method to write byte[] value in the table cell.
Could you please suggest any other way to write byte[] (binary value) directly to table cell without converting it to String? So that, we can read the value from table cell as byte[] (or binary value) back to view in Image Viewer or Save Content to File.
Hi Tom,
The BFILE datatype is the Oracle specific object, the value in the BFILE column must be the FILE object. The current implementation of the BFILE column has String as the column class, hence the value of this column comes as the String (the Hex string when convert binary to hex is ON), instead of object FILE.
As per the Oracle documentation, BFILE could be created only from Oracle. Previously (in the Dev-1 baseline build), the BFILE column was not editable. Now, as we make editable BINARY type columns it is giving the error mentioned by Juhi in above comment.
I think this column should remain non-editable in the Table Data Editor as we can not create BFILE object using Prepared statement.
Please suggest how we have to handle it?
[TC] Please fix it so it is not editable and can't have data up and downloaded to the column. Thanks.
Hi Tom,
The BFILE datatype is the Oracle specific object, the value in the BFILE column must be the FILE object. The current implementation of the BFILE column has String as the column class, hence the value of this column comes as the String (the Hex string when convert binary to hex is ON), instead of object FILE.
As per the Oracle documentation, BFILE could be created only from Oracle. Previously (in the Dev-1 baseline build), the BFILE column was not editable. Now, as we make editable BINARY type columns it is giving the error mentioned by Juhi in above comment.
I think this column should remain non-editable in the Table Data Editor as we can not create BFILE object using Prepared statement.
Please suggest how we have to handle it?
[TC] Please fix it so it is not editable and can't have data up and downloaded to the column. Thanks.
Hi Juhi,
We should not be able to load BLOB or CLOB data into a BFILE column. My understanding is that BFILE is a external file locator.
So I think this is a bug. Please have Harish investigate it.
Thank you,
Tom
Hi Juhi,
We should not be able to load BLOB or CLOB data into a BFILE column. My understanding is that BFILE is a external file locator.
So I think this is a bug. Please have Harish investigate it.
Thank you,
Tom
Revision no. 57638
Author: harish.khattri
Revision no. 57639
Author: harish.khattri
Revision no. 57638
Author: harish.khattri
Revision no. 57639
Author: harish.khattri
I observe a problem with the following scenarios :
I observe a problem with the following scenarios :
Revision no. 57642
Author: harish.khattri
Revision no. 57643
Author: harish.khattri
Revision no. 57642
Author: harish.khattri
Revision no. 57643
Author: harish.khattri
Hi all,
1.) I am done with testing all Scenarios for all the datatypes of all 3 Db's
a.) Oracle 172.24.1.44 v18.0
b.) DB2 LUW 172.24.1.44 v11.5
c.)SQL Server 10.31.200.24 2019
2.) Test cases are mentioned in this link "https://idera.testrail.net/index.php?/cases/view/248423"
3.) Please see the attached "Table_Editor_Observations.xlsx" for results of my Test cases.
Thanks
Juhi
Hi all,
1.) I am done with testing all Scenarios for all the datatypes of all 3 Db's
a.) Oracle 172.24.1.44 v18.0
b.) DB2 LUW 172.24.1.44 v11.5
c.)SQL Server 10.31.200.24 2019
2.) Test cases are mentioned in this link "https://idera.testrail.net/index.php?/cases/view/248423"
3.) Please see the attached "Table_Editor_Observations.xlsx" for results of my Test cases.
Thanks
Juhi
Revision no. 57645
Author: harish.khattri
Revision no. 57645
Author: harish.khattri
Hi Harish,
There is a behavior change in the QA results set->view cell in text viewer when I compare it to version 20.5. With the binary to hex switch on, I now see binary output displayed in the text viewer. Why? I don't think we should have this change since it might affect a current user. The same is true for TDE
Thanks,
Tom
Hi Harish,
There is a behavior change in the QA results set->view cell in text viewer when I compare it to version 20.5. With the binary to hex switch on, I now see binary output displayed in the text viewer. Why? I don't think we should have this change since it might affect a current user. The same is true for TDE
Thanks,
Tom
Hi Tom,
The changes for TDE was suggested by Nhi in the call, we discussed and agreed to implement the same logic in the View Cell In Text Viewer as it is implemented in the Save Content to File feature. It means that when Convert Binary to Hex is ON then we have to convert while data in the cell will be viewing in the Text Viewer.
These changes reflected in the Query Analyzer as the code of View Cell In Text Viewer is common for TDE and QA. Also, if we implement this behaviour only in TDE then the same feature behaving differently for TDE and QA. Hence, to make it consistent I made changes at both the places.
There are three options for implementing this behaviour:
Option 1: We have to convert binary to hex for BLOB datatypes in both TDE and QA when the option is ON.
Pros: The View Cell In Text Viewer behaviour will be consistent at both places and user will be seeing the original data in the Text Viewer.
Cons: Existing users will be affected and noticed the change in the behaviour.
Options 2: We have to convert binary to hex for BLOB datatypes only in TDE when the option is ON.
Pros: User will be seeing the original data in the Text Viewer in TDE. Existing user will not be affected and noticed any changes in QA.
Cons: The View Cell In Text Viewer behaviour will not be consistent in TDE and QA. Also, user will get confused about the behaviour.
Options 3: We will not have to convert binary to hex for BLOB datatypes for both TDE and QA when the option is ON. It means the value shown in the table cell will be shown in the Text Viewer as it is.
Pros: Existing user will not be affected and noticed any changes in the behaviour. Also, the behaviour will be consistent in TDE and QA.
Cons: User will not able to see original data, user will see the hex values which may not be meaningful.
Please suggest which option we need to implement. Option 1 is already in the repository (yesterday's changes) and I created patches for Option 2 and Option 3.
I recommend either Option 1 or Option 3 as the behaviour of the feature will be consistent at both places.
[Nhi] Let's go with option 3. Basically the following is the expected behavior in both the Grid Results and Table Data Editor. Save Content To File and Load Content From File only applies to Table Data Editor:
Basically this is the same behavior as ADS V20.5, except we add Save Content To File and Load Content From File in ADS V20.6.
Hi Tom,
The changes for TDE was suggested by Nhi in the call, we discussed and agreed to implement the same logic in the View Cell In Text Viewer as it is implemented in the Save Content to File feature. It means that when Convert Binary to Hex is ON then we have to convert while data in the cell will be viewing in the Text Viewer.
These changes reflected in the Query Analyzer as the code of View Cell In Text Viewer is common for TDE and QA. Also, if we implement this behaviour only in TDE then the same feature behaving differently for TDE and QA. Hence, to make it consistent I made changes at both the places.
There are three options for implementing this behaviour:
Option 1: We have to convert binary to hex for BLOB datatypes in both TDE and QA when the option is ON.
Pros: The View Cell In Text Viewer behaviour will be consistent at both places and user will be seeing the original data in the Text Viewer.
Cons: Existing users will be affected and noticed the change in the behaviour.
Options 2: We have to convert binary to hex for BLOB datatypes only in TDE when the option is ON.
Pros: User will be seeing the original data in the Text Viewer in TDE. Existing user will not be affected and noticed any changes in QA.
Cons: The View Cell In Text Viewer behaviour will not be consistent in TDE and QA. Also, user will get confused about the behaviour.
Options 3: We will not have to convert binary to hex for BLOB datatypes for both TDE and QA when the option is ON. It means the value shown in the table cell will be shown in the Text Viewer as it is.
Pros: Existing user will not be affected and noticed any changes in the behaviour. Also, the behaviour will be consistent in TDE and QA.
Cons: User will not able to see original data, user will see the hex values which may not be meaningful.
Please suggest which option we need to implement. Option 1 is already in the repository (yesterday's changes) and I created patches for Option 2 and Option 3.
I recommend either Option 1 or Option 3 as the behaviour of the feature will be consistent at both places.
[Nhi] Let's go with option 3. Basically the following is the expected behavior in both the Grid Results and Table Data Editor. Save Content To File and Load Content From File only applies to Table Data Editor:
Basically this is the same behavior as ADS V20.5, except we add Save Content To File and Load Content From File in ADS V20.6.
Thanks Tom, Thanks Nhi. The changes are pushed in the below mentioned revision.
Revision no. 57647
Author: harish.khattri
Thanks Tom, Thanks Nhi. The changes are pushed in the below mentioned revision.
Revision no. 57647
Author: harish.khattri
Hi all ,
Hi all ,
Issue #10563 |
Verified |
Fixed |
Resolved |
Completion |
No due date |
Fixed Build ADS 20.6.0-dev-48-no-ofsc |
No time estimate |
2 issue links |
relates to #15786
Issue #15786SQL server 2019 - Unable to load content into clob cell using load content from file option |
relates to #15687
Issue #15687Improve Export and Import for BLOBs cross databases |
For this issue, we'll add the ability to edit image blob, read content from a file of any type into a table editor cell, and save the blob/clob content to a file.