Re: SQL Anywhere 11

Brian Johnson wrote:
> Thanks for the quick reply Daniel.  As we don't use the freetds.conf
> file for our connections, and we use DNS-less connections, I'm trying to
> construct the connection string to include the dataserver name as well
> as it's IP in it, but can't seem to dig up the information.  I'm looking
> at:
> 
> http://www.freetds.org/userguide/dsnless.htm
> 
> And it shows an example with a dataserver entry in freetds.conf, and one
> using a freetds.conf-less connection that has a server entry.  Would I
> simply add them both and come out with something like:

You can set the environment variable ASA_DATABASE to be the name of your
database.  That causes FreeTDS to put that string in the appropriate field
of the login packet.  

Attached is a Perl script to help show what's going on and what to look
for.  Create a TDSDUMP log, and run this script against it.  It finds the
login packet in the log file, parses it, and prints the fields to standard
output.  You should see:

  30  server_name                     SYBASE
   1  server_name_length              6

except your database name instead of "SYBASE".  

N.B.  The field is called "server_name" but ASA uses it for the database
name.  Ask them, I just work here!  ;-)

== script ==
#! /usr/bin/perl -w

$prior_offset = 0;
while (<DATA>) {
	next unless /^\s*\d/;
	chomp;
	my ($offset, $type, $name) = split;
	my $size = -1;

	$prior_offset = $offset;

	my $char;
	if ($type =~ /CHAR\[(\d+)\]/) {
		$char = 'a';
		$size = $1;
	}
	if ($type =~ /INT(\d+)/) {
		$size = 1; $char = 'x';
		$char = 'c' if $1 == 8;
		$char = 's' if $1 == 16;
		$char = 'l' if $1 == 32;
		die if $char eq 'x';
	}
	if ($type =~ /^\?/) {
		$char = 'a';
		$name =~ /\[(\d+)\]/;
		$size = $1 or die;
	}
	
	die qq(no char in type "$type" in $_) unless $char;
	die qq(no size in type "$type" in $_) if     $size == -1;

	my $repeat = $size == 1? '' : $size;
	$pack_as .= "(H2)$repeat";
	$unpack_as .= "$char$repeat";

	push @names, $name;
	push @sizes, $size;
} 

die "no names" unless @names;
die "no sizes" unless @sizes;

print STDERR $pack_as, $/;
print STDERR $unpack_as, $/;

while (<>) {
	last if /^0000 02/;
}
goto Start;

while (<>) {
	Start:
	s/^.+-// if /^0000 02/; # trim off leading header bytes
	s/^[[:xdigit:]]{4} //;
	s/ \|.+$//;
	s/-/ /;
	
	if (/Sending packet/) {
		$ipacket++;
		next;
	}
	
	if( /^\s*$/ ) {
		last if $ipacket;
		next;
	}
	
	push @bytes, split;
}

die unless @bytes;

$data = pack $pack_as, @bytes or die qq(cannot pack with "$pack_as"\n);
@fields = unpack $unpack_as, $data;

for( $i=0; $i < @names; $i++ ) {
	$fields[$i] =~ s/\0+$//;
	$fields[$i] = "" unless defined($fields[$i]);
	$fields[$i] = "(ignored)" if ($names[$i] =~ /^magic|^\?/);

	printf( "%4d  %-30s  %-30s\n", $sizes[$i], $names[$i], $fields[$i] );
	die unless defined($sizes[$i]);
	die unless defined($names[$i]);
}

__DATA__

byte   var type    description
------------------------------
   8   CHAR[30]    host_name
  38   INT8        host_name_length
  39   CHAR[30]    user_name
  69   INT8        user_name_length
  70   CHAR[30]    password
 100   INT8        password_length
 101   CHAR[30]    host_process
 131   INT8        host_process_length
 132   ?           magic1[6]          /* mystery stuff */
 138   INT8        bulk_copy 
 139   ?           magic2[9]          /* mystery stuff */
 148   CHAR[30]    app_name
 178   INT8        app_name_length
 179   CHAR[30]    server_name
 209   INT8        server_name_length
 210   ?           magic3[1]          /* 0, don't know this one either */
 211   INT8        password2_length
 212   CHAR[30]    password2
 242   CHAR[223]   magic4
 465   INT8        password2_length_plus2
 466   INT16       major_version      /* TDS version */
 468   INT16       minor_version      /* TDS version */
 470   CHAR[10]    library_name       /* "Ct-Library" or "DB-Library" */
 480   INT8        library_length
 481   INT16       major_version2     /* program version */
 483   INT16       minor_version2     /* program version */
 485   ?           magic6[3]          /* ? last two octets are 13 and 17
*/
                                      /* bdw reports last two as 12 and 16
here  */
                                      /* possibly a bitset flag  */
 488   CHAR[30]    language           /* e.g. "us-english" */
 518   INT8        language_length
 519   ?           magic7[1]          /*  mystery stuff */
 520   INT16       old_secure         /* explanation? */
 522   INT8        encrypted          /*  1 means encrypted all password
fields blank */
 523   ?           magic8[1]          /*  no clue... zeros */
 524   CHAR[9]     sec_spare          /* explanation? */
 533   CHAR[30]    char_set           /* e.g. "iso_1" */
 563   INT8        char_set_length
 564   INT8        magic9[1]          /* 1 */ 
 565   CHAR[6]     block_size         /*  in text */
 571   INT8        block_size_length 
 572   ?           magic10[25]        /* lots of stuff here...no clue */
== tpircs ==