Dalam suatu database aplikasi yang kita buat terkadang ada beberapa kolom yang perlu diubah. Perubahan kolom tersebut bisa berupa perubahan tipe data maupun lebar kolom atau ukuran kolom.
Syntax untuk merubah kolom dalam Sybase yaitu :
ALTER TABLE {NAMATABEL} MODIFY {NAMAKOLOM} {TIPEDATA} {UKURANKOLOM}
Contoh :
ALTER TABLE MASTER.CIF MODIFY nama_ibu VARCHAR(15) VARCHAR(60)
Tuesday, May 13, 2014
Merubah Nilai Default Tabel database Sybase
Untuk merubah nilai default suatu kolom tabel dalam database sybase tidak dapat menggunakan fungsi ALTER MODIFY, tetapi harus menggunakan fungsi ALTER REPLACE.
Syntaxnya yaitu :
ALTER TABLE {NAMATABEL} REPLACE {NAMAKOLOM} DEFAULT {NILAIDEFAULT}
Contoh :
ALTER TABLE MASTER.CIF REPLACE AVG_TRANS DEFAULT 0
Syntaxnya yaitu :
ALTER TABLE {NAMATABEL} REPLACE {NAMAKOLOM} DEFAULT {NILAIDEFAULT}
Contoh :
ALTER TABLE MASTER.CIF REPLACE AVG_TRANS DEFAULT 0
Tuesday, October 1, 2013
Get multiple result rows from sybase store procedure
Sometime we need get multiple result rows from some query in store procedure.
For security purposes, I had to change some variable, column
and table names, but here is my stored procedure:
and table names, but here is my stored procedure:
Create Proc dbo.sp_GetMultiple (
@parm_keyField varchar(100) = null,
@parm_dbMyName char(30) = null Output,
@parm_dbState char(2) = null Output,
@parm_dbUniqueId varchar(16) = null Output,
@parm_dbField2 varchar(8) = null Output,
@parm_dbField3 varchar(8) = null Output,
@parm_dbField4 varchar(16) = null Output,
@parm_dbField5 varchar(8) = null Output,
@parm_dbPeriod varchar(8) = null Output
)
As
Begin
/**********************************************************
*Procedure Name: sp_GetMultiple
*********************************************************/
/*** declare variables ***/
declare @er integer
declare @varState varchar(2)
declare @varUniqueId varchar(16)
declare @varField2 varchar(8)
declare @varField3 varchar(8)
declare @varField4 varchar(8)
declare @varField5 varchar(16)
declare @varPeriod varchar(8)
/*** retrieve data ***/
declare resultList cursor for
SELECT LEFT(myName, 30) AS myName, State, column2, column3,
column4, column5, Period, UniqueId
FROM Table1 T1
LEFT OUTER JOIN Table2 T2 ON T1.CatId = T2.CatIdFK
WHERE T2.keyField = @parm_keyField
ORDER BY 1, 2
open resultList
if @@sqlstatus != 0 return
fetch resultList
into @parm_dbMyName, @varState, @varField2, @varField3,
@varField4, @varField5, @varPeriod, @varUniqueId
/* if cursor result set is not empty, then process each
row of information */
while (@@sqlstatus = 0)
begin
/* decrypt the data */
exec dbo.sp_DecryptVar @varState, @parm_dbState
output
exec dbo.sp_DecryptVar @varUniqueId, @parm_dbUniqueId
output
exec dbo.sp_DecryptVar @varField2, @parm_dbField2
output
exec dbo.sp_DecryptVar @varField3, @parm_dbField5
output
exec dbo.sp_DecryptVar @varField4, @parm_dbField3
output
exec dbo.sp_DecryptVar @varField5, @parm_dbField4
output
exec dbo.sp_DecryptVar @varPeriod, @parm_dbPeriod
output
/*** get next record from cursor ***/
fetch resultList into @parm_dbMyName, @varState,
@varField2, @varField3, @varField4, @varField5, @varPeriod,
@varUniqueId
end
close resultList
return
End
than...
How do you return data to the client ?
And how does the client proces the data ?
If you have multiple selects in a procedure, you get multiple
result sets sent to the client, so the client should process more
than one result set. A way to avoid this is to insert the
data in a temp table first and then select from the temp table.
@parm_keyField varchar(100) = null,
@parm_dbMyName char(30) = null Output,
@parm_dbState char(2) = null Output,
@parm_dbUniqueId varchar(16) = null Output,
@parm_dbField2 varchar(8) = null Output,
@parm_dbField3 varchar(8) = null Output,
@parm_dbField4 varchar(16) = null Output,
@parm_dbField5 varchar(8) = null Output,
@parm_dbPeriod varchar(8) = null Output
)
As
Begin
/**********************************************************
*Procedure Name: sp_GetMultiple
*********************************************************/
/*** declare variables ***/
declare @er integer
declare @varState varchar(2)
declare @varUniqueId varchar(16)
declare @varField2 varchar(8)
declare @varField3 varchar(8)
declare @varField4 varchar(8)
declare @varField5 varchar(16)
declare @varPeriod varchar(8)
/*** retrieve data ***/
declare resultList cursor for
SELECT LEFT(myName, 30) AS myName, State, column2, column3,
column4, column5, Period, UniqueId
FROM Table1 T1
LEFT OUTER JOIN Table2 T2 ON T1.CatId = T2.CatIdFK
WHERE T2.keyField = @parm_keyField
ORDER BY 1, 2
open resultList
if @@sqlstatus != 0 return
fetch resultList
into @parm_dbMyName, @varState, @varField2, @varField3,
@varField4, @varField5, @varPeriod, @varUniqueId
/* if cursor result set is not empty, then process each
row of information */
while (@@sqlstatus = 0)
begin
/* decrypt the data */
exec dbo.sp_DecryptVar @varState, @parm_dbState
output
exec dbo.sp_DecryptVar @varUniqueId, @parm_dbUniqueId
output
exec dbo.sp_DecryptVar @varField2, @parm_dbField2
output
exec dbo.sp_DecryptVar @varField3, @parm_dbField5
output
exec dbo.sp_DecryptVar @varField4, @parm_dbField3
output
exec dbo.sp_DecryptVar @varField5, @parm_dbField4
output
exec dbo.sp_DecryptVar @varPeriod, @parm_dbPeriod
output
/*** get next record from cursor ***/
fetch resultList into @parm_dbMyName, @varState,
@varField2, @varField3, @varField4, @varField5, @varPeriod,
@varUniqueId
end
close resultList
return
End
than...
How do you return data to the client ?
And how does the client proces the data ?
If you have multiple selects in a procedure, you get multiple
result sets sent to the client, so the client should process more
than one result set. A way to avoid this is to insert the
data in a temp table first and then select from the temp table.
Tuesday, July 2, 2013
You and I – Scorpions
I lose control because of you, babe
I lose control when you look at me like this
There’s something in your eyes
That is saying tonight
I’m not a child anymore
Life has opened the door
To a new exciting life
I lose control whem I’m close to you, babe
I lose control don’t look at me like this
There’s something in your eyes
Is this love at first sight?
Like a flower that grows
Life just wants you to know
All the secrets of life
It’s all written down in your lifelines
It’s written down inside your heart
You and I just have a dream
To find our love a place, where we can hide away
You and I, we’re just made
To love each other now, forever and a day, yeah
I lose control because of you, babe
I lose control don’t look at me like this
There’s something in your eyes
That is saying tonight
I’m so curious for more
Just like never before
In my innocent life
It’s all written down in your lifelines, yeah
It’s written down inside your heart
You and I just have a dream
To find our love a place, where we can hide away
You and I, we’re just made
To love each other now, forever and a day, yeah
The time stands still
When the days of innocence
Are falling for the night
I love you, girl, I always will
I swear, I’m there for you
Till the day I die
You and I just have a dream
To find our love a place, where we can hide away
You and I, we’re just made
To love each other now, forever and a day, yeah
I lose control when you look at me like this
There’s something in your eyes
That is saying tonight
I’m not a child anymore
Life has opened the door
To a new exciting life
I lose control whem I’m close to you, babe
I lose control don’t look at me like this
There’s something in your eyes
Is this love at first sight?
Like a flower that grows
Life just wants you to know
All the secrets of life
It’s all written down in your lifelines
It’s written down inside your heart
You and I just have a dream
To find our love a place, where we can hide away
You and I, we’re just made
To love each other now, forever and a day, yeah
I lose control because of you, babe
I lose control don’t look at me like this
There’s something in your eyes
That is saying tonight
I’m so curious for more
Just like never before
In my innocent life
It’s all written down in your lifelines, yeah
It’s written down inside your heart
You and I just have a dream
To find our love a place, where we can hide away
You and I, we’re just made
To love each other now, forever and a day, yeah
The time stands still
When the days of innocence
Are falling for the night
I love you, girl, I always will
I swear, I’m there for you
Till the day I die
You and I just have a dream
To find our love a place, where we can hide away
You and I, we’re just made
To love each other now, forever and a day, yeah
Rock You Like a Hurricane – Scorpions
It’s early morning
The Sun comes out
Last night was shaking
And pretty loud
My cat is purring
And scratches my skin
So what is wrong
With another sin
The bitch is hungry
She needs to tell
So give her inches
And feed her well
More days to come
New places to go
I’ve got to leave
It’s time for a show
Here I am, rock you like a hurricane
Here I am, rock you like a hurricane
My body is burning
It starts to shout
Desire is coming
It breaks out loud
Lust is in cages
Till storm breaks loose
Just have to make it
With someone I choose
The night is calling
I have to go
The wolf is hungry
He runs the show
He’s licking his lips
He’s ready to win
On the hunt tonight
For love at first sting
Here I am, rock you like a hurricane (Are you ready baby?)
Here I am, rock you like a hurricane
Here I am, rock you like a hurricane (Come on, come on baby)
Here I am, rock you like a hurricane
Rock you like a hurricane
It’s early morning
The Sun comes out
Last night was shaking
And pretty loud
My cat is purring
And scratches my skin
So what is wrong
With another sin
The night is calling
I have to go
The wolf is hungry
He runs the show
He’s licking his lips
He’s ready to win
On the hunt tonight
For love at first sting
Here I am, rock you like a hurricane (Are you ready baby?)
Here I am, rock you like a hurricane
Here I am, rock you like a hurricane (Come on, come on, come on, come on, come on)
Here I am, rock you like a hurricane
Here I am
The Sun comes out
Last night was shaking
And pretty loud
My cat is purring
And scratches my skin
So what is wrong
With another sin
The bitch is hungry
She needs to tell
So give her inches
And feed her well
More days to come
New places to go
I’ve got to leave
It’s time for a show
Here I am, rock you like a hurricane
Here I am, rock you like a hurricane
My body is burning
It starts to shout
Desire is coming
It breaks out loud
Lust is in cages
Till storm breaks loose
Just have to make it
With someone I choose
The night is calling
I have to go
The wolf is hungry
He runs the show
He’s licking his lips
He’s ready to win
On the hunt tonight
For love at first sting
Here I am, rock you like a hurricane (Are you ready baby?)
Here I am, rock you like a hurricane
Here I am, rock you like a hurricane (Come on, come on baby)
Here I am, rock you like a hurricane
Rock you like a hurricane
It’s early morning
The Sun comes out
Last night was shaking
And pretty loud
My cat is purring
And scratches my skin
So what is wrong
With another sin
The night is calling
I have to go
The wolf is hungry
He runs the show
He’s licking his lips
He’s ready to win
On the hunt tonight
For love at first sting
Here I am, rock you like a hurricane (Are you ready baby?)
Here I am, rock you like a hurricane
Here I am, rock you like a hurricane (Come on, come on, come on, come on, come on)
Here I am, rock you like a hurricane
Here I am
Holiday Lyrics - Scorpions
Let me take you far away
You’d like a holiday
Let me take you far away
You’d like a holiday
Exchange the cold days for the sun
A good time and fun
Let me take you far away
You’d like a holiday
Let me take you far away
You’d like a holiday
Let me take you far away
You’d like a holiday
Wherever you are
Let me take you far away
You’d like a holiday
Longing for the sun you will come
To the island without name
Longing for the sun be welcome
On the island many miles away from home
Be welcome on the island without name
Longing for the sun you will come
To the island many miles away from home
You’d like a holiday
Let me take you far away
You’d like a holiday
Exchange the cold days for the sun
A good time and fun
Let me take you far away
You’d like a holiday
Let me take you far away
You’d like a holiday
Let me take you far away
You’d like a holiday
sumber www.rizkyonline.com
Exchange your troubles for some loveWherever you are
Let me take you far away
You’d like a holiday
Longing for the sun you will come
To the island without name
Longing for the sun be welcome
On the island many miles away from home
Be welcome on the island without name
Longing for the sun you will come
To the island many miles away from home
Always Somewhere Lyrics - Scorpions
Arrive at seven the place feels good
No time to call you today
Encores till eleven then Chinese food
Back to the hotel again
I call your number the line ain't free
I like to tell you come to me
A night without you seems like a lost dream
Love I can't tell you how I feel
Always somewhere
Miss you where I've been
I'll be back to love you again
Another morning another place
The only day off is far away
But every city has seen me in the end
And brings me to you again
Always somewhere
Miss you where I've been
I'll be back to love you again.
No time to call you today
Encores till eleven then Chinese food
Back to the hotel again
I call your number the line ain't free
I like to tell you come to me
A night without you seems like a lost dream
Love I can't tell you how I feel
Always somewhere
Miss you where I've been
I'll be back to love you again
Another morning another place
The only day off is far away
But every city has seen me in the end
And brings me to you again
Always somewhere
Miss you where I've been
I'll be back to love you again.
Subscribe to:
Posts (Atom)