EAccessViolation whene executing long SQLCommand with TSQLQuery

Thread View: borland.public.cppbuilder.database.dbexpress

5 messages

[T] Back to Threads [V] Tree View [G] Group Articles

5 total messages Started by Alper YAZGAN Tue, 17 Jul 2007 13:24

#153

Author: Alper YAZGAN
Date: Tue, 17 Jul 2007 13:24

32 lines
2006 bytes

Is there a sql statement limit when passing SQLCommand thru 
TSQLQuery.Execute() ?
Because I get EAccessViolation during execution of  batches of sql 
statements (mostly inserts) 90K in size ? (And I could execute statements of 
30-40K)

Any comment ?
Thanks,

Alper YAZGAN

PS.
...
  sPointer<TSQLQuery> sqlQuery(new TSQLQuery(NULL));
    sqlQuery->SQLConnection = &sqlConnection;
    sqlQuery->NumericMapping = false;
    sqlQuery->ObjectView = false;
    sqlQuery->GetMetadata = false;
    sqlQuery->ParamCheck = false;
    sqlQuery->Prepared = false;
    sqlQuery->MaxBlobSize = 0;
    try
      {
        sqlConnection->Open();
        sqlQuery->SQL->Text = SQLStatement;  // 90K in size
        sqlQuery->ExecSQL(true);
      }
    catch (Exception &exception)
      {
            // Catches EAccessViolation
      }
...

Re: EAccessViolation whene executing long SQLCommand with TSQLQuery

#154

Author: Dave
Date: Tue, 17 Jul 2007 14:55

15 lines
1852 bytes

"Alper YAZGAN" <alperyazgan@hotmail.com> wrote in message 
news:469cc35d@newsgroups.borland.com...
> Is there a sql statement limit when passing SQLCommand thru 
> TSQLQuery.Execute() ?

I know this is not an answer to your actual question (size limit?), but a 
seemingly obvious solution would be to break down the 90k SQL into smaller 
batches if possible.  Maybe a bit of a hassle, but if you *know* 30-40k 
seems to work you can run with that.  Through a little experimentation with 
scaled down batch sizes, you might even be able to get close to the exact 
size where things stop working.

If there is a limit, I would guess it is around 64k.

Dave

Re: EAccessViolation whene executing long SQLCommand with TSQLQuery

#155

Author: Alper YAZGAN
Date: Tue, 17 Jul 2007 22:17

15 lines
1604 bytes

Thank you for your reply.

"Dave" <DaytonaDave@dslextreme.com> wrote in message 
news:469cd7bf$1@newsgroups.borland.com...
> If there is a limit, I would guess it is around 64k.
>
> Dave

Yes, actually it seems to be ~64K.
I could have run 1-1.2M batches with dbGo (TADOConnection, TADOQuery, etc.).
Now I will have to break down as you say.

Thanks,

Alper YAZGAN

Re: EAccessViolation whene executing long SQLCommand with TSQLQuery

#156

Author: Dave
Date: Wed, 18 Jul 2007 04:08

13 lines
1575 bytes

> Yes, actually it seems to be ~64K.
> I could have run 1-1.2M batches with dbGo (TADOConnection, TADOQuery, 
> etc.).
> Now I will have to break down as you say.

Glad you got it to work.

I had a few curious questions for you:
Do super size batches run quicker?
Are you using transactions?
If so, do your rollback the entire batch if a single insert fails?

I have never used SQL statements so large myself...

Re: EAccessViolation whene executing long SQLCommand with TSQLQuery

#157

Author: Alper YAZGAN
Date: Wed, 18 Jul 2007 15:10

28 lines
2560 bytes

> I had a few curious questions for you:
> Do super size batches run quicker?

    It depends on what database you are using :-)
    I am currently and mostly using Sybase ASE and ASA, so they are fast 
when you want to run in batches like 1000 lines of inserts
> Are you using transactions?
   Yes, a batch does consist of a whole transaction.
> If so, do your rollback the entire batch if a single insert fails?
   Again yes, so actually I want this style of execution deliberately, but 
alternatively you can use (if you run insert statements) "if not exists then 
insert else update" style statements
> I have never used SQL statements so large myself...

   I developed a replication server, currently running on more than 350 
sites (Remote site dbs are ASA and the Central Server is ASE). My 
application replicates transaction continously - whenever the network is 
online - to the consolidated site - central server - so I need to batch the 
accumulated transactions from remote sites to the central server and it runs 
fast in batches.

My problem is that I could  run 1000 inserts in one batch using dbGo (ADO) 
but do not run using dbExpress drivers because of sql statement buffer  size 
limit.

Thank you again,

Alper YAZGAN

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads