#629 Unable to see source of database views

2.6.5
closed-fixed
5
2014-08-19
2008-05-06
Peepsalot
No

I was having an issue in 2.6.4 where I wanted to see the "source" for a view, but Squirrel is just returning "null". There are many views in this database, and Squirrel will correctly display the source for *most* of them, but for some reason there are a few that just show null. I don't
know what would be special or different about the few that are acting up.

Client: Squirrel SQL client 2.6.4
Database: MS SQL Server 2000
Driver: com.inet.tds.TdsDriver
OS: Linux 2.6.24 (Ubuntu 8.04)

Today I was going to test if this was still a problem in Squirrel 2.6.5a, but the "source" tab does not even appear at all anymore. I can't see any way to view the source for any views with this version.

Discussion

  • Peepsalot

    Peepsalot - 2008-05-06

    Logged In: YES
    user_id=1041441
    Originator: YES

    Someone just informed me that the view source can be seen in MS SQL 2000 with the following query:
    select syscomments.text FROM syscomments JOIN sysobjects ON syscomments.id = sysobjects.id WHERE sysobjects.name = 'VIEW_NAME_GOES_HERE'

    For views with very long source, it will return multiple rows which must then be concatenated. I tested this on the views that are not displaying correctly for me, and they all return multiple rows like this.

    So it seems that maybe the code for displaying source does not take into account the possibility of multiple rows?

     
  • Peepsalot

    Peepsalot - 2008-05-12

    Logged In: YES
    user_id=1041441
    Originator: YES

    It appears this functionality is provided by the "Microsoft SQL Server Assistant" plugin v 0.3 (internal name "mssql") by Ryan Walberg

    This plugin, despite being included in the SQuirreL SQL installer, is not listed on this page:
    http://www.squirrelsql.org/index.php?page=plugins

    I'm unsure how to contact the author, or even where the source code lies.

     
  • Rob Manning

    Rob Manning - 2008-06-14

    Logged In: YES
    user_id=1287991
    Originator: NO

    The MS SQL Sever plugin relies on the following query to show the view definition:

    SELECT VIEW_DEFINITION
    FROM INFORMATION_SCHEMA.VIEWS
    WHERE TABLE_CATALOG = ?
    AND TABLE_NAME = ?

    TABLE_CATALOG will be bound to the value of catalogName in the info tab for the view. TABLE_NAME is bound to the "simpleName" value in the info tab for the view. Can you run this query and check to see that the view_definition is correct? I have setup a local SQLServer 2000 eval edition for testing and it seems to work ok for me. What version of the JDBC driver are you using (getDriverVersion in the Metadata tab for the root node in the object tree)?

    Rob

     
  • Rob Manning

    Rob Manning - 2008-06-14

    source of test view in SQL-Server 2000

     
  • Rob Manning

    Rob Manning - 2008-06-14

    Logged In: YES
    user_id=1287991
    Originator: NO

    The MS SQL Sever plugin relies on the following query to show the view definition:

    SELECT VIEW_DEFINITION
    FROM INFORMATION_SCHEMA.VIEWS
    WHERE TABLE_CATALOG = ?
    AND TABLE_NAME = ?

    TABLE_CATALOG will be bound to the value of "catalogName" in the info tab for the view. TABLE_NAME is bound to the "simpleName" value in the info tab for the view. Can you run this query and check to see that the view_definition is correct? I have setup a local SQLServer 2000 eval edition for testing and it seems to work ok for me. What version of the JDBC driver are you using (getDriverVersion in the Metadata tab for the root node in the object tree)? I downloaded I-Net Software's version 7.03 and it seems to show the view source fine.

    Rob

    File Added: Screenshot.png

     
  • Nobody/Anonymous

    Logged In: NO

    Hi Rob,

    I just checked, I am using version 7.00.

    I also just tested this with the JTDS driver ("jTDS Type 4 JDBC Driver for MS SQL Server and Sybase", v 1.2.2), and got the same problem so I don't think it's an issue in the driver.

    I tried manually running the query you gave in Squirrel, and I get null still. I can see that the INFORMATION_SCHEMA.VIEWS is returning a record when I select * from it, it's just that the VIEW_DEFINITION is null.

    Again, this problem only happens for particular views in my database, and I think it is only the ones that are very long. This length limit that causes problems appears to be 4096 characters.

     
  • Rob Manning

    Rob Manning - 2008-06-17

    Logged In: YES
    user_id=1287991
    Originator: NO

    Oh, 4096 characters you say... My view was not even close to that. I'll see if I can whip up a test view that has > 4096 chars.
    Thanks for the info.

    Rob

     
  • Rob Manning

    Rob Manning - 2008-07-06

    Logged In: YES
    user_id=1287991
    Originator: NO

    I did manage to reproduce this on SQLServer 2000. I opened the management tool and discovered that INFORMATION_SCHEMA.VIEWS is defined as follows:

    create view INFORMATION_SCHEMA.VIEWS
    as
    select
    db_name() as TABLE_CATALOG
    ,user_name(obj.uid) as TABLE_SCHEMA
    ,obj.name as TABLE_NAME
    ,case
    when exists (select *
    from syscomments com3
    where com3.id = obj.id
    and com3.colid > 1) then convert(nvarchar(4000), NULL)
    else com.text
    end as VIEW_DEFINITION
    ,case
    when exists (select *
    from syscomments com2
    where com2.id = obj.id
    and CHARINDEX('WITH CHECK OPTION',
    upper(com2.text)) > 0) then 'CASCADE'
    else 'NONE'
    end as CHECK_OPTION
    ,'NO' as IS_UPDATABLE
    from
    sysobjects obj
    ,syscomments com
    where
    permissions(obj.id) != 0
    and obj.xtype = 'V'
    and obj.id = com.id
    and com.colid = 1

    Notice that view_definition has this :

    convert(nvarchar(4000), NULL)

    My guess is that means that view isn't intended for view definitions with > 4000 chars. Nice!

    I guess we'll need to use syscomments directly then.

    Rob

     
  • Rob Manning

    Rob Manning - 2008-07-07

    Logged In: YES
    user_id=1287991
    Originator: NO

    Fixed in CVS. Adjusted the query for view source to use underlying system tables instead of INFORMATION_SCHEMA.VIEWS, since this view cannot handle views that are greater than 4000 characters. The new query is much like the one that was suggested, only it is ordered:

    SELECT text FROM sysobjects o , syscomments c
    where o.name = ?
    and o.id = c.id
    order by c.colid

    This will be available in next week's snapshot.

    Rob

     
  • Rob Manning

    Rob Manning - 2008-07-07
    • labels: 336290 --> MS-SQL Plugin
    • status: open --> closed-fixed
     
  • Rob Manning

    Rob Manning - 2008-07-12

    Logged In: YES
    user_id=1287991
    Originator: NO

    This has been merged to the 2.6 branch and will be available in the 2.6.7 release.

    Rob

     

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

JavaScript is required for this form.





No, thanks