Toad World® Forums

Transition from Toad for Sybase 2.1.1 to Toad for SAP Solutions 3.1


#1

I recently received the “free period ended” message and upgraded to Toad for SAP Solutions 3.1. In Query Builder, when I double click on certain tables (that had no issues in my previous version), I am seeing a “Domain error during explicit conversion of INT value ‘0’ to a BIT field” message. I can click OK and the table shows is added but the table doesn’t show all of the fields. These tables were set up by others, I simply query off of them.


#2

Hi,

we are sorry about this issue. This problem is caused by converting function in our SQL for getting information about table for QB. To help you with this problem I would like to know more information about your connection. What version are you using (best would be if you can provide us SELECT @@version)? Are you able enable Execution trace in Toad and see SQL which is running? You can try run this SQL also in Editor and confirm us bad SQL?

As last thing I suggest you is try our latest Beta TSAP3.2. We already fixed similar issue before.

Filip


#3

I have similar issue. The version of Toad I used is the free 3.0.0.129 that I just downloaded from your website.

Whenever I select any database, and select Table from the Object viewer, I got the error. The tables are never shown.

This is the version of Sybase we are using:

Adaptive Server Enterprise/16.0/EBF 22385 SMP/P/X64/Windows Server/asecepheus/3530/64-bit/FBO/Sun Feb 16 06:52:50 2014


#4

Hi,

this is related only to some specific version of ASE DB(with some type of licensing) and we are sorry about it. Can you try it with our latest version 3.1 or Beta version from this site? I believe that we already fixed this problem.

If you need fix for your 3.0 version let me know and I will help you. But I need your (bad)SQL from Tracer as i described in my first post.

Thanks

Filip


#5

As this is the “latest” in date topic on this subject, and as it is still relevant, I am bumping this.

The problem is not unique to any ASE version, as I have observed from others it happens on ASE 11.x, 15.0, 16.0, and for on 15.7.

The problem was caused by an incorrect conversion to bit.

All those “convert(bit, & )” were to blame. This is an invalid operation to perform as the result of “100 & 100”, is “100”, not “1”.

I have corrected the query under “Db.CommonSql” -> “DescribeTableColumns”, and after a reconnecting to the ASE server, I can now see the tables properly.

This is the new code:

select
sc.name as Column_Name,
sc.colid as Column_ID,
convert(bit,case
when (sc.status & 8) = 8 then 1
else 0
end) as Nullable,
convert(bit,case
when (sc.status & 128) = 128 then 1
else 0
end) as Is_Identity,
case when sc.usertype = -1 then sxt.xtname else st.name end as Datatype,
case
when sc.usertype = 34 then sc.length/2
when sc.usertype = 35 then sc.length/2
else sc.length
end
AS Datatype_Length,
sc.prec as ‘Precision’,
sc.scale as Scale,
convert(bit,case
when st.name in (‘char’, ‘nchar’, ‘varchar’, ‘nvarchar’, ‘binary’, ‘varbinary’, ‘unichar’, ‘univarchar’) then 1
else 0
end) as Variable,
convert(bit,case
when index_col(db_name() + ‘.’ + user_name(so.uid) + ‘.’ + object_name (si.id),si.indid,1) = sc.name then 1
when index_col(db_name() + ‘.’ + user_name(so.uid) + ‘.’ + object_name (si.id),si.indid,2) = sc.name then 1
when index_col(db_name() + ‘.’ + user_name(so.uid) + ‘.’ + object_name (si.id),si.indid,3) = sc.name then 1
when index_col(db_name() + ‘.’ + user_name(so.uid) + ‘.’ + object_name (si.id),si.indid,4) = sc.name then 1
when index_col(db_name() + ‘.’ + user_name(so.uid) + ‘.’ + object_name (si.id),si.indid,5) = sc.name then 1
when index_col(db_name() + ‘.’ + user_name(so.uid) + ‘.’ + object_name (si.id),si.indid,6) = sc.name then 1
when index_col(db_name() + ‘.’ + user_name(so.uid) + ‘.’ + object_name (si.id),si.indid,7) = sc.name then 1
when index_col(db_name() + ‘.’ + user_name(so.uid) + ‘.’ + object_name (si.id),si.indid,8) = sc.name then 1
when index_col(db_name() + ‘.’ + user_name(so.uid) + ‘.’ + object_name (si.id),si.indid,9) = sc.name then 1
when index_col(db_name() + ‘.’ + user_name(so.uid) + ‘.’ + object_name (si.id),si.indid,10) = sc.name then 1
when index_col(db_name() + ‘.’ + user_name(so.uid) + ‘.’ + object_name (si.id),si.indid,11) = sc.name then 1
when index_col(db_name() + ‘.’ + user_name(so.uid) + ‘.’ + object_name (si.id),si.indid,12) = sc.name then 1
when index_col(db_name() + ‘.’ + user_name(so.uid) + ‘.’ + object_name (si.id),si.indid,13) = sc.name then 1
when index_col(db_name() + ‘.’ + user_name(so.uid) + ‘.’ + object_name (si.id),si.indid,14) = sc.name then 1
when index_col(db_name() + ‘.’ + user_name(so.uid) + ‘.’ + object_name (si.id),si.indid,15) = sc.name then 1
when index_col(db_name() + ‘.’ + user_name(so.uid) + ‘.’ + object_name (si.id),si.indid,16) = sc.name then 1
else 0
end) onPrimaryKey,
convert(bit,case
when (isnull(sc.status2, 0) & 128) = 128 then 1
else 0
end) as encrypted,
convert(bit,case
when st.usertype > 100 then 1
else 0
end) as usertype,
(select text from syscomments where sc.cdefault = id) as DefaultValue,
(select text from syscomments where sc.domain = id and charindex(‘check’, lower(text)) = 1) as CheckConstraint,
0 as DefaultValueType,
su.name as EKUser,
seo.name as EncryptionKey,sc.encrkeydb,’’ as BindValueName,sc.encrkeyid,
case
when sc.cdefault = 0 then 0
else (select status & 8 from syscomments where sc.cdefault = id)
end as ConstraintDefault,
isnull(sc.status2, 0) & 131072 as IsCompressed,
convert(bit,case
when (so.sysstat3 & 4096) = 1 then 1
when (so.sysstat3 & 8192) = 1 then 1
else 0
end) as TableCompression,
sc.lobcomp_lvl as LobCompLvl,
case
when st.type in (34, 35, 174) then
case
when sc.lobcomp_lvl is null and so.lobcomp_lvl is null then ‘default’
when sc.lobcomp_lvl is null and (so.lobcomp_lvl = 0) then ‘Not compressed’
when sc.lobcomp_lvl is null and (so.lobcomp_lvl <> 0) then convert(varchar, so.lobcomp_lvl) + ’ (inherited from table)’
when (sc.lobcomp_lvl = 255) then ‘Not compressed’
else convert(varchar, sc.lobcomp_lvl)
end
else ‘N/A’
end as DetailLobLvl,
case when st.type in (34, 35, 174) then convert(bit, 1)
else convert(bit, 0)
end as IsLOB
from
syscolumns sc,
sysobjects so,
systypes st,
sysxtypes sxt,
sysindexes si,
sysobjects seo,
sysusers su
where
sc.id = so.id
and sc.usertype = st.usertype
and so.name = {{StringQuote(?)}}
and so.uid = user_id({{StringQuote(?)}})
and so.id *= si.id
and sc.xtype *= sxt.xtid
and si.indid > 0
and convert(bit, si.status & 2048) = 1
and sc.encrkeyid *= seo.id
and seo.uid *= su.uid
order by sc.colid

Enjoy,

Ziv.


#6

Hi Ziv,

you are right it is not unique to ASE version but only for some types of ASE servers. We found out that problem is with status(2,3) columns and with null value. Documentation don’t allow put null value but some databases have it.

Your fix is correct and you are right that 100 & 100 is not 1 but 100 but you still get correct result when you use it in convert function -> convert(bit, number > 0) = 1.

We also fixed this problem but we are using isnull() function. We covered all our statements and it will be fix in next release (or you can manually change it in SQL Dictionary as you suggested).

Sample of our change:

convert(bit, isnull(sc.status, 0) & 8) as Nullable,
convert(bit, isnull(sc.status, 0) &128) as Is_Identity,

Thanks for your replay :slight_smile:

Filip