Solved

Sybase ASE: concatenating large texts of character data

Posted on 2008-10-06
9
1,998 Views
Last Modified: 2008-11-16
Hi,

I need to write a stored proc that, given a view name will return it's sql text. This information is available in the syscomments system table, under the column 'text'. About syscomments: syscomments contains entries for each view, rule, default, trigger, table constraint, and procedure. The text column contains the original definition statements. If the text column is longer than 255 bytes, the entries will span rows. Each object can occupy up to 65,025 rows.

I have written a stored proc to do this, but the problem that I am having is that varchar cannot store so much data and if i try to use text, the i cannot concate it. The attached code snippet gives an error on the update line, saying: Invalid operator for datatype op: ADD type: TEXT

Kindly advise.
Thanks!
create procedure sp_getviewmetadata

@db_name varchar(20), @view_name varchar(20), @owner varchar(20)

as

declare @text varchar(1024)

declare @text_line varchar(1024)

declare @lines_count int

declare @i int
 

select @text = "", @i = 1
 

select @lines_count = count(c.text) from syscomments c, sysobjects o, sysusers u where o.id = c.id and o.uid = u.uid and o.name = @view_name and u.name = @owner
 

create table #viewquery

(

	sqlquery	text

)
 

while (@i <= @lines_count)

begin

    select @text_line = c.text from syscomments c, sysobjects o, sysusers u where o.id = c.id and o.uid = u.uid and o.name = @view_name and u.name = @owner and colid = @i    
 

    if @i = 1   

        insert into #viewquery select @text_line   

    else

        update #viewquery set sqlquery = sqlquery + convert(text, @text_line)
 

    select @i = @i + 1

end
 

select #viewquery.sqlquery from #viewquery

Open in new window

0
Comment
Question by:sdgarg
  • 4
  • 3
9 Comments
 
LVL 19

Expert Comment

by:grant300
ID: 22654668
While you don't specify the version of ASE you are running, the fact that you can declare a VARCHAR(1024) says it is at least 12.5.1

ASE version 12.5.1 and higher support larger page sizes, up to 16KB, and have increased the max size of varchar columns and variables.  Varchar columns can be up to just slightly less than the page size for the server while varchar variables (and SP arguments) can be up to 16KB regardless of page size.

While this will generally be large enough for view objects though there may well be stored procs that don't fit in 16KB.

I guess the $64 question is why do you need the text returned in a single SP argument.  I would format the source code from syscomments back into lines to match the original source as it was compiled and return a result set of varchar(254).  That is, after all, what you are really after isn't it; the original source code in the original format.

You don't state what the client code language is or what you are going to do with it but you should be able to easily construct a blob on the client side if you really need to.

The other alternative is kind of ugly but it stretches this quite a bit further.  You can create several SP arguments of type varchar(16200) and successive portions in each on in turn.  Quite frankly, I think that is the worst way to do this but it is up to you.

Finally, I have a comment on your code.  While what you have done is not wrong, I would declare a cursor on the syscomments table and selects what you need and uses and ORDER BY clause to get the rows in the correct order.   Read a row into a "buffer", find the first segment by the line terminator, output it to a result set, and repeat until you run out of buffer, then read the next row.  Pretty standard stuff.

Regards,
Bill
0
 

Author Comment

by:sdgarg
ID: 22654859
Thanks for the reply.
Here is the missing information that I had not provided in my original question:
Sybase ASE 12.5.3
Client code is Java 1.4 v

'I guess the $64 question is why do you need the text returned in a single SP argument.  I would format the source code from syscomments back into lines to match the original source as it was compiled and return a result set of varchar(254).  That is, after all, what you are really after isn't it; the original source code in the original format.'
Yes, this is exactly what I want: the original source code in the original format. But I am sorry, I did not understand what you are suggesting can be done to implement this. The only restriction that I do have is that I have to return only one resultset from the stored procedure, as the Java framework from which I have to execute my stored proc cannot handle multiple resultsets.

Thanks!
0
 
LVL 19

Expert Comment

by:grant300
ID: 22655063
Well if you are in Java, the string handling is much better there than in T-SQL.  Why not just call a simplified stored procedure that does a single SELECT statement returning the "text" column from syscomments.

Now you have everything in the result set in Java and you can concatenate the individual text values together into a large object.  The line terminators are embedded in the source code in syscomments so the formatting should all be there.

Regards,
Bill

BTW, what do you need the source code for views in a Java user application for if you don't mind my asking?

create procedure sp_getviewmetadata  @view_name varchar(32), @owner varchar(32)

as

begin

select c.text

  from syscomments c, 

       sysobjects o,

       sysusers u

 where o.id = c.id

   and o.uid = u.uid

   and o.name = @view_name

   and u.name = @owner

order by c.colid

end

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 19

Expert Comment

by:grant300
ID: 22655084
BTW, you know that stored procedures that start with "sp_" are treated specially by Sybase.  You should add the procedure to the sybsystemprocs database ONLY.  You can then call it from any database you like and it will run in the local context.

Bill
0
 

Author Comment

by:sdgarg
ID: 22655121
Thanks again.
This solution will not work in my case, as I cannot do any processing from my Java code. My stored proc needs to return the exact data. That is the limitation of the framework that we are using. So I need to return the concatenated text from the proc itself.

The company i work for builds metadata management system, and the source code of the view is part of metadata of the view...hence I need it.
0
 

Author Comment

by:sdgarg
ID: 22655149
I did read that abt procedures that start with sp_ and hence I named mine to start with sp_. However I thought that I can put it in any database. I did not know that it needs to be in the sybsystemprocs database only. Right now I am putting my procs in the master database and it runs in the local context of any database. Is it not ok to put it in the master database?
0
 
LVL 19

Accepted Solution

by:
grant300 earned 125 total points
ID: 22655515
Putting it in the MASTER database might work, however, I suspect this is a throwback to the days when system stored procs were all in the master and sybsystemprocs did not exist or it was optional to configure on.  If you put them in any user database, they loose their special properties and worse, they hide the system proc.

That is likely to be a deprecated behavior and you should plan on putting it in sybsystemprocs instead so you don't get burned.

As far as the other goes, let me put this as politely as I can: What the heck good is using a rich language like Java if you cannot write even the simplest code?  What "framework" are you using?  It sounds as if there really is no Java coding going on at all; they are trying to do everything in the 4GL/framework.  Good luck.



As far as getting the stored procedure to do what you want, it will take several hours of fooling around with it to get you an industrial-strength stored procedure written.  It is really beyond the scope of an EE question.

The process I outlines above still holds; you can present the formatted source code as a series of rows that come back as one line per row but you have to unpack them from the text field in syscomments.  The text field has potentially multiple source code lines stuffed in it.  You want to pull the lines out of the text field until you get a partial one at the end (since they usually don't pack evenly) and then pull the remainder of that partial line out of the text field in the next row.

You will wind up using a bunch of string function to locate the line terminator and substring out the portions you need.  As a hint, after you substring out the line (or second half as the case may be), you should use the RIGHT function to eliminate that portion you have already parsed out.  Then your string handling only needs to deal with the beginning of the text field string each time.

Take a stab at the code and see how far you get.  We can help you from there.

Regards,
Bill
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Is your company's data protection keeping pace with virtualization? Here are 7 dynamic ways to adapt to rapid breakthroughs in technology.
For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now