Jason L. Froebe: Ramblings of a Geek

35383: DBD::Sybase calls the broken sp_tables stored procedure

Centos 5
Linux dba-dev1 2.6.22.9-default #1 SMP Mon Oct 1 11:26:48 CDT 2007 i686
athlon i386 GNU/Linux

Perl v5.8.8 built for i386-linux-thread-multi
DBD::Sybase 1.07 but 1.08 also has the problem.

Due to Sybase CR 497173, sp_tables can not be relied upon to retrieve
table information correctly for ASE 15.

(ASE 15 stored procedure “sp_tables” doesn’t work with SQL UDFs)

We need to query the system tables instead. Obviously, the below code
would work in v12.0 and higher. A temp table could probably be used for
prior to 12.0 for the TABLE_TYPE to be filled correctly.

$ diff ../DBD-Sybase-1.07/Sybase.pm Sybase.pm
218c218
< my $sth = $dbh->prepare(”sp_tables $table, $schema, $catalog,
$type”);
—

$type”);
219a220,238
> my $sth = $dbh->prepare( q{
> select TABLE_QUALIFIER = db_name()
> , TABLE_OWNER = u.name
> , TABLE_NAME = o.name
> , TABLE_TYPE =
> case o.type
> when “U” then “TABLE”
> when “V” then “VIEW”
> when “S” then “SYSTEM TABLE”
> end
> , REMARKS = NULL
> from sysobjects o
> , sysusers u
> where o.type in (’U', ‘V’, ‘S’)
> and id >99
> and o.uid = u.uid
>
> });
>

35381: SQL::Translator::Parser::DBI::Sybase needs to strip the carriage return from stored procs/views sql

Centos 5
Linux dba-dev1 2.6.22.9-default #1 SMP Mon Oct 1 11:26:48 CDT 2007 i686
athlon i386 GNU/Linux

Perl v5.8.8 built for i386-linux-thread-multi
SQL::Translator::0.09000

Sybase ASE will often store the SQL text for stored procedures and views
with the carriage return embedded (\r). We need to strip that out else
we will receive “^M” in the output.

Sybase_orig.pm Sybase_new.pm

108a109

109a111,112

155a159,161
> $stuff->{procedures}->{$_->[0]}->{text} =~ s/\r//g
> if (defined ($stuff->{procedures}->{$_->[0]}->{text} ));
>

35380: SQL::Translator::Parser::DBI::Sybase fails when sp_helpindex is empty

Centos 5
Linux dba-dev1 2.6.22.9-default #1 SMP Mon Oct 1 11:26:48 CDT 2007 i686
athlon i386 GNU/Linux

Perl v5.8.8 built for i386-linux-thread-multi
SQL::Translator::0.09000

Sybase ASE’s sp_helpindex will normally return a result set containing
the column “INDEX_NAME” which parse() requires. When no index exists,
sp_helpindex returns a error string similar to “Object does not have any
indexes.” but no result set.

The following workaround simply wraps the selectall_hash with an eval,
if the eval results in no error, then try to use the hash.

Ubuntu 8.04 LTS Desktop:

http://releases.ubuntu.com/8.04/ubuntu-8.04-desktop-i386.iso.torrent i386
http://releases.ubuntu.com/8.04/ubuntu-8.04-desktop-amd64.iso.torrent AMD64

Kubuntu 8.04 LTS Desktop:

http://releases.ubuntu.com/kubuntu/hardy/kubuntu-8.04-desktop-i386.iso.torrent Kubuntu x86
http://releases.ubuntu.com/kubuntu/hardy/kubuntu-8.04-desktop-amd64.iso.torrent Kubuntu AMD64

Ubuntu 8.04 LTS Server:

http://releases.ubuntu.com/8.04/ubuntu-8.04-server-i386.iso.torrent i386
http://releases.ubuntu.com/8.04/ubuntu-8.04-server-amd64.iso.torrent AMD64

ASE implements a subset of SQL 92 and isn’t 100% compliant with the SQL 92 standard (no DBMS on the planet is btw).

SQL99 compliance isn’t seriously being looked at by the major commercial DBMS vendors. Disregarding the fact the the SQL standards aren’t all they are cracked up to be, the vendors have too much invested in their own proprietary SQL variants (and other components) to be 100% compliant. If they were 100% compliant with the SQL92/99/whatever standard, then wholesale migrations from one vendor to another would take place.

I believe as time goes forward the opensource DBMSs (PostgreSQL, MySQL, etc) may become far more compliant with the standards than the commercial vendors as vendor lock in doesn’t mean as much to them.

Look into what the vendors (Oracle, MS, IBM,etc) are saying what constitutes as “compliance”. Ask each vendor what parts of the SQL99 standard they will be implementing and which parts they won’t be. If any vendor says that they are 100% compliant with the SQL92 or SQL99 ANSI standard, then that particular person is lying to you. Granted, that person may have been told their DBMS was 100% compliant and believes it. An honest vendor says that they comply with features X,Y and Z of the SQL 92 or SQL 99 standards.

Personally, I have found no significant movement by any of the commercial DBMS vendors to implement the SQL99 standard. So far it has just been lip service IMHO.

My favorite Linux ads - sorry for all the videos the last two days:

Discovery Channel: I Love the World

Selena over at Tending the Garden, posted a very good video of why you would choose Wordpress or Drupal. It seems a little slanted towards Drupal but that may just be me

I’m working on a Historical Server replacement.  I need to check with my employer as to whether I can open source it or not but the guts of it consist of:

1. Perl POE/DBD::Sybase for the daemon/ASE db connections (using FreeTDS instead of OpenClient)
2. SQLite for the local storage (way lower overhead than ASE or SQL Anywhere)
3. Perl Catalyst / Template::Toolkit web front end with REST web services

All of this goes into a VMWare Appliance running Ubuntu 7.10 Server… a drop-in solution that can even be run on a laptop.  I’m hoping that my employer lets me release it as then I can zip it up and put it on sourceforge so everyone can benefit.

I downloaded the excellent SQLite administration tool, SQLiteman, today.  Take a look at the screenshots.  I’m very impressed with it but was disappointed that they don’t provide an Ubuntu binary package.  So… I built my own Ubuntu package using checkinstall:

sqliteman_1.0.1-1_i386.deb

When you install the Ubuntu package, it will show up in the Applications -> Office menu.

This came over the wire from twitter:

Perl is not going away

Sterling Hanenkamp wrote a great response to the now-infamous TIOBE Index article about how Perl is on its way out. This article is the sort of thing I wish I’d done when I was doing PR for The Perl Foundation. Sterling’s given me permission to republish it here. Here’s a link to the original. — Andy (Lester)

I’ve been taking DDJ for a couple years now. It’s cheap and occasionally has something interesting in it, but it’s been less interesting than I remember it being when I read it in college. I’ve been much more enamored with the Communciations of the ACM. Today, I received my issue and there’s an interview with Paul Jansen of TIOBE Software.

read more….

Andrew Sigmund over on the ISUG sybase-l mailing list, let us know that Sybase released ASE 15.0.2 esd 4 today. Go and get them!

The ASE 15 stored procedure sp_tables doesn’t work with SQL UDFs. sp_tables has the udfs coming back as system tables.

repro:
1) create a sql udf in a db
2) run sp_tables, you will see the UDF as a “SYSTEM TABLE”

DBADEV1.sybase_dba.1> sp_tables;
table_qualifier table_owner table_name table_type remarks
--------------- ----------- ---------- ---------- -------
sybase_dba dbo DATE_FORMAT SYSTEM TABLE NULL

In the above example, the DATE_FORMAT UDF is the one at Porting MySQL’s date_format function to Sybase ASE 15.0.2

I’ve opened a case with Sybase to get this fixed - I’ll post the CR # when I get it.

UPDATE:  Sybase assigned CR 497173 to this

Hi all,

I’m sure you’ve noticed by now but every so often a captcha will pop up when you’re reading my blog. This appears to be a feature of Wordpress 2.5 which I haven’t been able to disable yet. If anyone knows how to disable it, please let me know as the captcha is very annoying.

I’ve asked this on the Wordpress.org forums as well: Wordpress 2.5 and the annoying captcha

UPDATE: Looks like the culprit was http://crazyeddie.info/dealer/dealer.js in the footer.php file of the GlossyBlue 1.4 theme

The official download site http://www.ndesign-studio.com/resources/wp-themes/glossyblue/ doesn’t have this code. I did download it last week… hmmm

Otto42 over on the Wordpress.org forums identified the script as:

It’s a captcha defeating system. It’s not checking if you’re human or not, it’s sending the results back to crazyeddie.info to let the thing bypass the captcha from some other site!

I just went through all the files and verified that the only java script files that are being pulled are official ones.  Kudos to Otto42!

This year’s Flourish Conference was held at the University of Illinois in downtown Chicago (UIC). The Flourish web site describes Flourish as:

The goal of the Flourish Conference is to promote the use and adoption of Free Libre Open Source Software (FLOSS) by demonstrating the practical applications of FLOSS in the business and academic world. While everyone is familiar with businesses that focus on creating non-free proprietary software, the same cannot be said for those who specialize in developing and supporting FLOSS software, and an Open Culture.

I missed a part of Bruce Perens’ Innovation Goes Public presentation due to Chicago’s closing several sidewalks that go over or under the tristate. I’m not quite sure I agree with him that the road a head for open source is all rosey. Perhaps I misunderstood him but it seemed to me that the idea of simply giving the source of an application to the public would allow the programmers to still be paid via the support/service agreements.  This may work for some applications, but for others there wouldn’t be a sufficient number of support contracts to financially support the programmers Personally, I prefer open source but I will also pay for commercial proprietary software. I use what works.

The company I work for, Sungard Investment Systems, uses open source for many internal projects. One such product uses VMware and Linux for a portable monitoring system so I was highly interested in rBuilder Online from rPath. I attended the Appliance Development and Conary session presented by Stephanie Watson from rPath. I’m currently investigating on how well and easy it is to make a VMware appliance that would house a Sybase ASE server (DBMS) with no unnecessary os files. If the testing turns out well, I will ask my fellow Sybase ASE DBAs whether Sybase need certify it or whether we are ‘on our own’. The monitoring machine itself really doesn’t need Sybase ASE on it but since we have the license….

Dr. John Waclawsky, from Motorolla, gave a feel good presentation about the state of communication between wireless gadgets. The Revolution at the Edge of the Network Abstract. Short version: things are going to get very interesting in the next year

James Gray, from IBM, presented IBM’s Open Source Strategy - Turning Threat into Opportunity. While essentially a case study of how IBM has interacted as a semi-liason between the business executives and the open source community. IBM, to me, has seemed to be stuck in a love/hate relationship with open source and a dislike/hate relationship with the Free Software Foundation. That being said, it is necessary to give IBM credit giving credence to the open source community with the business executives. The IT folks no longer had to quietly use open source in the business but were free to use it extensively in many places. Linux, in particular, is no longer the wild west in many CTOs’ eyes but a integral part of their business.

I met Tony Wong in the Chicago Perl Mongers ‘booth’. We discussed the upcoming Yet Another Perl Conference (YAPC::NA 2008) that is being held in Chicago and the horrible documentation of the Catalyst MVC web framework. I informed him that the only way to learn Catalyst was the new book Catalyst by Jonathan Rockway (ISBN: 978-1-847190-95-6). I also warned him that the book should have gone through another round of proofreading prior to being published due to excessive example code errors, etc. I playfully suggested that Joshua McAdams be tormented because he wasn’t able to make it Friday morning.

The panel discussion on Friday afternoon was highly entertaining and a bit of a kangaroo court for Bobby Moore of Microsoft. I do not believe Mr. Moore was expecting Bruce Perens to repeatedly ask very pointed and informed questions. Moore did an excellent job of remaining cool and listening with an open mind to the criticism of Microsoft as a company. He explained that, yes, Microsoft did some not so nice things in the past but they are attempting to work with the open source community. Bruce Perens, Jon “Maddog” Hall, and James Bottomley brought up the pending MS Office OpenXML standard format issue where much of the format is proprietary and the published format won’t actually be used by Microsoft. Moore conceded that it was unlikely that Microsoft would use the published format as-is but the Office developers would likely base the actually used format off of the published format. Perens, Hall, Bottomley and James Gray all expressed their opinions that this was very typical of Microsoft. (side note: Microsoft has been accused of having the attitude of Embrace, Extend, Eliminate when it comes to industry standards.)

Perens, Hall and James Bottomley, as well as members of the audience, pummeled both James Gray of IBM and Bobby Moore of Microsoft for their companies’ approach and support of software patents.

Unfortunately, I was only able to attend the first day as my mother was in town from North Dakota to visit my three month old daughter (and the rest of us). If only I could have cloned myself… Don’t forget to check out the Flickr photo group for the Flourish 2008 Conference in Chicago!

From the Flourish people :

Hello All,

Flourish Conference 2008 starts tomorrow! Flourish 2008 will run from Friday, April 4th till Saturday, April 5th. Everything seems to be coming together and everyone on the Flourish Team believes we are about to put on a great confererence for you. We have 16 speakers, 6 special events and activities and currently over 325 registered attendees. If you know someone who has yet to register for Flourish 2008 who would like to take advantage of our free online registration, that person should sign up by 8:30 PM today!

The directions for how to get to UIC and Flourish 2008 are posted below this e-mail message. You can also refer to our venue page for the directions, a campus map, a link to the Flourish 2008 location in Google Maps and pictures of event locations. The venue page is located here: http://www.flourishconf.com/venue/.

We would like to bring to your attention that the Flourish 2008 Schedule has changed considerably in the last few days. Yesterday we added a new speaker and changed a couple of speech times. Unfortunately, these changes will not be reflected in your Flourish 2008 phamplet (although we may provide a corrective insert). You can see the updated Flourish 2008 schedule here: http://www.flourishconf.com/schedule/.

One event we particularly would like to bring to your attention is the Code for a Cause Hack-a-thon. The Code for a Cause Hack-a-thon is a great way to get involved with writing open source software while performing an act of community service. Dozens of open source developers and enthusiasts will collaborate to build open source websites and applications for local non-profit organizations. Projects include an interactive map of homeless shelters in Chicago, a website for a reforestation program in Ethiopia and a Facebook widget framework for raising awareness about critical social issues. Volunteers of all skill levels and backgrounds (including development, design, documentation, etc.) are welcome to attend!

See you soon,
The 2008 Flourish Team

Directions to Flourish 2008:

Read the rest of this entry »