IBSQL and multiple SQL clauses

JK

unread,

Jan 22, 2003, 7:05:40 AM1/22/03

to

Sorry one line was missed

IBSql:=TIBSql.Create(self.DB);

IBSql.database:=self.DB;
IBSql.transaction:=IBSql.database.DefaultTransaction;
IBSql.transaction.StartTransaction;
try
IBSql.Sql.add('insert into persons values (1,"person1");');
IBSql.Sql.add('insert into persons values (2,"person2");');
IBSql.Sql.add('insert into persons values (3,"person3");');
IBSql.ExecQuery; //exception happens here
IBSql.transaction.Commit;
finally
IBSql.Free;
end;

JK

unread,

Jan 22, 2003, 7:04:18 AM1/22/03

to

Hi

I have following code

IBSql:=TIBSql.Create(self.DB);

IBSql.database:=self.DB;
IBSql.transaction:=IBSql.database.DefaultTransaction;
IBSql.transaction.StartTransaction;
try
IBSql.Sql.add('insert into persons values (1,"person1");');
IBSql.Sql.add('insert into persons values (2,"person2");');
IBSql.Sql.add('insert into persons values (3,"person3");');

IBSql.transaction.Commit; //exception happens here
finally
IBSql.Free;
end;

If add only one SQL add time to IBSQL component then everything works fine
but if i put more than SQL clause at the once then exception is raised.

Could anyone give a help or tell the reason why it is so?

I appreciate your answers!

Will Honor

unread,

Jan 22, 2003, 7:21:03 AM1/22/03

to

JK wrote:

> Sorry one line was missed
>

> IBSql:=TIBSql.Create(self.DB);
>
> IBSql.database:=self.DB;
> IBSql.transaction:=IBSql.database.DefaultTransaction;
> IBSql.transaction.StartTransaction;
> try
> IBSql.Sql.add('insert into persons values (1,"person1");');
> IBSql.Sql.add('insert into persons values (2,"person2");');
> IBSql.Sql.add('insert into persons values (3,"person3");');

> IBSql.ExecQuery; //exception happens here
> IBSql.transaction.Commit;
> finally
> IBSql.Free;
> end;

You can only use one statement in TIBSQL at a time.
you need to use TIBScript for what you are doing.
Or you could modify your code as follows :

IBSql.database:=self.DB;
IBSql.transaction:=IBSql.database.DefaultTransaction;
IBSql.transaction.StartTransaction;
try

IBSql.Sql.clear ;

IBSql.Sql.add('insert into persons values (1,"person1");');

IBSql.ExecQuery;

IBSql.Sql.clear ;

IBSql.Sql.add('insert into persons values (2,"person2");');

IBSql.ExecQuery;

IBSql.Sql.clear ;

IBSql.Sql.add('insert into persons values (3,"person3");');

IBSql.ExecQuery; //exception happens here

IBSql.transaction.Commit;
finally
IBSql.Free;
end;

Regards Will

JK

unread,

Jan 22, 2003, 9:21:41 AM1/22/03

to

Thanks but

>
> You can only use one statement in TIBSQL at a time.
> you need to use TIBScript for what you are doing.

I use delphi5 and I can't find any component TIBScript.

Do you know where I can can find it?

Craig Stuntz [TeamB]

unread,

Jan 22, 2003, 9:28:49 AM1/22/03

to

In article <3e2ea8b3$1...@newsgroups.borland.com>,
juha....@rexpartners.com says...

> I use delphi5 and I can't find any component TIBScript.
>
> Do you know where I can can find it?
>

Make sure you are using IBX 5.04, from Jeff Overcash's page on
CodeCentral.

-Craig

--
Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH
Delphi/InterBase Weblog : http://delphi.weblogs.com
InterBase Perf. Monitor : http://delphi.weblogs.com/IBPerformanceMonitor
InterBase PLANalyzer 1.1: http://delphi.weblogs.com/IBPLANalyzer

JK

unread,

Jan 27, 2003, 9:21:18 AM1/27/03

to

Thanks a lot! That was the reason why i didn't find TIBQuery component.

By the way which supposed to be faster?
TIBQuery or TIBSQL like how Will Honor demostrated in his reply?

Short test i made was that performance was almost same with over 10000
sql clauses.

Will Honor

unread,

Jan 27, 2003, 9:24:20 AM1/27/03

to

JK wrote:

> Thanks a lot! That was the reason why i didn't find TIBQuery component.

Do you mean TIBScript or TIBQuery?

>
> By the way which supposed to be faster?
> TIBQuery or TIBSQL like how Will Honor demostrated in his reply?
>
> Short test i made was that performance was almost same with over 10000
> sql clauses.

I believe that all methods should be about the same. They all use a
TIBSQL to actually run the SQL.
Regards Will.

JK

unread,

Jan 27, 2003, 10:25:21 AM1/27/03

to

"Will Honor" <(NOSPAM) @ (NOSPAM).net> wrote in message
news:3e35...@newsgroups.borland.com...

> JK wrote:
>
> > Thanks a lot! That was the reason why i didn't find TIBQuery component.
> Do you mean TIBScript or TIBQuery?
>

Sorry, i meant TIBScript.

> I believe that all methods should be about the same. They all use a
> TIBSQL to actually run the SQL.

Do you know any other ways to get the performance to little bit better of
faster?

Now when i executing something like 10000 SQL queries it takes around 60
seconds?

Will Honor

unread,

Jan 27, 2003, 12:17:41 PM1/27/03

to

JK wrote:

Use a stored procedure to do the insert statements.
(already prepared, less network traffic)
Commit every 1000 records.
(as the transaction gets larger it slows down.

Jeff Overcash (TeamB)

unread,

Jan 27, 2003, 1:03:54 PM1/27/03

to

"Will Honor" <(NOSPAM) @ (NOSPAM).net> wrote:
>
>Use a stored procedure to do the insert statements.
>(already prepared, less network traffic)

No, you still have to prepare the SP itself. One Prepare for
an IBSQL and 1 Prepare for an IBStoredProc per execution of hte
SP. Network traffic is lighter for IBSQL.

All an SP has over a straight SQL statement is the ms's it
takes to compile the statement once in IBSQL but is already
compiled in the SP. Of course you have to compile the SP
executing statement anyways so even there there is no
performance gain (actually a loss since IBStoredProc unprepares
itself after each execution so has to be prepared again for hte
next execution).

>Commit every 1000 records.
>(as the transaction gets larger it slows down.

IBSQL holds its prepare over hte lifetime of the component, IBStoredProc will actually prepare every statement each time.

--
Jeff Overcash (TeamB)