I'm getting a stack overflow when parsing SQL. I know it is my SQL that is
doing it, but I'd like to know how to work around it. Essentially I have a
large INI file that I'm putting into a memo field in a database. The SQL
has been working fine for ages, but now the INI file may be bigger and
different to normal I'm using Engine.QuotedAnsiStr(szIniFileStuff) and I
suspect that it is the quoting that is causing the problem. (Code for the
routine below signature.)

My SQL looks, according to Codesite which might be doing nice display
things with the #10's, like:

UPDATE RequestStore SET rsState = 3 , rsCompleteTime = '2006-02-02
19:18:44.98' , rsResultStr =
'[Market]'+#13+''+#10+'MarketID=2725530'+#13+''+#10+'EventName=NOT-
SPECIFIED'+#13+''+#10+'Country=GBR'+#13+''+#10+'SportID=NOT-
SPECIFIED'+#13+''+#10+'MarketOpenedDate=2005-05-
05T06:00:00.000Z'+#13+''+#10+'MarketType=O'+#13+''+#10+'Status=ACTI'+#13+'
'+#10+'NoOfWinners=1'+#13+''+#10+'MarketInfo=NOT-
SPECIFIED'+#13+''+#10+'ParentEveID=NOT-
SPECIFIED'+#13+''+#10+'MarketDate=0001-01-
01T00:00:00.000Z'+#13+''+#10+'InplayDelay=NOT-
SPECIFIED'+#13+''+#10+'MenuPath=NOT-
SPECIFIED'+#13+''+#10+'RunnersMayBeAdded=NOT-
SPECIFIED'+#13+''+#10+'StartTime=2005-05-
05T06:00:00.000Z'+#13+''+#10+'TimeZone=UKT'+#13+''+#10+'Timestamp=NOT-
[lots snipped]
SPECIFIED'+#13+''+#10+'BackOdds3=NOT-
SPECIFIED'+#13+''+#10+'BackStake3=NOT-SPECIFIED'+#13+''+#10+'LayOdds3=NOT
-SPECIFIED'+#13+''+#10+'LayStake3=NOT-
SPECIFIED'+#13+''+#10+'TotalAmountMatched=NOT-
SPECIFIED'+#13+''+#10+''+#13+''+#10+'' , rsSearchTime = '00:00:02.297'
WHERE rsID = 633153

I think it is 24,970 bytes long. It fails with lots of
EvaluateExpressionToken calls on the stack. Actually, to be precise (and
thanks to Delphi being able to copy the stack to the clipboard, it is
failing with 2,440 calls on the stack.

What should I do to stop this happening please?

/Matthew Jones/


procedure TStateMachine.SetWorksState(const nTableID : Integer; const
nStateID : Integer; const szResultStr : string; const tmDuration :
TDateTime);
var
 szSQL : string;
begin
   szSQL := 'UPDATE RequestStore ';
   szSQL := szSQL + 'SET ';
   szSQL := szSQL + 'rsState = ' + IntToStr(nStateID) + ' ';
   szSQL := szSQL + ', rsCompleteTime = ' + Engine.QuotedSQLStr
(Engine.DateTimeToAnsiStr(Now, true)) + ' ';
   if trim(szResultStr) <> '' then
   begin
      if szResultStr = 'NULL' then
      begin
         szSQL := szSQL + ', rsResultStr = NULL ';
      end
      else
      begin
         szSQL := szSQL + ', rsResultStr = ' +
Engine.QuotedSQLStr(szResultStr) + ' ';
      end;
   end;
   if tmDuration <> 0.0 then
      szSQL := szSQL + ', rsSearchTime = ' +
Engine.QuotedSQLStr(Engine.TimeToAnsiStr(tmDuration, true)) + ' ';
   szSQL := szSQL + 'WHERE rsID = ' + IntToStr(nTableID) + ' ';

   Report('WORKSTATE SQL = ' + szSQL);
   try
      m_xUpdateQuery.SQL.Text := szSQL;
      m_xUpdateQuery.ExecSQL;
   finally
      m_xUpdateQuery.Close;
   end;
end;

Would using a parameter help me? I'll try that I think since I'm stuck
until this is resolved.

/Matthew Jones/

Matthew


It'll take a little longer but why not load the .ini into a TStringList and do each line separately?


Roy Lambert

Each line separately how? I need them all stuck in a memo field so that
they can be read later. I also need high speed, so a single call rather
than hundreds of calls / records is important. Indeed, there's another
thread sitting watching the database for the status to be set to complete
to grab the results and run with them.

Maybe the answer for now is to switch to using a table and write the field
directly. Hmm. (I've not been on this until now as our TeraStation backup
box decided to die completely, and I've been trying first aid on it.)

/Matthew Jones/

> Maybe the answer for now is to switch to using a table and write the
> field directly.

FWIW, this provides me with a workaround for now. May even be faster I
suppose?

/Matthew Jones/

Matthew


Sorry, I misinterpreted your code. I would agree with you that a table would probably be the easiest solution.

Good luck with the bandages!

Roy Lambert

Matthew,

<< What should I do to stop this happening please? >>

You have two options.  One is to use a parameter, and the other is to
increase the stack size for the application (Project/Options/Linker).

--
Tim Young
Elevate Software
www.elevatesoft.com

The parameter looks ideal - I'll try that to make sure it works. This
latest project (two actually) have been the first major projects I've used
SQL for in DBISAM, so I'm re-using a lot of techniques all over.

/Matthew Jones/

> You have two options.  One is to use a parameter, and the other is to
> increase the stack size for the application (Project/Options/Linker).
Excuse me for intruding here, but I'd personally (IMHO) consider this
a bug. Any sequence of + operators should consume no more then 2
levels of stack. I see no reason for this to be implemented differently.
So, although, the query is obviously very lengthy, which should naturally
suggest that author should rethink such design, evaluating consecutive
operations recursively is also not a very good solution... and also
not an efficient one. Another thing is if Matthew would have had 2000
consecutive parenthesis - there's little anyone could do about such
expression.

Regards,
Michael

Michael,

<< Excuse me for intruding here, but I'd personally (IMHO) consider this a
bug. Any sequence of + operators should consume no more then 2 levels of
stack. I see no reason for this to be implemented differently. So, although,
the query is obviously very lengthy, which should naturally suggest that
author should rethink such design, evaluating consecutive operations
recursively is also not a very good solution... and also not an efficient
one. Another thing is if Matthew would have had 2000 consecutive
parenthesis - there's little anyone could do about such expression. >>

Parentheses don't consume stack space.  Only binary operations do, and the
only time it is even an issue is the concatenation of large strings with
many components.

--
Tim Young
Elevate Software
www.elevatesoft.com