sybperl-l Archive

From: David Owen <dowen at midsomer dot org>
Subject: RE: getting output of sp_helptext
Date: Mar 23 2002 6:50PM

>>>>> "Dennis" == HP-Roseville,ex1   writes:

  Dennis> Hi David, I also noticed the same behavior.  I'm not sure why or
  Dennis> what is being done to the text strings.  However, I do have a
  Dennis> Perl script that returns the stored procedures (including
  Dennis> triggers) in a format that is identical to the format that was
  Dennis> used to create them.  I have two procedures.  One that will
  Dennis> return all of the stored procedures, and one that will returned a
  Dennis> specified stored procedure.

Alternatively, you can use dbschema.pl to extract your stored procedures.
There is an option that will extract just the stored procs or a particular
set of procs using a 'like' string.  Grab a copy from:

ftp://ftp.midsomer.org/pub/dbschema.tgz

There is a zip file at the same location.

dowen



  Dennis> -----Original Message----- From: David J Iannucci
  Dennis> [mailto:dji@myriad.com] Sent: Friday, March 22, 2002 6:26 PM To:
  Dennis> SybPerl Discussion List Subject: getting output of sp_helptext


  Dennis> Hi, I'm struggling with a frustrating problem now.  I'd like to
  Dennis> get the source text of a whole slew of stored procs out of our
  Dennis> database so that I can compare (diff) them en masse with what
  Dennis> we've got in CVS.

  Dennis> I note that sp_helptext returns the text of the procedure in
  Dennis> chunks, with newlines artificially attached to each chunk.  I
  Dennis> wrote a Perl script to collect these chunks from the output of
  Dennis> sp_helptext, "chomp" them, and re-concatenate them together.

  Dennis> The result, you'd think, would be identical to what had been put
  Dennis> into the database originally.  Apparently not.  In the process of
  Dennis> storing the source text, Sybase must be truncating trailing
  Dennis> whitespace, or something, from chunks, because after I
  Dennis> concatenate, I get words smooshed together with no whitespace in
  Dennis> between, viz. "where id = 10" becomes "whereid = 10".

  Dennis> Is this a well-known problem?  Am I missing something obvious?
  Dennis> If not, is there any way to get back OUT of Sybase exactly the
  Dennis> same source text that was put IN?  I'm using DBlib, if it makes
  Dennis> any difference.

  Dennis> Thanks for your time,

  Dennis> Dave Iannucci Myriad Genetics, Inc.  Salt Lake City, UT

-- 
David Owen     Midsomer Consultants Inc.      dowen@midsomer.org
Maintainer of the Sybase FAQ: http://www.isug.com/Sybase_FAQ