Multiple SQL statements in a single ZSQL method?
Is is possible to insert multiple SQL statements into a single ZSQL method? Can anyone provide a simple example for doing this? i.e.; in a single ZSQL method: INSERT INTO tbl1 VALUES (field1); INSERT INTO tbl2 VALUES (field1); Harlow Pinson Indepth Learning Email: hpinson@indepthl.com Web: http://www.indepthl.com Voice: 505-994-2135 FAX: 208-475-7678
Hi, Just place ; at the end of sql statement. delete from table1; insert into table1 values(1,'Hello'); insert into table1 values((select MAX(teable1_primary_key) from table1)+1,'Hello Again'); --- Harlow Pinson <hpinson@indepthl.com> wrote:
Is is possible to insert multiple SQL statements into a single ZSQL method? Can anyone provide a simple example for doing this?
i.e.; in a single ZSQL method:
INSERT INTO tbl1 VALUES (field1);
INSERT INTO tbl2 VALUES (field1);
Harlow Pinson Indepth Learning Email: hpinson@indepthl.com Web: http://www.indepthl.com Voice: 505-994-2135 FAX: 208-475-7678
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists -
http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
__________________________________________________ Do You Yahoo!? Check out Yahoo! Shopping and Yahoo! Auctions for all of your unique holiday gifts! Buy at http://shopping.yahoo.com or bid at http://auctions.yahoo.com
Actually <dtml-var sql_delimiter> is the preferred method. Florent Vid Bijelic <zoops4@yahoo.com> wrote:
Hi,
Just place ; at the end of sql statement.
delete from table1;
insert into table1 values(1,'Hello');
insert into table1 values((select MAX(teable1_primary_key) from table1)+1,'Hello Again');
--- Harlow Pinson <hpinson@indepthl.com> wrote:
Is is possible to insert multiple SQL statements into a single ZSQL method? Can anyone provide a simple example for doing this?
i.e.; in a single ZSQL method:
INSERT INTO tbl1 VALUES (field1);
INSERT INTO tbl2 VALUES (field1); -- Florent Guillaume, Nuxeo (Paris, France) +33 1 40 33 79 10 http://nuxeo.com mailto:fg@nuxeo.com
Just place ; at the end of sql statement.
When I do that, I get an ODBC error: Error Type: sql.error Error Value: ('37000', -3517, '[Microsoft][ODBC Microsoft Access Driver] Characters found after end of SQL statement.') Here's my very simple SQL statement, in the ZSQL method: INSERT INTO tblUsers VALUES ( 5 ); INSERT INTO tblParents VALUES ( 5 ); The first statement by itself works fine. Harlow Pinson Indepth Learning Email: hpinson@indepthl.com Web: http://www.indepthl.com Voice: 505-994-2135 FAX: 208-475-7678
Harlow, IIRC it depends on the ODBC/RDBMS implementations: check your docs for these and you should be able to work out if they support multiple sql statements... g On 18 Dec 2001, at 9:43, Harlow Pinson wrote: From: "Harlow Pinson" <hpinson@indepthl.com> To: Vid Bijelic <zoops4@yahoo.com> Subject: Re: [Zope] Multiple SQL statements in a single ZSQL method? Send reply to: hpinson@indepthl.com Copies to: zope@zope.org Priority: normal Date sent: Tue, 18 Dec 2001 09:43:56 -0700
Just place ; at the end of sql statement.
When I do that, I get an ODBC error:
Error Type: sql.error Error Value: ('37000', -3517, '[Microsoft][ODBC Microsoft Access Driver] Characters found after end of SQL statement.')
Here's my very simple SQL statement, in the ZSQL method:
INSERT INTO tblUsers VALUES ( 5 );
INSERT INTO tblParents VALUES ( 5 );
The first statement by itself works fine.
Harlow Pinson Indepth Learning Email: hpinson@indepthl.com Web: http://www.indepthl.com Voice: 505-994-2135 FAX: 208-475-7678
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
+-------------------------------------------+ Garry Steedman mailto:gs@styrax.com Styrax Associates http://www.styrax.com/ "The Good Man has no shape." +-------------------------------------------+
Hi, For MS SQL you have to use GO between statements, might be same for MS Access Vid --- Garry Steedman <gs@styrax.com> wrote:
Harlow,
IIRC it depends on the ODBC/RDBMS implementations: check your docs for these and you should be able to work out if they support multiple sql statements...
g
On 18 Dec 2001, at 9:43, Harlow Pinson wrote:
From: "Harlow Pinson" <hpinson@indepthl.com> To: Vid Bijelic <zoops4@yahoo.com> Subject: Re: [Zope] Multiple SQL statements in a single ZSQL method? Send reply to: hpinson@indepthl.com Copies to: zope@zope.org Priority: normal Date sent: Tue, 18 Dec 2001 09:43:56 -0700
Just place ; at the end of sql statement.
When I do that, I get an ODBC error:
Error Type: sql.error Error Value: ('37000', -3517, '[Microsoft][ODBC Microsoft Access Driver] Characters found after end of SQL statement.')
Here's my very simple SQL statement, in the ZSQL method:
INSERT INTO tblUsers VALUES ( 5 );
INSERT INTO tblParents VALUES ( 5 );
The first statement by itself works fine.
Harlow Pinson Indepth Learning Email: hpinson@indepthl.com Web: http://www.indepthl.com Voice: 505-994-2135 FAX: 208-475-7678
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists -
http://lists.zope.org/mailman/listinfo/zope-announce
+-------------------------------------------+ Garry Steedman mailto:gs@styrax.com Styrax Associates http://www.styrax.com/
"The Good Man has no shape." +-------------------------------------------+
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists -
http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
__________________________________________________ Do You Yahoo!? Check out Yahoo! Shopping and Yahoo! Auctions for all of your unique holiday gifts! Buy at http://shopping.yahoo.com or bid at http://auctions.yahoo.com
you have to separate them with: <dtml-var sql_delimiter> phil Am Die, 2001-12-18 um 17.43 schrieb Harlow Pinson:
Just place ; at the end of sql statement.
When I do that, I get an ODBC error:
Error Type: sql.error Error Value: ('37000', -3517, '[Microsoft][ODBC Microsoft Access Driver] Characters found after end of SQL statement.')
Here's my very simple SQL statement, in the ZSQL method:
INSERT INTO tblUsers VALUES ( 5 );
INSERT INTO tblParents VALUES ( 5 );
The first statement by itself works fine.
Harlow Pinson Indepth Learning Email: hpinson@indepthl.com Web: http://www.indepthl.com Voice: 505-994-2135 FAX: 208-475-7678
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Thanks Phil! That did the trick. Just to sum it up for others. To add multiple sql statements to a ZSQL method calling a Microsoft Access database via the MS ODBC driver: 1. Create a ZSQL Method. 2. Configure the multi-select statement, for example, like this: INSERT INTO tblUsers (user_id) VALUES (<dtml-sqlvar user_id type=nb>); <dtml-var sql_delimiter> INSERT INTO tblParents (user_id) VALUES (<dtml-sqlvar user_id type=nb>); <dtml-var sql_delimiter> INSERT INTO tblChildren (user_id) VALUES (<dtml-sqlvar user_id type=nb>); <dtml-var sql_delimiter> Harlow Pinson Indepth Learning Email: hpinson@indepthl.com Web: http://www.indepthl.com Voice: 505-994-2135 FAX: 208-475-7678
participants (5)
-
Florent Guillaume -
Garry Steedman -
Harlow Pinson -
Philipp Auersperg -
Vid Bijelic