Smokes your problems, coughs fresh air.

Tag: SQL

Cursor definition and looping with MS SQL

In MSSQL, I needed to loop over a bunch of orders and execute a stored procedure, except in one case. This does that:

declare @orderid int;
DECLARE @RC int
 
declare ordercursor cursor
FOR SELECT id FROM [ORDER];
 
open ordercursor
fetch next FROM ordercursor INTO @orderid
 
while @@fetch_status = 0
begin
  IF @orderid != 429
  begin
    EXECUTE @RC = [DeleteOrderById] @orderid  
  end
  
  fetch next FROM ordercursor INTO @orderid;
end
 
close ordercursor;
deallocate ordercursor;

Some new (upcoming) PostgreSQL features

These are some random new PostgreSQL features that I’m interested in:

  • The EXECUTE USING feature in PostgreSQL 8.4 will allow the use of more secure dynamic SQL in PL/PgSQL.

  • User defined exceptions in PostgreSQL 8.4 are a very exciting feature to me. In our unit tests we often check if an exception is properly raised. However, for exception that we raise ourselves, we’ve never been able to check which exception is raised.

    Another exciting possibility is that this will allow us to come up with a very clean validation scheme that produces exceptions that we’ll be able to use in the client web GUI.

  • WITH queries in PostgreSQL 8.4 will allow some very cool things, the coolest of which is something that I’ve wished for on more than one occasion: recursion.

  • The introduction of proper support for the SQL standard XML Type makes me wish that my favorite web hosting provider would support a real database besides their standard MySQL offering.

  • Enumerated Types will make some of my table definitions slightly clearer.

    -- This isn't too shabby (thanks to the CHECK constraint):
    CREATE TABLE persons (
    the_gender CHAR(1) CHECK (gender = 'm' OR gender = 'f')
    );
     
    -- But, I like this much better:
    CREATE TYPE gender AS ENUM ('male', 'female');
    CREATE TABLE persons (
    the_gender gender
    );

Extra kudos to the PostgreSQL development team for their accelerating pace! 😀

Purging a MySQL database without losing meta data

When I clone a production database to a development database, I always need to empty the development database first. I could never find an SQL command to completely purge a database, bur, recently, I learned something about MySQL that surprised me: MySQL doesn’t lose the user rights for a database when you drop it. So, purging a MySQL database (without losing any access data meta data about that DB) is as simple as issuing the following statements:

DROP DATABASE my_development_db;
CREATE DATABASE my_development_db;

Happy cloning! 🙂

© 2022 BigSmoke

Theme by Anders NorenUp ↑