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.

1. Add "Default Image Format" to the end of the list in "File > Options > General", which is a single select list (similar to Default Spreadsheet Format) with the following options:
   • GIF, JPEG, PNG, and TIFF. (default = PNG)
2. Add the following options in the table cell right-click menu: Edit Image, Load Content From File, and Save Content To File. See Edit-Blob.png
3. The Edit Image option will 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. The ADS UI will be blocked until the image editor program exits. The content of the temp file will then be written back to the table cell.
   • Note that ADS code base has the ExcelView class that is used by the "View Cell In... > Spreadsheet Viewer" option which launches the spreadsheet program in background mode.
   • We should probably create a class similar to ExcelView to launch the image editor program in "modal" mode.
4. The Load Content From File option will bring up a modal dialog box for the user to chose a file of any type. The content of the selected file will be read into the table editor cell. Note that if the "File > Options > Results > Convert binary to hex" option is turned ON, the table cell value will need to appear as a hex string.
5. The Save Content To File option will bring up a modal dialog box for the user to enter a filename. The binary content of the table editor cell will be saved to the file. Note that if the "File > Options > Results > Convert binary to hex" option is turned ON, the table cell value will appear as a hex string. Regardless of this setting, the saved file must contain the original, non-hex encoded content.
   • Note that there's an existing feature "Save Cell Value to File" in the Query Analyzer Grid Results. Thus the implementation may try to reuse this existing code. However the current will save the content as is, meaning that hex encoded strings are saved as text file. One needed change is to allow a parameter to indicate the hex strings should be converted to binary in the saved file.
6. Allow the "Edit in Window" option to work for BLOB/CLOB values. Currently BLOB/CLOB values in the table editor are not editable; the "Edit in Window" action results in a no-op. We need to change this behavior so that "Edit in Window" is allowed for BLOB/CLOB values.

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:

1. Support editing of BLOB/CLOB data only for Oracle, SQL Server, and DB2 databases.
2. Save BLOB/CLOB values to the database in a separate "pass". In this way, we only need to implement the Prepared Statements for UPDATE that only involve BLOB/CLOB values. Thus we'll use existing code in the first iteration to handle non-BLOB/CLOB values. In a second iteration, use UPDATE prepared statements to handle the BLOB/CLOB values.
3. Investigate to see if we can mix regular UPDATE/INSERT statements with Prepared Statements within the same transaction. If this is not feasible, then we will need to execute the Prepared Statements in a separate transaction. If this is the case, then whenever the user performs an action that modifies a BLOB/CLOB value in the Table Data Editor, we'll also need to show a warning dialog box stating this fact:
   • Title: Edit BLOB/CLOB
   • Message: Modified BLOB and CLOB values are saved to the database in a separate transaction.
   • Checkbox: Do not show this message again
   • Help Link: Provide a link to the online documentation that explains how to modify BLOB/CLOB data in the Table Data Editor.
   • Buttons: Close

Implementation Considerations:

1. Table Data Editor uses the DataTableModel::getUpdateScriptStatements() method to construct the SQL statements to save the changes to the database. The following member variables contain the changes:
   • _insertedRows -- contains new rows to be inserted
   • _deletedRows -- contains the rows to be deleted
   • _modifiedRows -- contains the rows to be modified. This is a HashMap where the "key" contains the original row data, and the "value" contains the modified row data.
2. Note that for UPDATE, the WHERE clause is constructed based on the original values. For example:
   • UPDATE table1 set col1 = new PK, col2 = some new value WHERE col1 = orig PK
3. We may need to add some checks in the above method to ignore BLOB/CLOB values if it's not already doing that.
4. I think it makes sense to add the new method getPreparedStatements() in the DataTableModel class. This method should return the prepared statements for updating only the BLOB/CLOB values.
   • For UPDATE, the WHERE clause should be constructed based on the modified values. For example:
     • UPDATE table1 set col3 = new blob value WHERE col1 = new PK
   • We also need to build UPDATE prepared statements for the _insertedRows that contain BLOB/CLOB values because the getUpdateScriptStatements() method ignores BLOB/CLOB values. So in the 2nd pass we need to do UPDATE prepared statements to update the BLOB/CLOB values for the entries in _insertedRows.
5. Note that for UPDATE, ADS currently will abort with message "A primary key or unique constraint is not defined..." when the user Save the changes without specifying the PK column(s). This was not needed for INSERT. However we now need to do this if the user inserts a new row with BLOB/CLOB data. This is because we will do UPDATE for the BLOB/CLOB values, which requires a PK column in the WHERE clause.

Look at importThead->generatePreparedInsert and importthread->prepareStmt for code samples. Remember that you only have to do this prepared statement update for BLOB/CLOB.

1. Also, the Load Content is not allowed for CLOB. It should be allowed.
2. "Load Blob/Clob Data" WARNING dialog
   • Rename title to "Load Content From File"
   • Please conform to the standard warning dialog style. See WarningDialog.png for example.
   • Rename the message as follows:
     • If multiple cells selected ==> "You may load the content from a file into only one cell at a time. Please repeat the operation with only one cell selected."
     • If no cells or wrong cell selected ==> "Load Content From File only works on a cell with BLOB or CLOB content type. Please repeat the operation with the appropriate cell selected."
3. "Load Blob/Clob Data" dialog
   • Remove dialog title
4. "Save Content To File" WARNING dialog
   • Please conform to the standard warning dialog style. See WarningDialog.png for example.
   • Rename the message as follows:
     • If multiple cells selected ==> "You may save the content to a file from only one cell at a time. Please repeat the operation with only one cell selected."
     • If no cells or wrong cell selected ==> "Save Content To File only works on a cell with BLOB or CLOB content type. Please repeat the operation with the appropriate cell selected."

Revision no. 57601
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:

1. Warning message dialog: The warning message dialog have title, message, Do Not Show Again checkbox and Ok button. Do we need the Help link. As the warning message dialog is being shown when user clicks either "Load Content from File" or "Edit in Window" or "Edit Image" menu item, it means user knows from where he/she wants to edit or insert BLOB/CLOB data.
   I found the popup dialog with "Do not show again" checkbox in AlterUI, so I used it. If we have to add Help link then I have to create custom popup dialog for it.
2. Scenario: If the table have primary key with auto-increment, then insert row (which have BLOB/CLOB) data then how we have to insert row, as primary key column's value is not known at the time of generating prepared statement.
   In that case, user have to insert data for non-BLOB/non-CLOB columns data, then perform "save and refresh" action. After that he/she update data for BLOB/CLOB column.
3. Edit Image: The image editor is getting launched successfully, however ADS UI is not getting blocked until user exits from the image editor. Ajit tried process.waitFor() method but there is no luck. He committed his code, please have a look whether we are missing something.

Thanks,
Harish

[Nhi]

• The "Edit In Window" should be enabled only for CLOB, and not for BLOB, unless there an easy way to implement a Hex editor or if there is open source embedded hex editor available. Otherwise editing the binary values in a window is not very useful.
• The Edit Image action in ADS dev-38 logs an exception in View Log: java.lang.Exception: can't open /var/folders/93/b81l_cpj1gx1pq90rksp51d40000gn/T/ViewCellInImageEditor3726870743271755344.png. Running from inside IntelliJ doesn't seem to throw this exception.
• In AQFileUtils::editFile(), the resulting Process object from invoking Runtime.getRuntime().exec(new String[]{"open", path}) does not seem to be that of the image editor process. Instead, it seems to be the process of a temporary "launcher" mechanism used by the OS, which gets destroyed quickly after the actual image editor is launched. Thus the proc.waitFor() call is invalid because proc is a temporary process that most likely has exited. Please do some investigation to see if there's a workaround. I will also do some research on my side.
• For "2. Scenario" above, it seems like we have no choice but to implement a Prepared Statement for INSERT, which has to handle all supported data types in addition to the BLOB/CLOB. Given this, let's also make the UPDATE prepared statement also handle all data types so that we don't need to do this is a separate transaction. This means that for the 3 supported database, the Table Data Editor will only use prepared statements, which handles INSERT, UPDATE and DELETE.

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 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. 57621
Author: harish.khattri

Revision no. 57626
Author: harish.khattri

Revision no. 57627
Author: harish.khattri

1. The automation test cases are not properly reported on Ranorex.
2. The local test report shows that the "DB2 LUW 172.24.1.44 v11.5" test case failed. It failed waiting for "AdsTableforEditorInfo" to get resolved. However I don't see any code trying to launch the table editor, so there is no way the table editor repository object will get resolved.

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 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.

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:

• Set Convert binary to hex ==> OFF
• Open Table Data Editor
• Insert new row
• Load the butterfly image file into BLOB cell
• Fill out other columns as needed
• Save Table Data Editor

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 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

 I observe a problem with the following scenarios : 

Revision no. 57642
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

Automation test result link

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 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:

• Convert binary to hex => ON
  • View As Text / Edit in Window -- shows HEX string
  • View Cell In.. > Text Viewer -- shows HEX string
  • View Cell In.. > Image Viewer -- shows image correctly if content is image
  • Save Content To File -- saves the raw value. For example if raw value is image, the saved file is viewable in image viewer/editor
  • Load Content From File -- the cell shows HEX string after loading. Saving the Table Data Editor saves the raw data to the database.
• Convert binary to hex => OFF
  • View As Text / Edit in Window -- shows raw data
  • View Cell In.. > Text Viewer -- shows raw data
  • View Cell In.. > Image Viewer -- shows image correctly if content is image
  • Save Content To File -- saves the raw value. For example if raw value is image, the saved file is viewable in image viewer/editor
  • Load Content From File -- the cell shows raw data after loading. Saving the Table Data Editor saves the raw data to the database.

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

Hi all ,