Re: Image from DB into datawindow

[Richard Keller]

This is the SQL Generated...

declare @P1 int
set @P1=0
declare @P2 int
set @P2=229378
declare @P3 int
set @P3=294916
declare @P4 int
set @P4=0
exec sp_cursoropen @P1 output, N'select * FROM BOS_Blob_Storage WHERE
bos_keyvalue = ''GOLDWE'' and bos_type = ''Logo''', @P2 output, @P3 output,
@P4 output
select @P1, @P2, @P3, @P4

"Richard Keller" <ric...@kellersystems.com> wrote in message
news:428025b1@forums-2-dub...
>I am trying to retrieve images into a datawindow from the database for
>dynamic picture assignment. I tried to use the New InkPicture and OLE
>Database Blob and cannot seem to get the image to come through. Any ideas?
>

[Richard Keller]

[Shenn Sellers]

I've done this in a couple of programs using a different method. Now
whether this method is the correct or best way, I have no idea. It has been
working for me just fine though. I have the image files saved as a blob
inside the DB along with the type of file they are (i.e. jpg, bmp, etc).
The DW I am placing the picture in has an empty Picture Control. After I do
a standard Retrieve( ) to populate the DW with data, my other code fires off
to handle the image file. I retrieve the blob data, write the data to a
file on the hard drive, and then associate the picture file with the Picture
Control in the DW. After you are done with the DW, I remove all the image
files off the hard drive. Code sample is below.

/*****************************************************************************/

//Retrieves picture from database
selectblob emp_pic
into :b_pic
from erpp_employee_info
where last_name = :is_lname
and first_name = :is_fname
and sect_assign = :is_section;

//As long as there was a picture
if (SQLCA.SQLCode = 0 AND NOT isNull(b_pic)) then

//Get the file length
ll_file_len = Len(b_pic)

//Obtains the file type of the picture
select file_type
into :ls_filetype
from erpp_employee_info
where last_name = :is_lname
and first_name = :is_fname
and sect_assign = :is_section;

//Makes sure we have a file type
if (SQLCA.SQLCode <> 0 OR isNull(ls_filetype) OR ls_filetype = "") then

MessageBox("ERROR", "Unable to load picture file.", StopSign!)

//Determines what picture file to use
if (FileExists("C:\_APPS\ERPP\nopic.bmp")) then
this.dw_1.object.p_emp.filename = "C:\_APPS\ERPP\nopic.bmp"
else
this.dw_1.object.p_emp.filename = "nopic.bmp"
end if

return lb_rc

end if

//Remove old picture file
if (FileExists("C:\_APPS\ERPP\emp_pic." + ls_filetype)) then
FileDelete("C:\_APPS\ERPP\emp_pic." + ls_filetype)
end if

//Opens picture file
li_fileNum = FileOpen("C:\_APPS\ERPP\emp_pic." + ls_filetype, StreamMode!,
&
Write!, LockReadWrite!)

//Determine how many times to call FileWrite
IF (ll_file_len > 32765) THEN

//Determines how many loops we need
IF (Mod(ll_file_len, 32765) = 0) THEN
li_loops = ll_file_len / 32765
ELSE
li_loops = (ll_file_len / 32765) + 1
END IF

ELSE
li_loops = 1
END IF

//Initializes
ll_tot_bytes = 1

//Writes blob to file
FOR i = 1 to li_loops
b_temp = BlobMid(b_pic, ll_tot_bytes, 32765)
ll_bytes = FileWrite(li_fileNum, b_temp)
ll_tot_bytes += ll_bytes
NEXT

//Closes file
FileClose(li_fileNum)

//Sets picture file to DW
this.dw_1.object.p_emp.filename = "C:\_APPS\ERPP\emp_pic." + ls_filetype

/***********************************************************************/

Hope this helps.

Shenn Sellers
Riverside County Waste Management

"Richard Keller" <ric...@kellersystems.com> wrote in message
news:428025b1@forums-2-dub...

[Richard Keller]

I was trying to avoid the unneccessary steps and thought the 10.2 Ink
Control would solve it instead. We never got the OLE Database Blob to ever
work consistently. I used to go to the Riverside PB User Group every time.
Glad to see PB still being used there.

Does anyone have any experience with trying the Ink Control for this
purpose?

Thanks again.

"Shenn Sellers" <ssel...@co.riverside.ca.us> wrote in message
news:42814745$1@forums-1-dub...

[Ken Judkins]

Richard,

I found the code in the CodeXChange sample to be helpful. When I was
experimenting, I had a problem with the picture not displaying. Finally I
realized I had an invalid Key Clause value. Apparently it needs to be
table_key = :table_key. The sample doesn't have the Table, Col For Ink
Data, or Col For Image fields completed, but on my experiment I put in the
DW table name and the two blob columns. It occurred to me later the
Definition tab page should allow me to set up a separate table just for
pictures, but since the pictures were in the same table as in the DW, the
Key Clause has the table key referencing itself.

I'm using Oracle and created two Blob columns.

Roughly what I did:
Create a table with two Blob columns
Create a datawindow with all the table columns except the two blobs
Drop an InkPicture object on the DW
Fill in the Definition tab page as described above
Leave the InkPicture tab page basically as is
In script, insert a row in the DW
Update DW
In script, do an INSERTBLOB to the picture blob column (Col For Image
column)
Update DW
In script, retrieve the DW. Note: The picture should be visible in the DW
painter preview, too.

I wanted the same ability to display pictures without writing files to disk
before displaying them in the DW. You can see a running conversation on
just this topic starting with Monica's 4/27 message in this newsgroup and
continuing with my 5/5 message in the Futures Discussion. Feel free to
chime in on the Futures Discussion topic, because I'm trying to convince
Bruce that PB REALLY needs a database to datawindow picture object.

Hope this helped!

Ken Judkins

"Richard Keller" <ric...@kellersystems.com> wrote in message

news:42816a45$1@forums-2-dub...

[Richard Keller]

This only relates to Blobs so I don't get flamed that MSSQL requires
Autocommit.

"Richard Keller" <ric...@kellersystems.com> wrote in message

news:428261b5@forums-1-dub...
>I think part of my problem is the Transaction Object is MSSQL must have
>AutoCommit = TRUE and this causes problems with rest of the application.
>Does Oracle have the same restriction when dealing with blob tables?
>
>
> "Ken Judkins" <nos...@notime.not> wrote in message
> news:4281f28a@forums-2-dub...

[Richard Keller]

I think part of my problem is the Transaction Object is MSSQL must have
AutoCommit = TRUE and this causes problems with rest of the application.
Does Oracle have the same restriction when dealing with blob tables?

"Ken Judkins" <nos...@notime.not> wrote in message
news:4281f28a@forums-2-dub...

[Jason 'Bug' Fenter]

Richard Keller wrote:
> I think part of my problem is the Transaction Object is MSSQL must have
> AutoCommit = TRUE and this causes problems with rest of the application.
> Does Oracle have the same restriction when dealing with blob tables?

My understanding was that this was a PB thing unrelated to the back end.
There's good news, though. AutoCommit is a "live" setting. You can turn
it on without having to reconnect. So, turn it on, do your SELECTBLOB or
UPDATEBLOB, then turn it back off so that the rest of the app doesn't
have any issues.

[jiimmorris]

I concure that PowerBuilder is woefully incomplete without
the ability to display pictures directly from the database
to a column control in picture mode. All the picture
parsing code already exists in the code base as seem in the
regular picture control so it should be just above trivial
to implement this. On the other had the masses have to
struggle to get anything at all to work and generally give
up on the desired design. In this case we want reports to
include signature that are stored as bitmaps. Hopefully
someone, someday will get the message and see the light.

> ****************** **/