FreeTDS Development Group

["Patrick Dunnigan" <patrick.dunnigan AT centivia.com>]

I am attempting to load a file into a TEXT field in a MS SQL Server database. 
I am using Michael Peppler's example in the DBD::Sybase documentation. 

Do the TEXT/IMAGE functions in DBD::Sybase work with FreeTDS? How about MS 
SQL?

Thanks, Patrick

>From the FreeTDS logs I am getting an error when DBD Sybase calls 
>syb_ct_send_data: 
writetext bulk  0x00000000000000000000000000000000 timestamp = 
0x0000000000000000 with log

DBD::Sybase::st syb_ct_send_data failed: Server message number=170 
severity=15 state=1 line=1 server=GAPRDDSCAT01 text=Line 1: Incorrect syntax 
near '0x00000000000000000000000000000000'. at test.pl line 54.

RH 7.3, Perl 5.8.6, DBI 1.47, DBD 1.05, FreeTDS 0.63RC9

The script (FreeTDS log below):
#!/usr/bin/perl -w
use DBI;
use Getopt::Long;
my %args;
GetOptions(\%args, '-U=s', '-P=s', '-S=s', '-D=s');
my $dbh = DBI->connect("dbi:Sybase:server=$args{S};database=$args{D}", 
$args{U}, $args{P},{RaiseError => 1}) or die "Can't connect: $DBI::errstr";
{RaiseError => 1}) or die "Can't connect: $DBI::errstr";

$dbh->{syb_do_proc_status} = 1;
  # update a database entry with a new version of a file:
  my $file = "xxx";
  my $size = -s $file;
  printf("size is %s\n",$size);
  # first we need to find the CS_IODESC data for the data
$dbh->do("set textsize 10240");
  $sth = $dbh->prepare("select file_id,file_text from script_audit_file_image 
where file_id = 1");
  $sth->execute;
  while($sth->fetch) {    # don't care about the data!
      $sth->syb_ct_data_info('CS_GET', 2);
  }
 # OK - we have the CS_IODESC values, so do the update:
  $sth->syb_ct_prepare_send();
  # Set the size of the new data item (that we are inserting), and make
  # the operation unlogged
  $sth->syb_ct_data_info('CS_SET', 2, {total_txtlen => $size, log_on_update 
=> 1});
#
  # open the file, and store it in the db in 1024 byte chunks.
  open(IN, $file) || die "Can't open $file: $!";
  while($size) {
      $to_read = $size > 1024 ? 1024 : $size;
      $bytesread = read(IN, $buff, $to_read);
      $size -= $bytesread;
        printf("bytesread = %d, buff is %s\n",$bytesread,$buff);  
        # Script makes it here, then dies on the next line, first 1024 chunk 
of the file
      $sth->syb_ct_send_data($buff, $bytesread);
  }
  close(IN);
  # commit the operation
  $sth->syb_ct_finish_send();
#END OF SCRIPT

... snippet ....>

7:32:17.404629                 rows_affected = 1
17:32:17.404645 ct_results() process_result_tokens returned 1 (type 4052)
17:32:17.404662 ct_results() results state = 2
17:32:17.404677 ct_res_info()
17:32:17.404693 ct_res_info(): Number of rows is 1
17:32:17.404709 ct_results()
17:32:17.404724 tds_process_result_tokens() state is COMPLETED
17:32:17.404739 ct_results() process_result_tokens returned 2 (type 4052)
17:32:17.404784 ct_command()
17:32:17.404829 ct_data_info() colnum -99999
17:32:17.405155 ct_send_data()
17:32:17.405197 tds_free_all_results()
17:32:17.405236 tds_put_string converting 90 bytes of "writetext bulk  
0x00000000000000000000000000000000 timestamp = 0x0000000000000000 with log"
17:32:17.405262 tds_put_string wrote 180 bytes
17:32:17.405280 Sending packet
0000 01 01 00 bc 00 00 01 00-77 00 72 00 69 00 74 00 |...¼.... w.r.i.t.|
0010 65 00 74 00 65 00 78 00-74 00 20 00 62 00 75 00 |e.t.e.x. t. .b.u.|
0020 6c 00 6b 00 20 00 20 00-30 00 78 00 30 00 30 00 |l.k. . . 0.x.0.0.|
0030 30 00 30 00 30 00 30 00-30 00 30 00 30 00 30 00 |0.0.0.0. 0.0.0.0.|
0040 30 00 30 00 30 00 30 00-30 00 30 00 30 00 30 00 |0.0.0.0. 0.0.0.0.|
0050 30 00 30 00 30 00 30 00-30 00 30 00 30 00 30 00 |0.0.0.0. 0.0.0.0.|
0060 30 00 30 00 30 00 30 00-30 00 30 00 20 00 74 00 |0.0.0.0. 0.0. .t.|
0070 69 00 6d 00 65 00 73 00-74 00 61 00 6d 00 70 00 |i.m.e.s. t.a.m.p.|
0080 20 00 3d 00 20 00 30 00-78 00 30 00 30 00 30 00 | .=. .0. x.0.0.0.|
0090 30 00 30 00 30 00 30 00-30 00 30 00 30 00 30 00 |0.0.0.0. 0.0.0.0.|
00a0 30 00 30 00 30 00 30 00-30 00 20 00 77 00 69 00 |0.0.0.0. 0. .w.i.|
00b0 74 00 68 00 20 00 6c 00-6f 00 67 00             |t.h. .l. o.g.|

17:32:17.407982 Received header
0000 04 01 00 be 00 34 01 00-                        |...¾.4..|

17:32:17.408043 Received packet
0000 aa aa 00 aa 00 00 00 01-0f 43 00 4c 00 69 00 6e |ªª.ª.... .C.L.i.n|
0010 00 65 00 20 00 31 00 3a-00 20 00 49 00 6e 00 63 |.e. .1.: . .I.n.c|
0020 00 6f 00 72 00 72 00 65-00 63 00 74 00 20 00 73 |.o.r.r.e .c.t. .s|
0030 00 79 00 6e 00 74 00 61-00 78 00 20 00 6e 00 65 |.y.n.t.a .x. .n.e|
0040 00 61 00 72 00 20 00 27-00 30 00 78 00 30 00 30 |.a.r. .' .0.x.0.0|
0050 00 30 00 30 00 30 00 30-00 30 00 30 00 30 00 30 |.0.0.0.0 .0.0.0.0|
0060 00 30 00 30 00 30 00 30-00 30 00 30 00 30 00 30 |.0.0.0.0 .0.0.0.0|
0070 00 30 00 30 00 30 00 30-00 30 00 30 00 30 00 30 |.0.0.0.0 .0.0.0.0|
0080 00 30 00 30 00 30 00 30-00 30 00 30 00 27 00 2e |.0.0.0.0 .0.0.'..|
0090 00 0c 47 00 41 00 50 00-52 00 44 00 44 00 53 00 |..G.A.P. R.D.D.S.|
00a0 43 00 41 00 54 00 30 00-31 00 00 01 00 fd 02 00 |C.A.T.0. 1....ý..|
00b0 fd 00 01 00 00 00      -                        |ý.....|

17:32:17.408211 processing result tokens.  marker is  aa(ERROR)
17:32:17.408312 tds_process_default_tokens() marker is aa(ERROR)
17:32:17.408339 tds_get_string: reading 134 from wire to give 67 to client.
17:32:17.408379 tds_get_string: reading 24 from wire to give 12 to client.
17:32:17.408410 ct_con_props() action = CS_GET property = 13
17:32:17.408431 fetching userdata 0x82d3678
17:32:17.408467 processing result tokens.  marker is  fd(DONE)
17:32:17.408485 tds_process_end: more_results = 0
                was_cancelled = 0
                error = 1
                done_count_valid = 0
17:32:17.408522 tds_process_end() state set to TDS_IDLE
17:32:17.409254 ct_close()