Skip to Content

Support with Sybase ASE 16

Dec 08, 2017 at 02:22 PM


avatar image

Hello all,

I am looking for some support on few topics in Sybase ASE 16.

1. How Do I get schema of a table. I am using below SQL to get table name and column names in a database but not able to get Schema info.

select table_name, column_name, data_type,
       st.length max_length,
       sc.colid colnumber
  from sysobjects so inner join syscolumns sc on = inner join systypes st on st.usertype = sc.usertype
 where so.type = 'U'
 order by table_name, colnumber

  • This gives me info as follows. But I miss "pubs3" as schema. How to get it?
    authors au_id id 11 1
    authors au_lname varchar 4096 2


2. I installed my Sybase with page size = 4K. This limits the maximum sizes of my BINARY and VARBINARY columns to 4010 bytes. Is there a way I increase it to 16K without reinstalling the database. _____________________________________________________________________

3. I am storing ZIP file of 2000 bytes into a BINARY column. When I read this information through a CSharp code, using "Sybase.Data.AseClient" with following code and extracting the text using TiKa extract, the extraction fails. This code is absolutely working fine with a ZIP file in IMAGE column and for a plain text file in BINARY column.

using TikaOnDotNet.TextExtraction;
using Sybase.Data.AseClient;
var _te = new TextExtractor();
AseConnection conn = new AseConnection();
conn.ConnectionString = SqlConnectionString;
IDbCommand cmd = new AseCommand(qryStmt, conn);
IDataReader reader = null;
reader = cmd.ExecuteReader(System.Data.CommandBehavior.Default);
	while (reader.Read())
		int len = Int32.Parse(reader["LEN"].ToString());
		byte[] b = new byte[len];
		reader.GetBytes(reader.GetOrdinal(colName), 0, b, 0, (int)len);
			var textExtractionResult = _te.Extract(b);
			string txt = textExtractionResult.Text;
		catch (System.Exception Ex2) { ex.Rows.Add(qryStmt,"Error (also while trying to scan without extracting): "+Ex2.Message); }  
Any support in this regard is highly appriciated. RegardsSP
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Bret Halford
Dec 08, 2017 at 02:57 PM

Hi Sds,

In the future, please open a new post for each distinct question and use a title more specific to the question, it helps
other people find the question and answer in the future. Also, I think people are less likely to respond at all if they are unable
to answer all of the questions posed in a post. While I pride myself on my ability to say "I don't know...", not everyone is
as willing to display ignorance in public. :-)

1) Assuming you are in the pubs3 database, select db_name().

2) To change page size, you would need to install a new ASE server, not just a new database.

3) I don't know on this one. What you might try is a byte-by-byte comparison of the extracted value with the original value
to see where and how they start to differ (for example, a DIFF of OD -X on both files.), as that will likely be a clue as to what
is happening. Is the extracted value smaller than the original (i.e. it got truncated?)

Bret Halford

10 |10000 characters needed characters left characters exceeded
Andy Ashwood
Dec 11, 2017 at 09:58 AM

Just to follow up on Bret's answer:

1> ASE holds the table schema in tables which are specific to each database so therefore logically there is no need to store database name in the schema tables. Thats why you have to use the db_name() function rather than looking it up in a table.

3> If you have a binary(4010) column and you store a 2000 byte zip file into it then ASE will pad out the remaining 2010 bytes with zero's as this is a fixed length column. Then when you query the table to retrieve your zip file you will get 4010 bytes of data back. Could it be these trailing zero's which are messing you up? Try using a variable length (varbinary) column so you only get back the 2000 bytes you stored instead to see if this helps.


Andy Ashwood

Show 1 Share
10 |10000 characters needed characters left characters exceeded

#3) - note, though, that the default behavior of varbinary() is to truncate any trailing 0x00 bytes, unless configuration parameter "disable varbinary truncation" is on. This might cause similar issues with a small percentage of the values.

Ben Slade Dec 12, 2017 at 05:28 PM

Re: How Do I get schema of a table.

I use the word "schema" to mean the design of tables, indexes, etc. So you are partially already extracting the schema for a database. Are you talking about the database name? Or the owner name for a set of db objects?

In anycase, there is the ddlgen utility that comes with the ASE server installation. ddlgen requires ASE servers files at run time. Ie., has to be run on the ASE server host (someone please correct me if I'm wrong)

FYI, my experience is that the 15.5 version of ddlgen doesn't work with ASE 16. Also when using the ASE 16 version of ddlgen against a database with a large number of objects, ddlgen creates thousands of transient hidden stored procs (flushing the procedure cache) that go away when ddlgen finishes. Probably a bug executing a "prepare" for every SQL statement.

10 |10000 characters needed characters left characters exceeded