PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: "Pham, Tuan A" <Tuan dot A dot Pham at siemenscom dot com>
Subject: RE: help with inserting text
Date: Mar 13 1998 5:33PM

Philip,
  I tried with basic insert, but many inserted rows with text larger
than 2K syntactically failed. However,I didn't have the problem when
using dbwritetext() the way Michael demonstrated. 
  Just want to let you know . And thanks to Michael.
Regards,
Tuan
>-----Original Message-----
>From:	philip mikal [SMTP:philipm@nafohq.hp.com]
>Sent:	Wednesday, March 11, 1998 2:16 PM
>To:	SYBPERL-L@trln.lib.unc.edu
>Subject:	RE: help with inserting text
>
>Tuan:
>
>I believe that you can insert up to 128k of text using a standard insert.  If
>your strings are longer that that, someone else will have to answer, as I 
>have not used dbwritetext(). 
>
>Regards,
>
>
>Philip
>
>
>> 
>> Hi,
>>   Can you give an example of using dbwritetext() to put long text to
>> TEXT columns as a replacement of this code sample ?
>> Tuan Pham
>> 
>> 
>> >-----Original Message-----
>> >From:	philip mikal [SMTP:philipm@nafohq.hp.com]
>> >Sent:	Wednesday, February 18, 1998 4:48 PM
>> >To:	SYBPERL-L@trln.lib.unc.edu
>> >Subject:	Re: help with inserting text
>> >
>> >Hello again!
>> >
>> >FYI, after much frustration and lots of help from this list, the final
>> >solution
>> >for my problem is listed below.  I would have been lost without everyone's
>> >help - especially Michael, for recommending the qq() method, which was the
>> >final element to functionality.
>> >
>> >Regards,
>> >
>> >
>> >Philip Mikal
>> >
>> >------------------------------
>> >sub loadindexdata {
>> >
>> >open (LINKS, "index.data") || &CgiDie ("I am sorry, but I was
>> >	not able to open the link database.   The value I have is
>> >	index.data. Would you please check the path and the permission.");
>> >
>> >while ()
>> >	{
>> >	$_ = $dbh->dbsafestr($_);
>> >	($link_title, $id, $unique_id, $link_url, $link_description,
>>$author_email,
>> >$author_name) = split (/\|/, $_);
>> >	$select = qq(insert into link_table values ("$link_title", $id,
>>$unique_id,
>> >"$link_url", "$link_description"));
>> >	$dbh->dbcmd("$select");
>> >	$dbh->dbsqlexec;
>> >	$select = qq(insert into link_owners_table values ($unique_id,
>> >"$author_name", "$author_email"));
>> >	$dbh->dbcmd("$select");
>> >	$dbh->dbsqlexec;
>> >	}
>> >close (LINKS);
>> >}
>> >
>> >------------------------------
>> >
>> >> 
>> >> cchrysos@ovid.com wrote:
>> >> > 
>> >> > It looks like Philip forgot to surround the character strings with
>> >>quotes.
>> >> > I believe single quotes will work without confusing Perl.
>> >> > 
>> >> > My example:
>> >> > 
>> >> > $select = "insert into link_table values ('$link_title', '$id',
>> >> > '$unique_id', '$link_url', '$link_description')"
>> >> > 
>> >> > Quoting isn't necessary for numeric datatypes like int, float,
>>decimal,
>> >>and
>> >> > so on. So, in this example, if the column for $id is an int, it
>>doesn't
>> >> > need quotes.
>> >> 
>> >> Note that T-SQL becomes confused if the strings being inserted
>> >> (say $link_title) itself contains the ' or " character.
>> >> 
>> >> To minimize this problem I usually use the qq() quoting operator
>> >> to quote the entire string, and then use " to quote strings in the
>> >> insert statement. Any string that is to be inserted that
>> >> contains the " character needs to be checked, and the " duplicated, eg
>> >> 
>> >> 	$link_title =~ s/"/""/g;
>> >> 
>> >> Michael
>> >> -- 
>> >> Michael Peppler       -||-  Data Migrations Inc.
>> >> mpeppler@datamig.com  -||-  http://www.mbay.net/~mpeppler
>> >> 
>> >
>> 
>From:	Michael Peppler [mpeppler@mbay.net]
>Sent:	Wednesday, March 11, 1998 2:01 PM
>To:	SYBPERL-L@trln.lib.unc.edu
>Subject:	Re: help with inserting text
>
Pham, Tuan A wrote:
> 
> Hi,
>   Can you give an example of using dbwritetext() to put long text to
> TEXT columns as a replacement of this code sample ?

Assume this table my_table(id int, the_text text).

# First insert the rest of the data to the table:
$dbh->dbcmd('insert my_table values(1, "")');
$dbh->dbsqlexec; $dbh->dbresults;

# now select the (empty) the_text field - we need to grab a 
# 'text' pointer
$dbh->dbcmd('select the_text from my_table where id= 1');
$dbh->dbsqlexec; $dbh->dbresults;
$dbh->dbnextrow;   # only one row - and we don't actually need the data

# do the writetext:
$dbh->dbwritext('my_table.the_text', $dbh, 1, "This is the text I want
to insert", TRUE);

See the manual (both Sybperl and Sybase OpenClient) for an explanation
of what the params mean here.

Michael


> Tuan Pham
> 
> >-----Original Message-----
> >From:  philip mikal [SMTP:philipm@nafohq.hp.com]
> >Sent:  Wednesday, February 18, 1998 4:48 PM
> >To:    SYBPERL-L@trln.lib.unc.edu
> >Subject:       Re: help with inserting text
> >
> >Hello again!
> >
> >FYI, after much frustration and lots of help from this list, the final
> >solution
> >for my problem is listed below.  I would have been lost without everyone's
> >help - especially Michael, for recommending the qq() method, which was the
> >final element to functionality.
> >
> >Regards,
> >
> >
> >Philip Mikal
> >
> >------------------------------
> >sub loadindexdata {
> >
> >open (LINKS, "index.data") || &CgiDie ("I am sorry, but I was
> >       not able to open the link database.   The value I have is
> >       index.data. Would you please check the path and the permission.");
> >
> >while ()
> >       {
> >       $_ = $dbh->dbsafestr($_);
> >       ($link_title, $id, $unique_id, $link_url, $link_description,
$author_email,
> >$author_name) = split (/\|/, $_);
> >       $select = qq(insert into link_table values ("$link_title", $id,
$unique_id,
> >"$link_url", "$link_description"));
> >       $dbh->dbcmd("$select");
> >       $dbh->dbsqlexec;
> >       $select = qq(insert into link_owners_table values ($unique_id,
> >"$author_name", "$author_email"));
> >       $dbh->dbcmd("$select");
> >       $dbh->dbsqlexec;
> >       }
> >close (LINKS);
> >}
> >
> >------------------------------
> >
> >>
> >> cchrysos@ovid.com wrote:
> >> >
> >> > It looks like Philip forgot to surround the character strings with
> >>quotes.
> >> > I believe single quotes will work without confusing Perl.
> >> >
> >> > My example:
> >> >
> >> > $select = "insert into link_table values ('$link_title', '$id',
> >> > '$unique_id', '$link_url', '$link_description')"
> >> >
> >> > Quoting isn't necessary for numeric datatypes like int, float, decimal,
> >>and
> >> > so on. So, in this example, if the column for $id is an int, it doesn't
> >> > need quotes.
> >>
> >> Note that T-SQL becomes confused if the strings being inserted
> >> (say $link_title) itself contains the ' or " character.
> >>
> >> To minimize this problem I usually use the qq() quoting operator
> >> to quote the entire string, and then use " to quote strings in the
> >> insert statement. Any string that is to be inserted that
> >> contains the " character needs to be checked, and the " duplicated, eg
> >>
> >>      $link_title =~ s/"/""/g;
> >>
> >> Michael
> >> --
> >> Michael Peppler       -||-  Data Migrations Inc.
> >> mpeppler@datamig.com  -||-  http://www.mbay.net/~mpeppler
> >>
> >

-- 
Michael Peppler         -||-  Data Migrations Inc.
mpeppler@datamig.com    -||-  http://www.mbay.net/~mpeppler
Int. Sybase User Group  -||-  http://www.isug.com