Commenting tables, column, procedures ++ in SQL Server

Unlike Oracle, PostgreSQL and others, you don't have specific table or column comments. Instead, SQL Server has so-called extended properties which are key-value pairs containing whatever information you want to put there. You can access these by using built-in procedures.

Example:
exec sp_addextendedproperty 'MS_Description', 'This column is awesome', 'schema', 'dbo', 'table', 'mytable', 'column', 'mycolumn';

This adds an extended property called MS_Description to the column dbo.table.mycolumn.

Note:

• MS_Description is the standardized name for a short description. Using this helps when auto generating documentation from your database.
• You can't add a property if it already exists. In that case, you must specifically update it. See examples for more info.
• If you don't use schemas in your database, then dbo is your current schema.

Examples:

Action	Command
Add description to a procedure	exec sp_addextendedproperty 'MS_Description', 'My super cool procedure', 'schema', 'dbo', 'procedure', 'my_proc';
Update existing property version on a table	exec sp_updateextendedproperty 'version', '2.0', 'schema', 'dbo', 'table', 'my_table';
Delete existing property version from a table	exec sp_deleteextendedproperty 'version', 'schema', 'dbo', 'table', 'my_table';
List all properties of a column	select * from fn_listextendedproperty(default, 'schema', 'dbo', 'table', 'my_table', 'column', 'my_column');
List the MS_Description property of a table	select * from fn_listextendedproperty('MS_Description', 'schema', 'dbo', 'table', 'my_table', null, null);

Database link between Oracle 9i and 11g

Creating a database link between Oracle 9i and 11g can surface as an unexpected problem. Either your connection times out, or you get an invalid username/password message when you try to connect. The remedies are easy:

1. Don't use a connection string in your CREATE DATABASE LINK statement; instead add it to your TNS file and reference it, otherwise you might just timeout when connecting. Why this is the case I have no idea.
2. Use uppercase in your 11g password or turn off case sensitivity. The latter can either be done at installation or by executing ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE.
   

Tested successfully on 9.2.0.8.0 (64 bit) against 11.2.0.1.0 (64 bit), using both SQL and PL/SQL.

Assigning restricted hotkeys (with the Win key) to whatever you like in Win 7/Vista

After installing Windows 7, I suddenly found myself unable to assign Win+Space to one of my most essential tools; Launchy. Thankfully, there's a registry fix:

HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\Advanced\DisabledHotkeys

It's a simple expandable string containing all characters for which default Win-hotkeys should be disabled (it may not already exist, in which case you'll have to create it). In my setup, it contains a single space, meaning that the system hotkey Win+Space should be unbound and ready for anyone else to use. For example, had it looked like this:

" a13"

..then Win+Space, Win+a, Win+1 and Win+3 would not be bound and made available for other use. Remember to restart you computer for the changes to apply.

Oracle 9 and boolean values

Oracle has come a long way since 9, but there are still a lot of businesses using it. If you're one of them, and also happen to enjoy PL/SQL, you should be aware of the dangers of using boolean values as function parameters.

Now, we all know that you can't use boolean in tables - it's strictly PL/SQL-only. However, the blur between what's considered PL/SQL and what's not might get you banging your head against the wall without realizing what's actually the problem. As is my preferred fashion, I'm going to give you the bullet points:

• Calling PL/SQL procedures and functions with boolean parameters within PL/SQL works without problems (of course)
• Calling PL/SQL procedures with boolean parameters using EXEC works fine
• Calling PL/SQL functions with boolean parameters using SELECT does not work
• Calling PL/SQL procedures and functions using JDBC prepared statements and trying to bind a variable to a boolean value does not work
• PL/SQL procedures and functions using boolean parameters with default values will work as long as you don't try to set them
• Trying to make an int2boolean(..) function for use with SELECT statements won't work, since to the parser it returns an unknown datatype

It's not supposed to be easy, is it? Usually, the solution is simply to either

1. Overload the function/procedure so that it accepts both boolean and int values (0/1 or whatever you like), or..
2. ..skip using boolean altogether

This code shows some of it:

create or replace function bool_me(p boolean default false) return int is
begin
  if p = false then
      return 0;
  elsif p = true then
      return 1;
  else
      --p is NULL
      raise_application_error(-20000, 'p is NULL!');
  end if;
end;
/

create or replace procedure bool_me2(p boolean default false) as
begin
  if p = false then
      dbms_output.put_line('p is false');
  elsif p = true then
      dbms_output.put_line('p is true');
  else
      --p is NULL
      dbms_output.put_line('p is NULL');
  end if;
end;
/

create or replace function return_true return boolean is
begin
  return true;
end;
/





SQL> set serveroutput on
SQL> exec bool_me2(true);
p is true


SQL> exec bool_me2;
p is false


SQL> select bool_me(true) from dual;
select bool_me(true) from dual
             *
FEIL på linje 1:
ORA-00904: "TRUE": invalid identifier


SQL> select bool_me from dual;

 BOOL_ME
----------
       0


SQL> select return_true from dual;
select return_true from dual
     *
FEIL på linje 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type

Network connections on Nokia S60

Do you have problems with network connections on Nokia S60 based phones? do you suspect that something is wrong with their implementation of HttpConnection? And perhaps SocketConnection too?

You're absolutely right. Forum Nokia won't tell you this (well, you can get some glimpses in for example KIJ001610 and KIJ000852), but you may be in a lot of trouble if you need to transfer large amounts of data.

I've done a lot of testing, and here's the conclusion:

• The data you send using a HttpConnection is retained in memory. The VM will say it's gone, but Symbian won't. Send enough data and you get an OutOfMemoryError. This is regardless of the requests' size.
• SocketConnection might be broken (see the knowledge base article mentioned above), but don't expect it to be fixed on newer models. This differs from device to device. N73, for example, works like a charm. N95 and 5800 do not.

So what do you do? Well, the only workaround is to

1. Use SocketConnection (although this only goes for S60, remember)
   
2. Make sure you keep the connection alive. Note that the server you're connecting to must also be configured for keepalive.
3. Also note that keeping idle connections alive requires a lot more memory on the server.

Every time you open a new SocketConnection, you go one step closer to doom. "Doom" may occur after 70-100 times; I haven't tested it that thoroughly and it probably varies a lot between devices.

Hope this was of any help!