When using custom SQL functions, is it safe to create an object once in the main thread -- in the same place with the Engine.Functions.CreateFunction() calls are made -- and use that one instance of the object within all custom functions?

We have been doing this for years in our main application (several thousand users) but periodically run into invalid pointer and AV errors that seem to be related to heavy use of this object in SQL queries -- especially if the data returned from one of the string functions is very long. It got me wondering whether DBISAM is using multiple threads to resolve custom function calls or doing something else that would mess up this object from one call to the next.

Basically, this object is given a BLOB as a string (which actually represents a special type of database record with a variable number of fields) and has methods to manipulate data within the BLOB to extract fields, etc. The data in the object changes with each database record and there are often several custom functions within a SQL query that access the object.

Thanks,
Danny

Daniel


I'm not Tim so I don't have a definitive answer, but in the very app that I'm typing this response in I use custom functions multithreaded with no problems, and have for years. Have you tried adding MadExcept or Eurekalog into your app to identify where things are happening?

Roy Lambert [Team Elevate]

Roy,

Yes, I've been using it that way for years, also, and never thought it was a problem. This just got me looking for what I might be doing wrong. It's not always an AV error -- sometimes it just causes the query to freeze.

I'm narrowing in on what might be the cause and I think it might have something to do with returning a long string from a custom SQL function and, perhaps, when that query uses an INSERT INTO to copy the data into another table.

I have a database from one of our clients that exhibits this behavior. Through much trial, I found that one particular record causes the problem. If I exclude that record from the query, the query runs fine. If I include it, the query locks up. On this record, one of my custom SQL functions returns a very long string (600+ characters). This is an INSERT INTO query and this value is inserted into a 100-character field in the target table.

As an experiment, I modified the function to truncate the returned string to 100 characters. That made the problem go away! I continued experimenting, changing the truncation length, and found that it failed always failed if the returned string was more than 524 characters. 524=no problem! 525=freeze!

Here is the string that the function returns (I half expected to find some funky character at position 525 but that isn't the case:

"Complementary and alternative medicine sourcebook : basic consumer health information about ayurveda, acupuncture, aromatherapy, chiropractic care, diet-based therapies, guided imagery, herbal and vitamin supplements, homeopathy, hypnosis, massage, meditation, naturopathy, Pilates, reflexology, reiki, shiatsu, tai chi, traditional Chinese medicine, yoga, and other complementary and alternative medical therapies ; along with statistics, tips for selecting a practitioner, treatments for specific health conditions, a glossary of related terms, and a directory of resources for additional help and information"

This is not a good solution because this function is used by many queries, some of which need the entire length of the string -- whether it be 6 or 600 characters long. I am beginning to think the problem might be in DBISAM. I am using 4.26 (haven't upgraded yet because we have a huge installed base that could get burned by the BLOB writing incompatibility of 4.28+).



Danny

Daniel


For the INSERT query try SUBSTRing the text to the right length and see what happens. Even if it is a bug in DBISAM if you're stuck on an older version you're probably out of luck unless you have the source and can mod it yourself.

Roy Lambert [Team Elevate]

Roy,

I actually tried that first (I used LEFT -- not SUBSTR). It didn't work. The only thing that worked was truncating the string before returning it from the custom function.

I'm willing to see if 4.29 solves the problem but I didn't see anything in the history that looked like this.

Danny

Just installed and re-compiled with 4.29 Build 4 -- same problem.

Daniel

> I actually tried that first (I used LEFT -- not SUBSTR). It didn't work.
The only thing that worked was truncating the string before returning it
from the custom function.
>

Is this client/server?

Can you post the function you are using?

I tried the following custom function in an insert query (inserting into a
char(100) field) without any problem.

 if(AnsiCompareText(FunctionName,'LONGSTRING')=0) then
 result := 'Complementary and alternative medicine sourcebook : basic
consumer health information about ayurveda, acupuncture, aromatherapy,
chiropractic care, diet-based therapies,'+
 ' guided imagery, herbal and vitamin supplements, homeopathy, hypnosis,
massage, meditation, naturopathy, Pilates, reflexology, reiki, shiatsu, tai
chi, traditional Chinese medicine, yoga,'+
 ' and other complementary and alternative medical therapies ; along with
statistics, tips for selecting a practitioner, treatments for specific
health conditions, a glossary of related terms,'+
 ' and a directory of resources for additional help and information';

John

Danny,

<< When using custom SQL functions, is it safe to create an object once in
the main thread -- in the same place with the
Engine.Functions.CreateFunction() calls are made -- and use that one
instance of the object within all custom functions?  >>

It depends upon what you're doing with the object.  If you're modifying any
of the member variables in the custom function, then no, that is definitely
not thread-safe.  If the object only modifies data that is passed in as
parameters, then you will be okay.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" wrote:

Danny,

<< When using custom SQL functions, is it safe to create an object once in
the main thread -- in the same place with the
Engine.Functions.CreateFunction() calls are made -- and use that one
instance of the object within all custom functions?  >>

It depends upon what you're doing with the object.  If you're modifying any
of the member variables in the custom function, then no, that is definitely
not thread-safe.  If the object only modifies data that is passed in as
parameters, then you will be okay.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

I do two things with the object during each call from a custom function:

1. Set a string member variable to the value of a string passed to the SQL function (from a BLOB)
2. Call a method to return a string (usually) based on the first

I could use simple functions here instead of the object but this object class is a key part of the application and is used in many places -- not just in the SQL function. It made more sense to just use the object.

It never caused me any problems but, in any case, I have eliminated that and now create and destroy the object within each custom SQL function to keep things nice and safe.

I'm still having the problem with returning large (525+ character) strings from custom SQL functions causing a query lockup.

Danny

John,

It's file-system -- not client/server -- though the code is designed to work either way.

The code as-is wouldn't do you much good because it has a lot of dependencies that you don't have and a lot of extra un-related code that you don't need. But, here is snip from it:

FMarc := TMarc.Create(FUseANSEL);

if (AnsiCompareText(FunctionName,'MarcField')=0) then begin
     try
       FMarc.Marc := FunctionParams[0].AsString;
       Result := MarcField(FMarc,
                           FunctionParams[1].AsInteger,
                           lowercase(FunctionParams[2].AsString+' ')[1],
                           FunctionParams[3].AsInteger,
                           FunctionParams[4].AsInteger,
                           False, False);
       if Result='' then
         Result := null;
     except
       Result := null;
     end;
   end