Go Premium for a chance to win a PS4. Enter to Win


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 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)
    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   
        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
  • 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 o.id = c.id
   and o.uid = u.uid
   and o.name = @view_name
   and u.name = @owner
order by c.colid

Open in new window

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
In this article, the configuration steps in Zabbix to monitor devices via SNMP will be discussed with some real examples on Cisco Router/Switch, Catalyst Switch, NAS Synology device.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses
Course of the Month12 days, 1 hour left to enroll

916 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