How do I retrieve a BLOB value/data (NOTsegment/pic/raw/cursor) using a single Valentina SQL Statement

[Barry G. Sumpter]

Brilliant answer Bart!

Thanks so much for taking the time and making such a verbose effort!


This should be added to the wiki.  

-----

I finally realized I just wanted to retrieve the BLOB as a getstring like I
do every other field type.

myCursor.Field(n).getstring

And NOT having it return 10 or 19.  

I didn't want to have to know which field is a blob 
or have to query its data type first then retrieve as a blob just to get its
value. etc.


baz

-----Original Message-----
From: valentina-bounces at lists.macserve.net
[mailto:valentina-bounces at lists.macserve.net] On Behalf Of Bart Pietercil
Sent: Thursday, 25 October 2007 4:17 PM
To: Valentina Developers
Subject: Re: How do I retrieve a BLOB value/data (NOTsegment/pic/raw/cursor)
using a single Valentina SQL Statement

Hi Barry,

If I understand you correctly you just want to obtain a cursor  
containing a blob stored in table X ?

So in RB you would do something like:

Dim myCurs as VCursor
Dim myQuery as String
Dim aBlob as vBlob
Dim y as Integer

myQuery = "Select blobfield from table x where recid = y"
myCursor = myDB.ExecuteSelect(myquery, param,....)
aBlob = myCursor.field(blobfield).blobvalue

....

Above is clearly with errors but you get the idea

Now if you would like to have this in a Stored Procedure it would  
look like this (again untested)

Create or Replace sp_ReturnABlob(IN arecid LONG)
begin
SELECT blobfield from table x where recid = arecid;
end


and in RB you'ld do this

Dim myCurs as VCursor
Dim aBlob as vBlob
Dim y as Integer

y = somerecid
myCursor = myDB.ExecuteSelect("CALL sp_ReturnABlob(y)")
aBlob = myCursor.field(blobfield).blobvalue


HTH

Bart

hmm, NOT a cursor you say ?

Then instead of creating a Stored Procedure use a Stored Function

Create or replace sf_ReturnABlob(IN arecid LONG) RETURNS VBlob
	Declare aBlob VBlob;
Begin
SET aBlob = SELECT blobfield from table x where recid = arecid;
RETURN aBlob;
END


In RB you still have

aBlobField = myDB.ExecuteSelect("SELECT sp_ReturnABlob(arecid)")


Hope you get the picture


Not to say you could not do it the way you suggest. However setting  
an @variable still implies that you select the variable with a select  
call from within RB, so why bother and create overhead ?


HTH

Bart

On 25-okt-07, at 00:41, Barry G. Sumpter wrote:

>
> Any one at all know how to do something like this?
>
>
> Select ReturnValentinaBlobData(myBlob) from tbl
>
>
> 'I'm jut making this up
> Create SP ReturnValentinaBlobData (@myBlobFieldIn IN,  
> @myBlobDataOut OUT)
>         If @myBlobFieldIn <> "" then
>                mySelectedBlobResult = Select vBlob(@myBlobField)  
> from tbl
>                @myBlobDataOut = mySelectedBlobResult
>         End if
>
>
>
>
>
>
> BTW, the 3.5b6 is NOT crashing anywhere near as much.
> Stored Procs are saving without being deleted etc.
> No crash on open of DBs.
>
>
>
> -----Original Message-----
> From: valentina-bounces at lists.macserve.net
> [mailto:valentina-bounces at lists.macserve.net] On Behalf Of Barry G.  
> Sumpter
> Sent: Thursday, 25 October 2007 7:36 AM
> To: 'Valentina Developers'
> Subject: RE: How do I retrieve a BLOB value/data (NOT segment/raw)
> inValentina SQL Statement
>
> With just a simple sql query?
>
> i.e.  no curs object
>
> Select * from tbl
>
> ???
>
> -----Original Message-----
> From: valentina-bounces at lists.macserve.net
> [mailto:valentina-bounces at lists.macserve.net] On Behalf Of Ruslan  
> Zasukhin
> Sent: Thursday, 25 October 2007 4:46 AM
> To: valentina at lists.macserve.net
> Subject: Re: How do I retrieve a BLOB value/data (NOT segment/raw) in
> Valentina SQL Statement
>
> On 24/10/07 8:44 PM, "Barry G. Sumpter" <BarrySum at BigPond.Net.AU>  
> wrote:
>
>>
>> OK, but how do I return the value thru V4RB with a valentina SQL
> statement?
>
>     dim fBlob as VBLOB
>
>     curs = db.SqlSelect( query )
>
>     fBLOB = curs.VBLOB("fldBlob")
>
>     fBLOB.Read()
>
>     ...
>
> -- 
> Best regards,
>
> Ruslan Zasukhin
> VP Engineering and New Technology
> Paradigma Software, Inc
>
> Valentina - Joining Worlds of Information
> http://www.paradigmasoft.com
>
> [I feel the need: the need for speed]
>
>
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina
>
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina
>
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina

_______________________________________________
Valentina mailing list
Valentina at lists.macserve.net
http://lists.macserve.net/mailman/listinfo/valentina