Sunday, December 25, 2005

Get default value of stored procedure parameter

Get the default value of a stored procedure parameter.
Well, SQL Server, does not store the default values of a stored procedure parameters in its system tables, they are merely evaluated by the text of the procedure at runtime. Boo- Hoo-.

So, taking an SP (_GetParamDefault) created by Eva Zadoyen back in 2002, adding another one of my own and some C# code and "Walla".

Some small information on the process:
* _GetAllProcedures - get all parameters of all stored procedures.
The resultset which returns contains:SP Name, SP Column

* _GetParamDefault - get default values for a stored procedure parameter.
The SP gets:SP Name, SP Column and an OUTPUT parameter for the default value.

* Now, lets wrap it all with some C# glue:

// open two connections
string ConnectionString = "Data Source=localhost;" +
"Initial Catalog=Northwind;user id=sa";
SqlConnection conSP = new SqlConnection(ConnectionString);
SqlConnection conSPParamValue = new SqlConnection(ConnectionString);

// get all stored procedures and parameters
SqlCommand cmdSP = new SqlCommand("_GetAllProcedures", conSP);
cmdSP.CommandType = CommandType.StoredProcedure;

// loop on all stored procecdures and parameters
SqlDataReader myReader = cmdSP.ExecuteReader();
while (myReader.Read())
if (myReader.GetString(0).ToLower().StartsWith("dt_") != true)

string ParamDefaultValue = "";

SqlCommand cmdSPParamValue =
new SqlCommand("_GetParamDefault", conSPParamValue);
cmdSPParamValue.CommandType = CommandType.StoredProcedure;
SqlParameter param = cmdSPParamValue.Parameters.Add
param.Direction = ParameterDirection.Output;


ParamDefaultValue = param.Value.ToString();
if (ParamDefaultValue != "NO DEFAULT SPECIFIED")
Console.WriteLine("{0}, {1}, {2}",
myReader.GetString(0), myReader.GetString(1), ParamDefaultValue);

Create the following 2 stored procedures:



select, from sysobjects, syscolumns
sysobjects.xtype='P' and =



CREATE proc _GetParamDefault
@Procname varchar(50),
@ProcParamName varchar(50),
@DefaultValue varchar(100) OUTPUT
This procedure will return DEFAULT value for
the parameter in the stored procedure.

declare @Value varchar(30)
exec _GetParamDefault 'random_password','@password_type',@value OUTPUT


Created by Eva Zadoyen

set nocount on
declare @sqlstr nvarchar(4000),
@obj_id int,
@version int,
@text varchar(8000),
@startPos int,
@endPos int,
@ParmDefinition NVARCHAR(500)
select @procName = rtrim(ltrim(@procname))
set @startPos= charindex(';',@Procname)

if @startPos<>0
set @version = substring(@procname,@startPos +1,1)
set @procname = left(@procname,len(@procname)-2)
set @version = 1

SET @sqlstr =N'SELECT @text_OUT = (SELECT text FROM syscomments
WHERE id = object_id(@p_name) and colid=1 and number = @vers)'
SET @ParmDefinition = N'@p_name varchar(50),
@ParamName varchar (50),
@vers int,
@text_OUT varchar(4000) OUTPUT'

EXEC sp_executesql
@p_name = @procname,
@ParamName = @ProcParamName,
@vers = @version,
@text_OUT =@text OUTPUT

--select @TEXT
select @startPos = PATINDEX( '%' + @ProcParamName +'%',@text)
if @startPos<>0
select @text = RIGHT ( @text, len(@text)-(@startPos +1))
select @endPos= CHARINDEX(char(10),@text) -- find the end of a line
select @text = LEFT(@text,@endPos-1)
-- check if there is a default assigned and parse the value to the output
select @startPos= PATINDEX('%=%',@text)
if @startPos <>0
select @DefaultValue = ltrim(rtrim(right(@text,len(@text)-(@startPos))))
select @endPos= CHARINDEX('--',@DefaultValue)
if @endPos <> 0
select @DefaultValue = rtrim(left(@DefaultValue,@endPos-1))

select @endPos= CHARINDEX(',',@DefaultValue)
if @endPos <> 0
select @DefaultValue = rtrim(left(@DefaultValue,@endPos-1))
select @DefaultValue = 'NO DEFAULT SPECIFIED'

set nocount off

Wednesday, December 21, 2005

System.Messaging using OpenJMS

Source code of this sample
The situation:

  • You are working on a port project from .NET to Java using Grasshopper.
  • Your application utilizes System.Messaging
  • Grasshopper 1.7 does not support System.Messgaing.

Your reaction:

  • Scratch your head and raise the eyebrows.
  • Whine a bit.
  • Get down with some mixed .NET and Java code.

The ingredients:

  • 1 Grasshopper version 1.7
  • 1 Tomcat 5.5
  • 1 OpenJMS server
    OpenJMS is the Message Queue MOM for Tomcat.

In this example we will demonstrate a System.Messaging namespace stub (stub – an empty or a mockup class) which is filled with JMS (Java Message Service ) java implementation for Message and MessageQueue classes and for the methods MessageQueue.Send and MessageQueue.Receive.

Let's start with a System.Messaging stub file which contains all our application needs from System.Messaging namespace.

Now, just before we start writing the Java JMS code, we should add some Java References, taken from the OpenJMS lib directory.
Add Java references to the following jars
* commons-codec-1.3.jar
* commons-logging-1.0.4.jar
* concurrent-1.3.4.jar
* jms-1.1.jar
* jndi-1.2.1.jar
* openjms-0.7.7-alpha-1.jar
* openjms-common-0.7.7-alpha-1.jar
* openjms-net-0.7.7-alpha-1.jar
* spice-jndikit-1.1.jar

Add a using statements to JMS namespaces

using javax.naming;
using javax.jms;

Fill up the MessageQueue class with JMS functionality.
MessageQueue class constructor is using a helper class MessageQueueProperties which behave as a small pool for JMS Context objects.
The .NET message queue path is get parsed at MessageQueuePath class.

In general, JMS applications begin by looking up a ConnectionFactory instance from JNDI (the Java Naming and Directory Interface), and then using this to create Connection and then Session instances.

java.util.Hashtable properties = new java.util.Hashtable();
properties.put(Context__Finals.PROVIDER_URL, "tcp://localhost:3035");
properties.put(Context__Finals.SECURITY_PRINCIPAL, "admin");
properties.put(Context__Finals.SECURITY_CREDENTIALS, "openjms");

If you wish to avoid hardcoding the JNDI properties,
they can be specified in an application "Embedded Resource" file named located in the application's classpath or at WEB-INF\classes.

Here is a code sample which Sends messages to the queue.
"queue1" is one of the pre-configured queues of OpenJMS.

string queueServer = "localhost";
string queueName = "queue1";

int N = 10;
if (args != null && args.Length > 0)
N = Convert.ToInt32(args[0]);
for (int i = 0; i < N; i++)
Message theMessage = new Message();
theMessage.Body = "test value " + i;
MessageQueue mq = new MessageQueue("FormatName:DIRECT=OS:" +
queueServer + "\\" + queueName);
Console.WriteLine("Sent Message: {0}", theMessage.Body);


Here is a code sample which Receives messages from the queue.

string queueServer = "localhost";
string queueName = "queue1";

int N = 10;
if (args != null && args.Length > 0)
N = Convert.ToInt32(args[0]);
for (int i = 0; i < N; i++)
MessageQueue mq = new MessageQueue("FormatName:DIRECT=OS:" +
queueServer + "\\" + queueName);
Message theMessage = mq.Receive();
Console.WriteLine("Received message: {0}", theMessage.Body);

enjoy and deploy.

also thanks to Svetlana Zholkovsky.

Thursday, December 08, 2005

Reach Out Touch Faith - Depeche Mode comming to Israel

Just wanted to brag that I got 4 tickets for the first Depeche Mode concert in Israel, Thursday, August 3rd, 2006.

Depeche Mode "Touring The Angel 2005/2006"

Israel - Castel Tickets:
phone: +972-3-604-5000