1. Anthony Mancuso
  2. PowerBuilder
  3. Thursday, 30 August 2018 16:15 PM UTC

We have a Oracle 12c Database.  In a table in the database is a column with a RAW data type.  The column is designed to not allow nulls per requirements of the DBA.

We are trying to retrieve/insert/update this column.   A regular datawindow does not allow me to select that column when i build it with the painter.  

We are on powerbuilder 2017 r2

 

What is the best approach to updating this column utilizing a datawindow?

 

 

Shenn Sellers Accepted Answer Pending Moderation
  1. Thursday, 30 August 2018 19:38 PM UTC
  2. PowerBuilder
  3. # 1

The Raw datatype is treated just like a Blob.  So you will have to use SelectBlob and UpdateBlob.  The only way to have the Datawindow modify it is by using the OLE Database Blob control.  Using this approach you will need to activate the column by either double-clicking it or triggering it with code by using the OLEActivate method.

 
 
 
I have included code I use to open a PDF that is saved in a blob.  This will work the same with the Raw datatype.
 
string ls_access
string ls_READONLY = "R"
string ls_READWRITE = "RW"
string ls_template
datetime ldt_ackgt_date
long ll_emp_id
long ll_doc_id
long ll_temp
long ll_rows
long ll_row
int li_filenum
boolean lbl_null
boolean lbl_iblob
DWItemStatus dwi_status

SetPointer(HourGlass!)

//Makes sure we have a row
if (row > 0) then

//Sets highlighting style
This.SetRow(row)

//If the PDF button was clicked
if (dwo.name = "b_pdf" AND this.object.b_pdf.Enabled = '1') then

//Determines the User's access setting
ls_access = f_vo_command_access()

//Gets data values
ll_emp_id = this.GetItemNumber(row, "emp_id")
ll_doc_id = this.GetItemNumber(row, "doc_id")
ldt_ackgt_date = this.GetItemDateTime(row, "ackgt_date")

//Checks for NULL
if (isNull(ll_doc_id)) then
MessageBox("ERROR", "You must specify a Document Name before adding a PDF.", StopSign!)
this.SetItem(row, "ackgt_on_pdf", "N")
this.SetColumn("doc_id")
return
end if

//Checks for NULL
if (isNull(ldt_ackgt_date)) then
MessageBox("ERROR", "You must specify an Acknowledgement Date before adding a PDF.", StopSign!)
this.SetItem(row, "ackgt_on_pdf", "N")
this.SetColumn("ackgt_date")
return
end if

//Checks for a NULL blob
select count(*)
into :ll_temp
from tadtp_doc_ackgts
where emp_id = :ll_emp_id
and doc_id = :ll_doc_id
and ackgt_date = :ldt_ackgt_date
and pdf_file is null;

//Determines NULL and IBLOB value
if (ll_temp = 0) then

dwi_status = this.GetItemStatus(row, 0, Primary!)

if (dwi_status = New! OR dwi_status = NewModified!) then
lbl_null = true
lbl_iblob = true
else
lbl_null = false
lbl_iblob = false
end if

else
lbl_null = true
lbl_iblob = false
end if

//Opens the document in READ ONLY mode if the user only has READ access
if (ls_access = ls_READONLY) then

//Is the blob NULL
if (lbl_null) then

//Message to user
MessageBox("FYI", "A PDF has not been associated with this record yet.")

else

//Updates Blob DW if it has been modified
if (dw_rblob.RowCount() > 0) then
if (dw_rblob.GetItemStatus(1, 0, Primary!) <> NotModified!) then dw_rblob.Update()
end if

//Retreives data in the Blob DW
ll_rows = dw_rblob.Retrieve(ll_emp_id, ll_doc_id, ldt_ackgt_date)

//Opens the document and allow editing
dw_rblob.OLEActivate(ll_rows, "blob_1", 0)

end if

else

//Is the blob NULL
if (lbl_null AND NOT ibl_pdf_set[row]) then

//Opens window
open(w_acquire)

//User did not specify anything
if (Message.DoubleParm = -1) then return

//Gets the returned string
ls_template = Message.StringParm

//Determines what Blob DW to populate
if (lbl_iblob) then
ll_rows = dw_iblob.InsertRow(0)
dw_iblob.SetItem(ll_rows, "emp_id", ll_emp_id)
dw_iblob.SetItem(ll_rows, "doc_id", ll_doc_id)
dw_iblob.SetItem(ll_rows, "ackgt_date", ldt_ackgt_date)
dw_iblob.SetItemStatus(ll_rows, 0, Primary!, DataModified!)
else
ll_rows = dw_rblob.Retrieve(ll_emp_id, ll_doc_id, ldt_ackgt_date)
end if

//Sets the string as the PDF template
if (lbl_iblob) then
dw_iblob.object.blob_1.Template = ls_template
else
dw_rblob.object.blob_1.Template = ls_template
end if

//Changes flag
ibl_pdf_set[row] = true

//Opens the document and allow editing
if (lbl_iblob) then
dw_iblob.OLEActivate(ll_rows, "blob_1", 0)
else
dw_rblob.OLEActivate(ll_rows, "blob_1", 0)
end if

//Makes sure the DW knows there's modified data
this.SetItem(row, "emp_id", ll_emp_id)

else

//Determines what Blob DW to activate
if (lbl_iblob) then

//Initialize variable
ll_row = 1

//Goes through all the rows
do while(ll_row <= dw_iblob.RowCount())

//Finds the correct row in the Blob DW
ll_row = dw_iblob.Find("emp_id = " + string(ll_emp_id) + " and doc_id = " + string(ll_doc_id), ll_row, dw_iblob.RowCount())

//Makes sure its the right date
if (dw_iblob.GetItemDateTime(ll_row, "ackgt_date") = ldt_ackgt_date) then exit

//Increments
ll_row++

loop

//As long as we have a row
if (ll_row > 0) then

//Opens the document and allow editing
dw_iblob.OLEActivate(ll_row, "blob_1", 0)

//Makes sure the DW knows there's modified data
this.SetItem(row, "emp_id", ll_emp_id)

end if

else

//Updates Blob DW if it has been modified
if (dw_rblob.RowCount() > 0) then
if (dw_rblob.GetItemStatus(1, 0, Primary!) <> NotModified!) then dw_rblob.Update()
end if

//Retrieves data for Blob DW
ll_rows = dw_rblob.Retrieve(ll_emp_id, ll_doc_id, ldt_ackgt_date)

//Opens the document and allow editing
dw_rblob.OLEActivate(ll_rows, "blob_1", 0)

//Makes sure the DW knows there's modified data
this.SetItem(row, "emp_id", ll_emp_id)

end if

end if

end if

end if

end if
Comment
There are no comments made yet.
Anthony Mancuso Accepted Answer Pending Moderation
  1. Friday, 31 August 2018 10:49 AM UTC
  2. PowerBuilder
  3. # 2

Thank you for responding Shenn. We are using the Raw column to store a password (encrypted). Our DBA won't allow nulls on the column. So I was wondering how I would use the datawindow to insert a row into the table. The DW won't allow selection of the raw column and i believe an insert without this field would cause. an error because it cannot be null. My thought was that it should allow nulls then my insert would work and an Update Blob, as you said, right after the insert would be the way to go.
Thanks again for your response. I really appreciate it.
tony

Comment
There are no comments made yet.
Shenn Sellers Accepted Answer Pending Moderation
  1. Friday, 31 August 2018 17:22 PM UTC
  2. PowerBuilder
  3. # 3

You won't be able to use the DW and the OLE Database Blob approach to save a password.  Using that approach will mean you have to apply it using a specific program.  For example, it will open Word, Acrobat, Excel, MS Paint, etc. 

You need to convince your DBA to either allow NULLs or change the column type to a String if the DBA is insistent on the column not being NULL.  Otherwise, you are correct in assuming that it will cause an error.  I can't imagine that your encrypted password is more than 107,3741,823 characters (the max a String value can hold).

Attachments (1)
Comment
There are no comments made yet.
Jeff Kandt Accepted Answer Pending Moderation
  1. Friday, 31 August 2018 17:44 PM UTC
  2. PowerBuilder
  3. # 4

Can you create a stored procedure which accepts the password as a string, converts it to RAW and inserts the row, and then call the sproc from your app?

Comment
  1. Anthony Mancuso
  2. Tuesday, 4 September 2018 10:45 AM UTC
Thanks Jeff. Probably going to use SQL in powerscript and abandon the Datawindow idea.
  1. Helpful
There are no comments made yet.
Anthony Mancuso Accepted Answer Pending Moderation
  1. Tuesday, 4 September 2018 10:43 AM UTC
  2. PowerBuilder
  3. # 5

Thanks for responding Jeff,

I think I will have to abandon the use of a data window and write inline SQL in powerscript to accomplish this. The stored proc is a good idea though.

 

Tony

Comment
There are no comments made yet.
Olan Knight Accepted Answer Pending Moderation
  1. Tuesday, 4 September 2018 21:56 PM UTC
  2. PowerBuilder
  3. # 6

Did the DBA explain WHY NULLs are not allowed?

It seems to me that the added concienience of being able to handle the password in a DW exceeds any possible issues with a NULL value in the password column. The app can easily check for the NULL and flag is as illegal.

It's not a bug deal either way, but I do dislike arbitrary restristrictions.

YMMV.

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.