SODEVE

As I promised before, I will put the output of the programs that I mention in the previous post.

As input I will use the following SQL string (which I have verified that it is a valid SQL statement).

 ALTER PROCEDURE [dbo].[aspnet_Setup_RemoveAllRoleMembers] @name   sysname AS BEGIN     CREATE TABLE #aspnet_RoleMembers     (         Group_name      sysname,         Group_id        smallint,         Users_in_group  sysname,        User_id         smallint     );     INSERT INTO #aspnet_RoleMembers     EXEC sp_helpuser @name;     DECLARE @user_id smallint;    DECLARE @cmd nvarchar(500);    DECLARE c1 cursor FORWARD_ONLY FOR         SELECT User_id FROM #aspnet_RoleMembers;    OPEN c1;    FETCH c1 INTO @user_id;    WHILE (@@fetch_status = 0)    BEGIN        SET @cmd = 'EXEC sp_droprolemember ' + '''' + @name + ''', ''' + USER_NAME(@user_id) + '''';        EXEC (@cmd);        FETCH c1 INTO @user_id      END;     CLOSE c1;    DEALLOCATE c1  END

The output of the program are as follows:

1. SQLinForm
   

   ALTER
   PROCEDURE [dbo].[aspnet_Setup_RemoveAllRoleMembers] @name sysname
   AS
           BEGIN
                   CREATE TABLE #aspnet_RoleMembers
                                   (
                                   Group_name sysname    ,
                                   Group_id smallint     ,
                                   Users_in_group sysname,
                                   User_id smallint
                                   );
                   INSERT INTO #aspnet_RoleMembers EXEC sp_helpuser @name;
                   DECLARE @user_id smallint;
                   DECLARE @cmd nvarchar(500);
                   DECLARE c1
                   cursor FORWARD_ONLY FOR
                           SELECT User_id FROM #aspnet_RoleMembers;
                           OPEN c1;
                           FETCH c1 INTO @user_id;
                   WHILE (@@fetch_status = 0)
                   BEGIN
                           SET @cmd =  'EXEC sp_droprolemember ' + '''' + @name + ''', ''' + USER_NAME(@user_id) + '''';
                           EXEC (@cmd);
                           FETCH c1 INTO @user_id
                   END;
                   CLOSE c1;
                   DEALLOCATE c1
           END
   

   
   
   Initially I thought there was missing ‘END’, but I found out that because I choose ‘Any SQL’ instead of ‘SQL Server’. Very impressive!!!

2. SQL Online Formatter
   

   ALTER PROCEDURE [DBO].[ASPNET_SETUP_REMOVEALLROLEMEMBERS]
                  @name SYSNAME
   AS
     BEGIN
       CREATE TABLE #ASPNET_ROLEMEMBERS (
         GROUP_NAME     SYSNAME,
         GROUP_ID       SMALLINT,
         USERS_IN_GROUP SYSNAME,
         USER_ID        SMALLINT);
   
       INSERT INTO #ASPNET_ROLEMEMBERS
       EXEC SP_HELPUSER
          @name;
   
       DECLARE  @user_id SMALLINT;
   
       DECLARE  @cmd NVARCHAR(500);
   
       DECLARE C1 CURSOR FORWARD_ONLY FOR
       SELECT USER_ID
       FROM   #ASPNET_ROLEMEMBERS;
   
       OPEN C1;
   
       FETCH  C1
       INTO @user_id;
   
       WHILE (@@FETCH_STATUS = 0)
         BEGIN
           SET @cmd = 'EXEC sp_droprolemember ' + '''' + @name + ''', ''' + USER_NAME(@user_id) + '''';
   
           EXEC( @cmd);
   
           FETCH  C1
           INTO @user_id
         END;
   
       CLOSE C1;
   
       DEALLOCATE C1
     END
   

   As you see, it also performs as good as SQLinForm

3. SQL Review
   

   ALTER PROCEDURE [dbo].[aspnet_Setup_RemoveAllRoleMembers] @name sysname AS
   BEGIN
   
   CREATE TABLE #aspnet_RoleMembers (
         Group_name sysname,
         Group_id smallint,
         Users_in_group sysname,
         User_id smallint
      );
   
   INSERT INTO #aspnet_RoleMembers
   EXEC sp_helpuser @name;
   
   DECLARE
      @user_id smallint;
   
   DECLARE
      @cmd nvarchar(500);
   
   DECLARE
      c1
   CURSOR FORWARD_ONLY
   FOR
   SELECT
      User_id
   FROM
      #aspnet_RoleMembers;
      OPEN c1;
      FETCH c1 INTO @user_id;
      WHILE (@@fetch_status = 0)
   BEGIN
   
   SET @cmd = 'EXEC sp_droprolemember ' + '''' + @name + ''', ''' + USER_NAME(@user_id) + '''';
   
   EXEC (@cmd);
      FETCH c1 INTO @user_id
   END;
   CLOSE c1;
   DEALLOCATE c1
   END
   

   
   Unfortunately there is no option to indent text between BEGIN and END. But the plus point is you can run it

4. Pl/sql tidy
   Hmm.. doesn’t seem to work. Is it because it only parse PL/SQL? (I will investigate further when I really have nothing to do :P)

As for the LEX script, I’m still trying to get it work on C# using CsLEX, so it would take sometime before I post the result here.

Hardono Arifanto :: Aug.30.2007 :: Database, Software :: 1 Comment »

SQL SMO (SQL Management Object) is a collection of assemblies that shipped together with SQL 2005. This collection of assemblies is all that you need to control your SQL Server. Be it SQL 2005, or SQL 2000, you can control/manipulate it programmatically using SQL SMO.

Since SQL SMO gave you the ability to treat the database elements such as Table, Database, Stored Procedure, and Trigger as an object, interacting with them should be relatively easy and practical. Additionally, you might want to check out this list of things that make SQL SMO exciting.

OK, lets get our hand dirty with the project. The first step would be importing the references into your project. If you use Visual Studio 2005, look for Microsoft.SqlServer.Smo in the list of .NET assembly when you open ‘Add References’ window.
If you already have MS SQL 2005 installed, you can find the DLLs in the SDK\Assemblies folder of your MS SQL 2005 installation folder.
If you don’t have MS SQL 2005 installed in your computer, you might want to download the Express edition HERE.

Next, you could use the following code as reference.

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System;
using System.Configuration;
using System.Data.SqlClient;

namespace Test
{
    class Program
    {
        static void Main(string[] args)
        {
            //Assuming that you include a Configuration file to your
            //project and set the key 'DataSource'
            //as the connection string
            SqlConnection conn = new SqlConnection(
                      ConfigurationManager.AppSettings["DataSource"]
            );
           //Instantiate the connection to the server
	   ServerConnection dbConn = new ServerConnection(conn);
	   //Instantiate the Database Server Object
	  Server dbServer = new Server(dbConn);			

          //Traverse the Database Objects
          foreach (Database db in dbServer.Databases)
          {
              if (db.IsAccessible)
              {
                  //traverse the Stored Procedure Objects
                  foreach (StoredProcedure sp in db.StoredProcedures)
                  {
                      foreach (string str in sp.Script())
                          Console.WriteLine(str);
                  }
                  //Traverse the Table Objects
                  foreach (Table tb in db.Tables)
                  {
                      foreach (string str in tb.Script())
                          Console.WriteLine(str);
                  }
                  //Traverse the View Objects
                  foreach (View vw in db.Views)
                  {
                      foreach (string str in vw.Script())
                          Console.WriteLine(str);
                  }
              }
          }
      }
  }
}

Make sure in your app.config you have the DataSource key. You can follow this template for the configuration file:

Have fun!

Hardono Arifanto :: Jul.17.2007 :: Database, .NET :: 1 Comment »

Consider you have a stored procedure that perform a SELECT operation for an object which is characterized by the ‘Start Date’ and ‘End Date’. Let’s assume we have the following TABLE:

Table Name: Events

Columns:

EventID int

EventName Varchar(64)

EventDesc Varchar(512)

EventStartDate DateTime

EventEndDate DateTime

And let’s assume that we have the following stored procedure:

CREATE PROCEDURE [dbo].[ListFilteredEvents]
	-- Add the parameters for the stored procedure here
	@StartDate datetime,
	@EndDate datetime
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

        -- Insert statements for procedure here
	SELECT
		Events.EventID,
		Events.EventName,
		Events.EventDesc,
		Events.EventStartDate,
		Events.EventEndDate
	FROM
		Events
	WHERE
		Events.EventStartDate>= @StartDate AND
                Events.EventEndDate<= @EndDate
	ORDER BY
		Events.EventStartDate
END

Now what will happen if somehow the SP caller will pass blank value as the StartDate and/or EndDate? Error (Exception will be thrown) of course. If we have access to the source code of the application that execute this stored procedure, we could easily error-prevention at the application level. But if our access is only limited to database level, we could do the following:

CREATE PROCEDURE [dbo].[ListFilteredEvents]
	-- Add the parameters for the stored procedure here
	@StartDate datetime,
	@EndDate datetime
AS
BEGIN
   IF @StartDate IS NULL
   BEGIN
      -- Set it with the smallest value of DateTime data type
      SET @StartDate=CAST('1753-01-01' as DATETIME)
   END

   IF @EndDate IS NULL
   BEGIN
      -- Set it with the biggest value of DateTime data type
      SET @EndDate=CAST('9999-12-31' as DATETIME)
   END

   BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

        -- Insert statements for procedure here
	SELECT
		Events.EventID,
		Events.EventName,
		Events.EventDesc,
		Events.EventStartDate,
		Events.EventEndDate
	FROM
		Events
	WHERE
		Events.EventStartDate>= @StartDate AND
                Events.EventEndDate<= @EndDate
	ORDER BY
		Events.EventStartDate
   END
END

This way we could prevent Error/Exception being thrown whenever a blank value is passed into the stored procedure.

Important Update: Saturday, 21 April 2007 13.43 PM — Thanks for Bro MCA for pointing out a better solution =)

CREATE PROCEDURE [dbo].[ListFilteredEvents]
	-- Add the parameters for the stored procedure here
	-- Set it with the smallest value of DateTime data type
        @StartDate DateTime='1753-01-01',
	-- Set it with the biggest value of DateTime data type
        @EndDate DateTime='9999-12-31'
AS
BEGIN
   -- SET NOCOUNT ON added to prevent extra result sets from
   -- interfering with SELECT statements.
   SET NOCOUNT ON;

   -- Insert statements for procedure here
   SELECT
	Events.EventID,
	Events.EventName,
	Events.EventDesc,
	Events.EventStartDate,
	Events.EventEndDate
   FROM
	Events
   WHERE
	Events.EventStartDate>= @StartDate AND
        Events.EventEndDate<= @EndDate
    ORDER BY
    Events.EventStartDate
END

Hardono Arifanto :: Apr.20.2007 :: Database :: 3 Comments »

(c) Mr Dew

When I’m travelling on Singapore Mass Rapid Transit (SMRT), I often make myself busy with my HP RX3715. Especially when the MRT is not crowded and I’m lucky enough to get a seat. The comfort condition is really supporting your creative mind, so better not waste it. Most of the time, I will open up Pocket Word, and jot down whatever ideas running in my head. Occasionally, I will revisit my old ideas and amend if not improve it.

(c) superciliousness

I have been had this idea of creating a mini Bus Guide in Street Directory-style in my HP RX3715 for quite sometimes. Although I yet to have clear way on the implementation, I’d like to share the idea with you all hoping for your feedbacks.

ALAS, apparently such guide is already created. But it is a static document. Anyway, I will carry on with this project, hopefully I will learn something about Embedded System utilising J2ME/.NET Compact Framework/C++ from this.

So far my idea is represented in T-SQL syntaxes.

Any Suggestions?

Hardono Arifanto :: Apr.07.2007 :: Database, Programming :: 1 Comment »

So I am now the main person responsible for LMS development for Star Horizon. And yeah, i’m under their cheque-book now

After modifying a couple of Datagrids so that they are sortable and page-able, I’m now stuck with the number of multiple connections connected to the MySQL server.

Our System consists of MyODBC, .NET Framework 1.1, IIS. 6 and MySQL Database server.

Today i run some dummy test, basically i used two computers to login to LMS using different username. I was playing around with the datagrids to see their effect to MyODBC. I was continually clicking the sorting button and changing the page number of the datagrids.

Maybe after only 5 iterations with 2 users, i generated almost 100 connections between MyODBC to MySQL. (Check the screenshot)

At the moment i dont have any clear picture what will happen if let say, 1000 users login at the same time. I dare to say its gonna be a darn gloomy day… hehe..

So what i can do now is just increasing the number of concurrent connection allowed to the MySQL server.

So i increased the number of connections from 800 to 20000.

Hmm.. let me get some volunteers or tools to test it.

Hardono Arifanto :: Apr.05.2006 :: Database :: No Comments »