Thread: RE: Using SELECT as DDL/DML statement is wrong (was RE: rei nitialize a sequence?)
RE: Using SELECT as DDL/DML statement is wrong (was RE: rei nitialize a sequence?)
From
Michael Ansley
Date:
<p><font size="2">In fact, I would have thought that this could be done using</font><p><font size="2">ALTER SEQUENCE sequence_nameSET property = value</font><p><font size="2">But, altering the database in a procedure called from a selectis a design decision, and if somebody wants to do it, well, it's their problem. There may (on very few occasions,one would hope) actually be some good reasons to do this.</font><p><font size="2">Cheers...</font><br /><p><fontsize="2">MikeA</font><br /><br /><p><font size="2">-----Original Message-----</font><br /><font size="2">From:Edmar Wiggers [<a href="mailto:edmar@brasmap.com">mailto:edmar@brasmap.com</a>]</font><br /><font size="2">Sent:05 December 2000 16:50</font><br /><font size="2">To: pgsql-sql@postgresql.org</font><br /><font size="2">Subject:Using SELECT as DDL/DML statement is wrong (was RE: [SQL]</font><br /><font size="2">reinitialize a sequence?)</font><br/><p><font size="2">If and when stored procedures are supported, there should be some way to</font><br/><font size="2">prevent functions called in a SELECT statement to modify the database</font><br /><font size="2">(create,insert, etc.).</font><p><font size="2">It is confusing (and wrong IMHO) to use statements like</font><p><fontsize="2">SELECT setval('tablename_serfield_seq',max(serfield)) FROM tablename;</font><br /><font size="2">(whichis used to reset a sequence)</font><p><font size="2">That should be done with</font><p><font size="2">EXECUTEprocedure(tablename_name,sequence_name);</font><br /><font size="2">(not sure if execute is the right keyword)</font><p><fontsize="2">Yours sincerely,</font><p><font size="2">Edmar Wiggers</font><br /><font size="2">BRASMAPInformation Systems</font><br /><font size="2">+55 48 9960 2752</font><code><font size="3"><br /><br /> **********************************************************************<br/> This email and any files transmitted with itare confidential and<br /> intended solely for the use of the individual or entity to whom they<br /> are addressed. Ifyou have received this email in error please notify<br /> Nick West - Global Infrastructure Manager.<br /><br /> This footnotealso confirms that this email message has been swept by<br /> MIMEsweeper for the presence of computer viruses.<br/><br /> www.mimesweeper.com<br /> **********************************************************************<br /></font></code>
RE: Using SELECT as DDL/DML statement is wrong (was RE: reinitialize a sequence?)
From
"Edmar Wiggers"
Date:
> But, altering the database in a procedure > called from a select is a design decision, > and if somebody wants to do it, well, it's > their problem. There may (on very few > occasions, one would hope) actually be some > good reasons to do this. Ok, it's their problem. But, as a DBA, I would like to have stored procedures. Having that, I would also like a way to prevent any application development that uses SELECT statements to alter the database. By definition, that's not what SELECT is for (and so it is bad practice to use it that way). Currently however, that's the only whay to do it (use something similar to stored procs). And, true, that's much better than not doing it at all. Yours sincerely, Edmar Wiggers BRASMAP Information Systems +55 48 9960 2752
Hi, can anybody tell me where I can get a free JDBC driver for Postgresql that implements CallableStatement? The driver in the distribution I have is PostgreSQL 7.0.2. Thanks in advance, Shane