Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Sybase ASE: concatenating large texts of character data

Posted on 2008-10-06
Medium Priority
Last Modified: 2008-11-16

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.
create procedure sp_getviewmetadata
@db_name varchar(20), @view_name varchar(20), @owner varchar(20)
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 = and o.uid = u.uid and = @view_name and = @owner
create table #viewquery
	sqlquery	text
while (@i <= @lines_count)
    select @text_line = c.text from syscomments c, sysobjects o, sysusers u where = and o.uid = u.uid and = @view_name and = @owner and colid = @i    
    if @i = 1   
        insert into #viewquery select @text_line   
        update #viewquery set sqlquery = sqlquery + convert(text, @text_line)
    select @i = @i + 1
select #viewquery.sqlquery from #viewquery

Open in new window

Question by:sdgarg
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 19

Expert Comment

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.


Author Comment

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.

LVL 19

Expert Comment

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.


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)
select c.text
  from syscomments c, 
       sysobjects o,
       sysusers u
 where =
   and o.uid = u.uid
   and = @view_name
   and = @owner
order by c.colid

Open in new window

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

LVL 19

Expert Comment

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.


Author Comment

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.

Author Comment

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?
LVL 19

Accepted Solution

grant300 earned 500 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.


Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Check out what's been happening in the Experts Exchange community.
The core idea of this article is to make you acquainted with the best way in which you can export Exchange mailbox to PST format.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA:…

705 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