Comparison of MS Sql Server vs. Sybase

Comparison Of Sybase and MS Sql Server

Just some thoughts here:

1. There are many large players in the financial industry who have significant investments in Sybase. This means there's a ready supply of DBA's who know both the industry and the technology.

2. Sybase has a data warehousing solution called Sybase IQ, which does lots of optimization specific to data warehouses automatically. It is a 'sparse database' model, storing all unique values and then references to those values, much like is done with varchar2 storage. This saves lots of space, and allows for faster queries if the queries are large. Caution, IQ is built for a few large queries; if you throw lots of small ones at it, it will choke / run very slow, so don't do that.

3. Sybase has available drivers in Perl, Python, etc., and runs on many different OS's (Linux especially). Not true for SQL server, which is a Microsoft-OS-ONLY DBMS.

4. Sybase has a reputation for handling very, very large datasets easily. Terabyte-sized tables are normal. MS SQL Server has a reputation for being optimized for small tables, and choking on large ones.

5. MS SQL Server has a very strange idea of backup / admin tools. These did not handle continuous backup (keeping the DB online thru a backup is a normal thing). Note, this might be fixed now. Sybase has always had this feature.

List of Common CPAN Modules

I've been having trouble finding a list of handy perl modules. You'd think that people would be ready with a list, but Perl.org doesn't keep any, and the Google search for 'list of common CPAN modules' doesn't return anything.

So, here's my list, for all it's worth. Feel free to post additions. I presume you've installed CPANPLUS, then invoked:

$ perl -MCPANPLUS -e shell

CPANPLUS> i Algorithm::Diff B::Keywords BSD::Resource Bundle::HTML CGI CPAN CPANPLUS Carp::Always Chart Config::General Crypt::Blowfish Crypt::DES Crypt::DSA DBD::mysql DBD::SQLite Daemon::Generic Data::Alias Data::Buffer Data::Dump Data::Dump::Streamer Data::Dumper Data::FormValidator Data::Peek Date::Calc DateTime DateTime::TimeZone Devel::NYTProf Devel::Size Email::Simple Email::MIME Email::Send Encode::Detect File::CheckTree File::Fetch File::GlobMapper File::MMagic File::Slurp File::Spec File::Temp File::Which Getopt::Declare Getopt::Euclid Getopt::Long Getopt::Std HTML::Entities HTML::Mason HTML::Parser HTML::Template HTML::Tidy HTML::TreeBuilder HTTP::Date HTTP::Request IO::Interactive IO::Prompt IO::Stringy IPC::System::Simple Image::Size JSON-RPC JSON-XS LWP LWP::MediaTypes List::MoreUtils List::Util Locale::Encode Log::Any Log::Log4perl MIME::Tools MIME::Types MailTools Math::Random::Secure Math::BigInt::FastCalc=>0.25, Math::BigRat=>0.2602, Module::Metadata Module::Starter Moose Net::Cmd Net::Domain Net::FTP Net::LDAP Net::NNTP Net::Netrc Net::PH Net::POP3 Net::SMTP Net::Telnet Net::Time PAR::Dist PDF::API2 PDF::Report Parse::RecDescent PatchReader Path::Classs Perl::Critic Perl::OSType POE Return::Value Regexp::Common Regexp::Assemble Text::Autoformat SOAP::Lite Scalar::Util Devel::Cover Test::Pod Test::Pod::Coverage Test::Spelling Test::Valgrind Test::kwalitee Test::MinimumVersion Module::Release Module::Build Module::Install Module::Install::AuthorTests Module::Install::ExtraTests Test::Prereq Parse::CPAN::Meta Spreadsheet-ParseExcel Spreadsheet-WriteExcel Template Test::Deep Test::Fail Test::Perl::Critic Test::Script Test::Tester Test::noWarnings Text::Diff Text::CSV Text::CSV::Simple Text::CSV_XS TheSchwartz Tie::Dir Time::HiRes Time::Zone TimeDate Try::Tiny WWW::Mechanize version

Apachectl: Use Wget instead of Lynx

A common error with apachectl is 'cannot find lynx'. This is a silly error because lynx is a text mode browser that is just used to retrieve the server-status page and grep out pertinent information.

Building Lynx just for use starting, stoppping, restarting, graceful, status, statusfull, etc. on Apache is silly. Most servers already have Curl or Wget. I prefer wget because it's simple and easy to use.

To use wget instead of lynx, modify your apachectl file as follows:

1. add a line near the top with:

# PIDFILE
PIDFILE='/home/username/local/apache/logs/httpd.pid'

2. About line 90-ish, you'll see status) - you should make it look like this:

configtest)
$HTTPD -t
ERROR=$?
;;
status)
# $LYNX $STATUSURL | awk ' /process$/ { print; exit } { print } '
/usr/bin/wget $STATUSURL -q -O - | egrep '(Current|Restart|uptime)' | sed 's/<[/]*dt>//g'
if [[ -f $PIDFILE ]] ; then
echo "PID: " `cat $PIDFILE`
else
echo "No PID file."
fi
;;
fullstatus)
$LYNX $STATUSURL
;;
*)

This uses wget to grab the status (uptime, restart time, etc.) instead of Lynx, which as I said should be discontinued as a dependency. As you can see, Wget solves the problem easily.

Note that the bottom section for fullstatus) is not changed; I didn't need the fullstatus, if you do, feel free to improve on this text.

Enjoy!

had horrid trouble with Sybase Python interaction with escaping parameters. Problem was evinced by passing string ''' I'm ''' into an update statement with the simple:

'update table set field = %s where id = 1' % (instr)

The trouble with this is the sql string has a quote, and it dies.

Turns out, you have to escape by passing it into the c.execute(sql, paramHash)

code is as follows:

c=db.cursor()
sql = '''update Answer set answerText=@at where answerid=2'''
c.execute(sql, { '@at':'hello'} )
c.rowcount
c.close()

This solves the parameterization. CRUCIAL POINT: HAVE TO USE @VAR NOT JUST VAR.

FYI.

JQuery Autocomplete and the Back Button

JQuery's autocomplete plugin is great. No problems with it so far, easy to use, etc. However, one of my testers just found a problem:

1. Autocomplete is on Page Main. Enter text, choose option. Result function does redirect to page Other.
2. User hits back button on browser. Main shows up again.
3. form focus is still on autocomplete, so hit backspace and ... UG! space is cleared and now browser is trying to autocomplete something else into that field (usernames of all things).

So, after trying several things, including a window.onbeforeunload() method and clearing the autocompleted field, found that problem is solved by CHANGING FOCUS AWAY AND BACK.

Yes, it's that simple. Main's document ready function is called again after the user comes back using back button (thank goodness), so I just put a pair of lines in:

$("#othertable").focus();
$("#orgName").focus();

and it was fixed.

JQuery: TableSorter AJAX Zebra Fix

Just had a day of yuckky dealing with a 'bug' / nonfeature of JQuery's Tablesorter dealing with an AJAX call. Problem was that once I make the ajax call, the zebra striping goes all to hell.

This was due, it turns out, to 2 things:
1. I had a hidden/openable div in one of the table cells;
2. I didn't have the update call after the ajax return.

IMPORTANT: TO SEE FULL CODE, CUT AND PASTE. STUPID LAYOUT OF THIS BLOG. The text/code is really there, the viewable div below prevents it from displaying. Just cut and paste the code fragments below to see the full code...

So, step one:

function actionCount() {
   $.ajaxSetup({
       cache:  false,
       type:   "POST",
       success:  function(html) {
           $("#myprojectstable").trigger("update");
           // console.log("Inside Success function..");
       }
   });
   var orgid = $("#orgID").val();
   var projidlist = <%= self.fields.projidlist %>;
   $.post("ActionsCountJSON", { projids: projidlist }, function(raw){
       var rawData = raw['resultSet'];
       var rawList = raw['resultListing'];
       for (f in rawData) {
           var divloc = "#pActionsProjid" + f;
           var divobj = $(divloc);
           divobj.html(rawData[f]);
           divloc = "#pActionDataProjid" + f;
           var divobj = $(divloc);
           divobj.html(rawList[f]);
       };   
       $("#myprojectstable").trigger("sorton", [0,0]);
       $("#myprojectstable").trigger("applyWidgets");
       $("#myprojectstable").trigger("update");
       //console.log("done with AJAX POST.");
       //stripe("myprojectstable");
   }, 'json');
}

Having the update there made the widget trigger after the ajax returned, a vital thing because otherwise tablesorter doesn't know the underlying data has changed. After I had this, it resorted after the ajax came back.

Next, I had a widgets: [zebra] in my document ready function, but this widget wasn't working right.

So, I replaced it with my own widget by specifying widgets: ['zebraKJR']
then creating that widget as:

$.tablesorter.addWidget(
{
    id: "zebraKJR",
    format: function(table) 
    {
        var even = false;
        // console.log("zebraKJR called.");
        // if arguments are provided to specify the colours              
        // of the even & odd rows, then use the them;                
        // otherwise use the following defaults:                     
        var evenColor = arguments[1] ? arguments[1] : "#fff";        
        var oddColor  = arguments[2] ? arguments[2] : "#cde4ff";         
                                                                 
        if(table.config.debug) { var time = new Date(); }
            
        //var tbody = $("tr:visible",table.tBodies[0]);
        // by definition, tables can have more than one tbody
        // element, so we'll have to get the list of child
        // <tbody>s 
        var tbodies = table.getElementsByTagName("tbody");
        // and iterate through them...
        // for (var h = 0; h < 1tbodies.length; h++) {
        for (var h = 0; h < 1; h++) {
            // find all the <tr> elements... 
            var trs = tbodies[h].getElementsByTagName("tr");
            // ... and iterate through them
            for (var i = 0; i < trs.length; i++) {
                if (trs[i].id != 'stripeMe') {
                    // console.log("SKIPPING thru tr's, h="+h+", i="+i+", len="+trs.length+", trs data:"+trs[i].id);   //innerHTML
                    continue;
                }
                // console.log("COLORING thru tr's, h="+h+", i="+i+", len="+trs.length+", trs data:"+trs[i].id);   //innerHTML
                
                trs[i].style.backgroundColor = even? evenColor : oddColor;
                // avoid rows that have a class attribute
                // or backgroundColor style
                if (true) { //(! hasClass(trs[i]) && ! trs[i].style.backgroundColor) {
                    // get all the cells in this row...
                    var tds = trs[i].getElementsByTagName("td");
                    // and iterate through them...
                    for (var j = 0; j < tds.length; j++) {
                        var mytd = tds[j];
                        // avoid cells that have a class attribute
                        // or backgroundColor style
                        if (true) { //(! hasClass(mytd) &&! mytd.style.backgroundColor) {
                            mytd.style.backgroundColor = even ? evenColor : oddColor;
                        }
                    }
                }
            // flip from odd to even, or vice-versa
            even =  ! even;
            }
        }
    }
});

That's it!

I also have a parser method for parsing text out of HTML tags:

// add parser through the tablesorter addParser method
$.tablesorter.addParser({
   // set a unique id
   id: 'noUrlSorter',
   is: function(s) {
       // return false so this parser is not auto detected
       return false;
   },
   format: function(s) {
       // format your data for normalization
       beforeString = trim(s);
       t1 = s;
       t2 = t1.replace(/\"/ig,"");
       t3 = t2.replace(/(<([^>]+)>)/ig,"");
       t4 = t3.toLowerCase();
       //alert(" t1="+t1+
       //      ",t2="+t2+
       //      ",t3="+t3+
       //      ",t4="+t4);
       //console.log("parsing, b4="+beforeString+", returning: "+t4);
       return t4
   },
   // set type, either numeric or text
   type: 'text'
});

diffdir

One thing Solaris does nicely is let you find the differences between directories and show 'em side by side so you can figure out what files are where.

I'm using Ubuntu (and Bash, like normal) and need to find a dir diff showing files in one dir and not in another, etc .

this is simply the command diff -q -r dir1 dir2.

However, I'm using source controlled directories, so this shows up with a whole mess of gall-durn .svn directories, etc. So I need to filter this out and an alias won't work.

Here's the code for my dirdiff or diffdir bash script:

#!/bin/bash
#--verbose
if (!(test -d $1))
then
echo "bad dir 1";
exit;
fi
if (!(test -d $2))
then
echo "bad dir 2";
exit;
fi
out=`diff -q -r $1 $2 | egrep -v svn `;
# echo "Result of diff: $out";
if [ -z "$out" ]
then
echo "Same";
else
echo "Difference between $1 and $2: "
echo "$out"
fi
# echo "Done";