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);
conSP.Open();
conSPParamValue.Open();

// 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;
cmdSPParamValue.Parameters.Add("@Procname",myReader.GetString(0));
cmdSPParamValue.Parameters.Add("@ProcParamName",myReader.GetString(1));
SqlParameter param = cmdSPParamValue.Parameters.Add
("@DefaultValue",SqlDbType.VarChar,100);
param.Direction = ParameterDirection.Output;

cmdSPParamValue.ExecuteNonQuery();

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


Create the following 2 stored procedures:


CREATE PROCEDURE _GetAllProcedures
AS

SET NOCOUNT ON

select sysobjects.name,syscolumns.name from sysobjects, syscolumns
where
sysobjects.xtype='P' and
sysobjects.id = syscolumns.id

RETURN
GO


and...



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

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

SELECT @VALUE

*****************************************************
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
begin
set @version = substring(@procname,@startPos +1,1)
set @procname = left(@procname,len(@procname)-2)
end
else
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
@SQLStr,
@ParmDefinition,
@p_name = @procname,
@ParamName = @ProcParamName,
@vers = @version,
@text_OUT =@text OUTPUT

--select @TEXT
select @startPos = PATINDEX( '%' + @ProcParamName +'%',@text)
if @startPos<>0
begin
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
begin
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))
end
ELSE
select @DefaultValue = 'NO DEFAULT SPECIFIED'
end
else
SET @DefaultValue = 'INVALID PARAM NAME'

set nocount off
return
GO

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 http://openjms.sourceforge.net
    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.INITIAL_CONTEXT_FACTORY,
"org.exolab.jms.jndi.InitialContextFactory");
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 jndi.properties 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);
mq.Send(theMessage);
Console.WriteLine("Sent Message: {0}", theMessage.Body);
}

java.lang.System.exit(0);


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"
http://www.depechemode.com/tour/dates.html

Israel - Castel Tickets: http://www.ticketnet.co.il/depechemode
phone: +972-3-604-5000