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: Tim_Green at mercer dot com
Subject: Re[2]: help with inserting text
Date: Feb 18 1998 10:32PM

Philip:

First, you need to quote your strings in your insert statement.  So:
     
$select = "insert into link_table 
 values ($link_title, $id, $unique_id,$link_url, $link_description)";

should be:

$select = "insert into link_table 
 values ('$link_title', $id, $unique_id, '$link_url', '$link_description')";

Second, since your data contains both double and single quotes, your have to 
escape the quote in the data that corresponds to the one you use to enclose your
string; the single quote in my example above.  DB Library has a function for 
this called dbsafestr(), though I've never had to use it and am not sure if 
Sybperl has included it.  You can check the man page, or just do it yourself 
with the following.

while ()
{
   s/'/''/g;   #  Change every quote char to two quote characters.
   ($link_title, $id, $unique_id, $link_url, 
    $link_description, $author_email, $author_name) = split (/\|/, $_);

BTW, consider the advice Michael gave.  If your strings don't need to exceed 255
characters, by all means change the datatype to varchar.  Not only will it save 
a good deal of space (text datatypes allocate 2k data pages) and be faster, they
are much easier to manipulate.  You can't concatenate or append to text fields 
within SQL, or index them.

Good luck,
Tim


______________________________ Reply Separator _________________________________
Subject: Re: help with inserting text
Author:  SYBPERL-L@trln.lib.unc.edu at uucp
Date:    2/18/98 11:06 AM


Tim:
     
Thanks for the response.  When I use the following subroutine to insert 
my data, I get errors regarding syntax errors, that seem to be caused by 
characters such as ("',@).  I'm sure I'm just forgetting something.
     
Help!
     
Philip
     
-----------------
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 ()
        {
        ($link_title, $id, $unique_id, $link_url, $link_description,
$author_email, $author_name) = split (/\|/, $_);
        $select = "insert into link_table values ($link_title, $id, $unique_id,
$link_url, $link_description)";
        $dbh->dbcmd("$select");
        $dbh->dbsqlexec;
        $select = "insert into link_owners_table values ($unique_id,
`$author_name`, `$author_email')";
        $dbh->dbcmd("$select");
        $dbh->dbsqlexec;
        }
close (LINKS);
     
> 
>      If your text strings aren't a great deal longer than you have here, 
>      then using dbwritetext is overkill and probably slower.  It's 
>      definitely more complicated.  That particular function is meant for 
>      dealing with large text strings.  
>      
>      For data of the variety you have here, simply use a basic insert 
>      statement, which can populate text datatype columns just like any 
>      other, and can handle data up to approx. 128k.
>      
>      Good luck,
>      
>      Tim Green                                        Tim_Green@mercer.com 
>      Administrative Solutions Group
>      "An ADP/Mercer Alliance"
> 
> 
> ______________________________ Reply Separator 
_________________________________
> Subject: help with inserting text
> Author:  SYBPERL-L@trln.lib.unc.edu at uucp 
> Date:    2/17/98 6:44 PM
> 
> 
> Hello:
>      
> I am a bit confused on how one inserts text into Sybase using DBlib. I have 
> data that I am trying to load into my database that looks like:
>      
> µ-ziq|24|129|http://www.caroline.com/astralwerks/muziq/|"America's 
premiere
>  electronic label, Astralwerks Records, presents the 
µ-ziq"|astralwerks@car
> oline.com|Astralwerks
>      
> I am taking this data from a text file, spliting it apart (pipe delimited, 
"|") 
> into variables and attempting to insert it into my database.  All my columns 
> are text datatypes except for two, integers, which in the above example, would
     
> be "24" and "129".
>      
> I believe I am to use dbwritetext, but I do not understand it properly. The 
> examples in the man pages are confusing to me, e.g., how does one determine 
> the column number.
>      
> If anyone could explain how to accomplish this, it would be greatly 
> appreciated.
>      
> Regards,
>      
>      
> Philip
> philipm@nafohq.hp.com
>      
>