I have a bug (surprise, surprise) in a user defined function and I think its this bit that's at fault

 ms := TmemoryStream.Create;
ms.Write(Pointer(FldStr)^, Length(FldStr) * SizeOf(Char));
ms.Position := 0;

and I think its at fault because in the function definition I'm passing it as a string


function CVSearch(const docType, FldStr, TstStr, CompStr, CI: string): boolean;

but in the call I'm using .AsBlob (which is what FldStr is)

else if ckn = 'cvsearch' then Params.FindParam('Result').AsBoolean := CVSearch(Params.FindParam('docType').AsString, Params.FindParam('FldStr').AsBlob, Params.FindParam('TstStr').AsString, Params.FindParam('CompStr').AsString, Params.FindParam('CI').AsString);


Help please.

Roy Lambert

Roy,

<< I have a bug (surprise, surprise) in a user defined function and I think
its this bit that's at fault

ms := TmemoryStream.Create;
ms.Write(Pointer(FldStr)^, Length(FldStr) * SizeOf(Char));
ms.Position := 0;

and I think its at fault because in the function definition I'm passing it
as a string

function CVSearch(const docType, FldStr, TstStr, CompStr, CI: string):
boolean;

but in the call I'm using .AsBlob (which is what FldStr is) >>

What exactly is the problem that you're seeing ?  AsBlob will return a
string up until Delphi 2009, after which it returns an array of bytes.
However, a type mismatch like that should get caught by the compiler, so
that can't be the issue, and the above code is fine for working with a long
AnsiString.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>What exactly is the problem that you're seeing ? AsBlob will return a
>string up until Delphi 2009, after which it returns an array of bytes.
>However, a type mismatch like that should get caught by the compiler, so
>that can't be the issue, and the above code is fine for working with a long
>AnsiString.

I'm currently using D2006.

What's happening is I'm storing a document in this column (generally it will be a Word document but might be pdf, spreadsheet or some other form of wp) and I want to be able to do a word search using my own SQL function. To do this I pass the column as one of the parameters and, when its a Word document I'm loading it into Scalabium's MSWord component to extract the text for further processing. This component has LoadFromFile and LoadFromStream methods neither of which I can get to work because whilst in EDBManager I can see (say) 32Kb of BLOB data when it reaches the user defined function I seem to have only 4 bytes left - I added this showmessage(fldstr+' ...:'+inttostr(length(FldStr))); before I do anything with the data and received ???? ....:4 as the message. FldStr is the blob field I'm passing across. If I push it into the memorystream and save to file I end up with a file containing ????.

From my end it looks as though the data isn't getting through properly. On the other hand I could be misinterpreting things or doing something totally wrong or not allowed.

I hope that helps cos I'm stuck at trying to explain it better.

Roy Lambert

Tim


The latest is I added a new function with just the parameter changed from BLOB to CLOB  

EXECUTE IMMEDIATE 'CREATE FUNCTION "xxx" (IN "docType" VARCHAR COLLATE ANSI, IN "FldStr" CLOB, IN "TstStr" VARCHAR COLLATE ANSI, IN "CompStr" VARCHAR COLLATE ANSI, IN "CI" VARCHAR COLLATE ANSI)
RETURNS BOOLEAN
EXTERNAL NAME "nlhFunctions"';

and the .AsBlob changed to .AsString


xxx' then Params.FindParam('Result').AsBoolean := CVSearch(Params.FindParam('docType').AsString, Params.FindParam('FldStr').AsString, Params.FindParam('TstStr').AsString, Params.FindParam('CompStr').AsString, Params.FindParam('CI').AsString);

and CAST the field in the sql statement

xxx(_LatestCVType, cast(_LatestCV as clob),  'CONTAINS' , 'watling', 'Y')

This works and even returns the correct result which I think validates my code and indicates that the problem is something to do with the parameter passing or the .AsBlob


Roy Lambert

Roy,

<< This works and even returns the correct result which I think validates my
code and indicates that the problem is something to do with the
parameter passing or the .AsBlob >>

I'm still looking into this, and should have something for you soon.

--
Tim Young
Elevate Software
www.elevatesoft.com