SODEVE

Currently I am reading a book about MS SQL Stored Procedure. I need to read the book as an anticipation that i will be accepted to an Insurance Company. Just to make sure my brain is not rusty :-P. I believe in-order to understand this guide, you need to know the basic Data Creation and Data Modification commands in T-SQL. I will use this posting to share with you what i *roughly* get from the book

1. What is Stored Procedure?

Stored procedure is a collection of queries/commands encapsulated in a function stored in the database server. The function may receive parameters which later can be used as the paremeters of the queries/commands. More definition.

2. Why Stored Procedure (SP) is useful?

Because SP will encapsulated your queries/commands into a parameterized queries/data definitions/data modifications. Thus you can standardize the exchange of information between the database server and its users. At the same time you will reduce the debugging-time on Application development since all the business logic are stored in the database. SP also able to help you maintaining security by classifying which users are permitted and which users prohibited to call the Stored Procedures.

3. Why we need Stored Procedure (SP) ?

Because we want to achieve result mentioned on number 2. We can achieve this by replacing the Stored Procedure as a library of functions stored outside the database server. But it will hinder the flexibility of type of Application that using the same business logic. For example, the current business application is using Web-based interface; If we want to create a Desktop-based/Smart Client Application we will be using more time to translate the business logic into different programming language/framework. We also will have major headache if we need to upgrade the Application but not the Business Logic.

4. Why we DON’T need Stored Procedure (SP) ?

Because SP is less powerful compared to the programming language that we use on the Web Application/Desktop Application/Smart Client. It also possible that we will locked into only one RDBMS vendor. Thus reducing our flexibility in managing IT budget.

5. When a Stored Procedure is suited Us best?

When you are confident that the current RDBMS will be deployed in your organization long enough compared to the time to develop the stored procedure. Extending the application into multiple platform/programming language is mentioned in your Application’s Development Roadmap.

Hardono Arifanto :: Mar.26.2007 :: Database :: No Comments »

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 »

Let say you want to create a on-the-fly table which fetch the list of users with certain properties. The problem with the users is that, some of them is without any properties. Instead of displaying blank, you want to display ‘—-’. Since you want to do this on the fly, not by modifying the table. These infos will come handy.

Simply using UNION will help you to achieve above problem. Meta-SQL :

In IIS, this will throw you Collation error if you Users table is encoded in UTF8, the Properties value of ‘—-’ will be encoded in Latin. Thus, it will throws you collation error.

In order to convert the ‘—-’ encoded as UTF8, you must use _utf8 as prefix of your string. Eq: _utf8′—-’ instead of only ‘—-’

Hardono Arifanto :: Jan.23.2006 :: Database :: No Comments »

Today i’m doing some programming to help Budi catering JSML’s requests. At one point, i need to have an aggregate function for varchar data type. I immediately remember ‘GROUP_CONCAT’, but hey … thats MySQL. Looking through the MS SQL Book Online, couldn’t find any reference about it.

Googling for 5 minutes, i found what i want …

These are the snippets:

create table table1([id] int identity,
names varchar (50),
groupname varchar (50))

insert into table1(Names, GroupName)

select 'name1', 'group1'union
select 'name2', 'group2'union
select 'name3', 'group1'

create function concatname(@GroupName varchar(50))
Returns varchar(250)
as
BEGIN
Declare @String varchar(250)

Select @String = Coalesce(@String,'') + Names + ','
from table1
where groupName = @GroupName

set @String = left(@String, len(@String) -1)
Return @String
END

After trial and error for a couple times, i come up with my
own solution to the problem:

create function SCROverallStatus(@thelog_uid int)
returns varchar(50)
as
begin
 if (select count(*) from LMB_SML_PROC_SCR_APPROVAL where approval_level=1 and status='Approved' and log_uid=@thelog_uid)>=2
 begin
  return 'Approved'
 end

 if (select count(*) from LMB_SML_PROC_SCR_APPROVAL where approval_level=1 and status='Rejected' and log_uid=@thelog_uid)>=1
 begin
  return 'Rejected'
 end

 return 'Pending'
end

Hardono Arifanto :: Jan.10.2006 :: Database :: No Comments »