Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

dbd:Sybase bit scramble

by CaptainDaddy (Initiate)
on Mar 21, 2013 at 01:22 UTC ( #1024642=perlquestion: print w/replies, xml ) Need Help??
CaptainDaddy has asked for the wisdom of the Perl Monks concerning the following question:

Recently I've had trouble putting files larger than a certain size (around 3kb) into a sql server. I've been using unixODBC, DBD:ODBC, and FreeTDS.
I've noticed that a similar issue was posted on CPAN as a bug for the latest DBC:ODBC, and didn't understand the fix that the poster found.
I recently switched from DBD:ODBC to DBD:Sybase, and after what I felt was an unnescessary use of CONVERT (see my thread 'dbd odbc sql big file blues') I could insert very large varbinaries.
However when I try to insert real files using this method I get a warning that there is an error converting characters and the files get stomped on. This seems odd to me since I use

binmode(FILE); $fdata=read(FILE,$filelength);

before I bind the data for insert. I'd like to use a solution with freeTDS for obvious reasons; it was suggested that I use the easysoft driver in the above mentioned thread and I will try that with dbd:odbc and post the results there, but this seems a problem separate from freeTDS. I'll be grateful for any help in this.

Replies are listed 'Best First'.
Re: dbd:Sybase bit scramble
by mje (Curate) on Mar 21, 2013 at 09:55 UTC

    Just a small correction to your reference to dbd odbc sql big file blues. I didn't (and I don't think anyone else did either) recommend you used the Easysoft SQL Server ODBC Driver I simply showed you that your code worked with that driver as part of my evidence that something (possibly freeTDS) was broken.

    Also, could you point me at this "similar issue was posted on CPAN as a bug for the latest DBC:ODBC".

        FWIW I don't think the issue you hit and the one you point to are related. As you may have seen if you looked down the whole log for that bug I rejected it as not a DBD::ODBC but the reporter keeps coming back and reopening it.

      Code

      # $host='...'; $port='...'; $user = "..."; $pass = "..."; $dsn="dbi:Sybase;server=dbhDB;database=...;"; $dbh= DBI->connect("$dsn",$user,$pass) or die "can't connect"; $fileTableName="fileTable"; $cmd="SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE T +ABLE' AND TABLE_NAME='$fileTableName\'"; $fetchcmd= $dbh->prepare($cmd); $fetchcmd->execute(); if(@row= $fetchcmd->fetchrow_array){ print "the file table is there!!!\n"; }else { $createCmd=$dbh->prepare("create table $fileTableName (fileCol +umn VARBINARY(MAX) not null)"); $createCmd->execute() or die "can't create table"; $createCmd->finish(); } $fetchcmd->finish(); $insertcmd= $dbh-> prepare("insert into $fileTableName (fileColumn) va +lues (CONVERT(varbinary(max),?))"); open FILE,"<","./a.b" or die "file"; binmode(FILE); $filelength = -s FILE; read(FILE,$fcontents,$filelength); print"did"; $insertcmd->bind_param(1,$fcontents,DBI::SQL_VARBINARY); $insertcmd->execute() or print" not "; $insertcmd->finish(); $sz=length($insert); print " succeed. size: $sz\n"; close(FILE); $dbh->disconnect;

      results

      >perl sybasefiletrial.pl the file table is there!!! DBD::Sybase::st execute failed: OpenClient message: LAYER = (0) ORIGIN + = (0) SEVERITY = (9) NUMBER = (98) Server dbhDB, database Message String: Error converting characters into server's character se +t. Some character(s) could not be converted did succeed. size: 0
      I probably am missing something obvious here; help will be apprecieated.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1024642]
Approved by Corion
help
Chatterbox?
[Corion]: usemodperl: No, asking is not unreasonable. Insisting that it must always keep working for you is unreasonable.
[Corion]: But again, you haven't explained why your users need to download, or why they need to download via http, or why you can't host the SSL-stripper yourself.
[choroba]: Wait a second. Does shutting down search.cpan.org mean the cpan clients can no longer use their urllist?
[usemodperl]: i'm under no obligation to explain myself mommy, and i'm not insisting, just asking a simple question
[Veltro]: So, I'm confused on the matter actually. There are locked-down computers, but they need updates. How does that work? Isn't that contradictary?
[usemodperl]: EXACTLY
[usemodperl]: and the https only situation makes it even worse
[Veltro]: How is that 'exactly'. Locked-down means you don't change or update. So why do you need to access CPAN?
[Corion]: choroba: Heh - I think that's an interesting situation - you can't bootstrap a vanilla Perl then via CPAN, as IO::Socket::SSL is not in core, but also can't be installed from a cpan client. You need a wget/curl with SSL built-in to do that :)
[usemodperl]: i was thinking of forcing something like archive.org to proxy from meta but they seem all https too :-/

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (8)
As of 2018-06-24 16:41 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Should cpanminus be part of the standard Perl release?



    Results (126 votes). Check out past polls.

    Notices?