EAccessViolation whene executing long SQLCommand with TSQLQuery

[Alper YAZGAN]

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
}
...

[Dave]

"Alper YAZGAN" <alper...@hotmail.com> wrote in message
news:469c...@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

[Alper YAZGAN]

Thank you for your reply.

"Dave" <Dayto...@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

[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.

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...

[Alper YAZGAN]

> 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