Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Checksum of a Stored Procedure

4 posts in General Discussion Last posting was on 2010-05-10 21:51:49.0Z
Ray Posted on 2010-05-10 17:41:53.0Z
From: "Ray" <lcm@hotmail.com>
Newsgroups: sybase.public.ase.general
Subject: Checksum of a Stored Procedure
Lines: 32
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5843
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4be84561$1@forums-1-dub>
Date: 10 May 2010 10:41:53 -0700
X-Trace: forums-1-dub 1273513313 10.22.241.152 (10 May 2010 10:41:53 -0700)
X-Original-Trace: 10 May 2010 10:41:53 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29183
Article PK: 78419

Hi,

We have a job system running a set of stored procedure. In order to ensure
the stored procedures
are the one we confirmed, we would like to do a calculation of MD5 with
content of a stored
procedure content. And then verify with another database record to make sure
the stored procedure
is correct or not modified. However, I know Sybase 12.5 does not offer any
MD5 function.

So I think can I "sp_helptext SP_NAME" to output the content of a stored
procedure and then
calculate the MD5 of the content in another programming language (i.e. .NET
or Java)?

But I worried that the content output from development or UAT sybase (i.e.
same version)
may not exactly equal to the production sybase even it is the same stored
procedure.
Is it possible?

Or any other way to select stored procedure content from internal system
tables rather
than using "sp_helptext" command?

Any solutions are welcome.

Thanks,
Raymond


Rob V [ Sybase ] Posted on 2010-05-10 19:10:18.0Z
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.9) Gecko/20100317 Thunderbird/3.0.4
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Checksum of a Stored Procedure
References: <4be84561$1@forums-1-dub>
In-Reply-To: <4be84561$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4be85a1a@forums-1-dub>
Date: 10 May 2010 12:10:18 -0700
X-Trace: forums-1-dub 1273518618 10.22.241.152 (10 May 2010 12:10:18 -0700)
X-Original-Trace: 10 May 2010 12:10:18 -0700, vip152.sybase.com
Lines: 64
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29185
Article PK: 78422


On 10-May-2010 19:41, Ray wrote:
> Hi,
>
> We have a job system running a set of stored procedure. In order to ensure
> the stored procedures
> are the one we confirmed, we would like to do a calculation of MD5 with
> content of a stored
> procedure content. And then verify with another database record to make sure
> the stored procedure
> is correct or not modified. However, I know Sybase 12.5 does not offer any
> MD5 function.
>
> So I think can I "sp_helptext SP_NAME" to output the content of a stored
> procedure and then
> calculate the MD5 of the content in another programming language (i.e. .NET
> or Java)?
>
> But I worried that the content output from development or UAT sybase (i.e.
> same version)
> may not exactly equal to the production sybase even it is the same stored
> procedure.
> Is it possible?
>
> Or any other way to select stored procedure content from internal system
> tables rather
> than using "sp_helptext" command?
>
> Any solutions are welcome.
>
> Thanks,
> Raymond
>
>
>

Yes, this is possible, but the question is: do you want to calc the MD5
in SQL our outside the server? The latter is likely easier:
reverse-engineer the proc code with the 'ddlgen' tool, and then run MD5
on it.
Inside ASE, you can only run MD5 (provided you have 15.0.2 or later or
MD5 isn't available at all) per row. Should you'd need to concatenate
all SQL lines before doing the MD5 which makes it hard if the total
length would exceed 16384 bytes (which is the max run-time string length).

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., Amersfoort, The Netherlands
Chamber of Commerce 27138666
-----------------------------------------------------------------


J Posted on 2010-05-10 21:51:49.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.general
Subject: Re: Checksum of a Stored Procedure
Reply-To: J@bogusemailAddress.com
Message-ID: <4be87f2d.13036656@forums.sybase.com>
References: <4be84561$1@forums-1-dub> <4be85a1a@forums-1-dub>
X-Newsreader: Forte Free Agent 1.21/32.243
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 10 May 2010 14:51:49 -0700
X-Trace: forums-1-dub 1273528309 10.22.241.152 (10 May 2010 14:51:49 -0700)
X-Original-Trace: 10 May 2010 14:51:49 -0700, vip152.sybase.com
Lines: 87
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29187
Article PK: 78426

On 10 May 2010 12:10:18 -0700, "Rob V [ Sybase ]"
<robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote:

Since there is an openssl command in the $SYBASE/OCS-15_)/bin dir on
latest releases.

One could do:

sp_helptext <procname>
go | openssl dgst -md5

returning the hash result.

e.g.
1> sp_helptext proc1
2> go | openssl dgst -md5
2f4b51be2213ed421b1f933125ed0bea

Jay

>On 10-May-2010 19:41, Ray wrote:
>> Hi,
>>
>> We have a job system running a set of stored procedure. In order to ensure
>> the stored procedures
>> are the one we confirmed, we would like to do a calculation of MD5 with
>> content of a stored
>> procedure content. And then verify with another database record to make sure
>> the stored procedure
>> is correct or not modified. However, I know Sybase 12.5 does not offer any
>> MD5 function.
>>
>> So I think can I "sp_helptext SP_NAME" to output the content of a stored
>> procedure and then
>> calculate the MD5 of the content in another programming language (i.e. .NET
>> or Java)?
>>
>> But I worried that the content output from development or UAT sybase (i.e.
>> same version)
>> may not exactly equal to the production sybase even it is the same stored
>> procedure.
>> Is it possible?
>>
>> Or any other way to select stored procedure content from internal system
>> tables rather
>> than using "sp_helptext" command?
>>
>> Any solutions are welcome.
>>
>> Thanks,
>> Raymond
>>
>>
>>
>
>Yes, this is possible, but the question is: do you want to calc the MD5
>in SQL our outside the server? The latter is likely easier:
>reverse-engineer the proc code with the 'ddlgen' tool, and then run MD5
>on it.
>Inside ASE, you can only run MD5 (provided you have 15.0.2 or later or
>MD5 isn't available at all) per row. Should you'd need to concatenate
>all SQL lines before doing the MD5 which makes it hard if the total
>length would exceed 16384 bytes (which is the max run-time string length).
>
>HTH,
>
>Rob V.
>-----------------------------------------------------------------
>Rob Verschoor
>
>Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
>and Replication Server 15.0.1/12.5 // TeamSybase
>
>Author of Sybase books (order online at www.sypron.nl/shop):
>"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
>"The Complete Sybase ASE Quick Reference Guide"
>"The Complete Sybase Replication Server Quick Reference Guide"
>
>mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
>http://www.sypron.nl
>Sypron B.V., Amersfoort, The Netherlands
>Chamber of Commerce 27138666
>-----------------------------------------------------------------
>


"Mark A. Parsons" <iron_horse Posted on 2010-05-10 19:23:59.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Checksum of a Stored Procedure
References: <4be84561$1@forums-1-dub>
In-Reply-To: <4be84561$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 100510-0, 05/10/2010), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4be85d4f@forums-1-dub>
Date: 10 May 2010 12:23:59 -0700
X-Trace: forums-1-dub 1273519439 10.22.241.152 (10 May 2010 12:23:59 -0700)
X-Original-Trace: 10 May 2010 12:23:59 -0700, vip152.sybase.com
Lines: 59
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29186
Article PK: 78423

The source code for a proc is broken up into varchar(255) chunks and dumped into the syscomments table.

The ASE 12.x version of sp_helptext basically dumps the contents of syscomments to the client ... no
appending/re-attaching of textual lines that were previously split into varchar(255) chunks.

The ASE 15.x version of sp_helptext has an option that provides for the appending/re-attaching of textual lines so that
they look like what was originally passed during the parse&compile of 'create procedure' command.

While you could implement your own MD5-like calculation against the results from sp_helptext (or select from
syscomments), you'll need to decide how to handle the case where the same exact proc exists in 2 different databases but
where the contents of syscomments (and thus what's displayed by sp_helptext) could be 'different' (eg, extra white
space, different dataserver page size).

Ideally you should see the same content in syscomments as long as you use the same stored proc source code file and the
same installation process. But if at any point someone happens to step outside your normal procedures you could end up
with 'different' contents in syscomments even though the proc is functionally/technically the same.

It may make more sense (?) if you perform a separate/new MD5 calculation each time the proc is created; this should
alleviate the issue of differing MD5 values across different databases even though the proc is actually the same.
Obviously (?) if you dump the database and then load it into a different database, you'll want to either a) re-calculate
your MD5 values or b) bring along the MD5 values that were created with the source database.

And of course there's the issue of what to do if the stored proc source code was deleted (eg, sp_hidetext) and thus no
longer available from syscomments.

Ray wrote:
> Hi,
>
> We have a job system running a set of stored procedure. In order to ensure
> the stored procedures
> are the one we confirmed, we would like to do a calculation of MD5 with
> content of a stored
> procedure content. And then verify with another database record to make sure
> the stored procedure
> is correct or not modified. However, I know Sybase 12.5 does not offer any
> MD5 function.
>
> So I think can I "sp_helptext SP_NAME" to output the content of a stored
> procedure and then
> calculate the MD5 of the content in another programming language (i.e. .NET
> or Java)?
>
> But I worried that the content output from development or UAT sybase (i.e.
> same version)
> may not exactly equal to the production sybase even it is the same stored
> procedure.
> Is it possible?
>
> Or any other way to select stored procedure content from internal system
> tables rather
> than using "sp_helptext" command?
>
> Any solutions are welcome.
>
> Thanks,
> Raymond
>
>