Data Manipulation Statements and PurePerl MySQL API
Using the PurePerl MySQL API – Part 3
Writing a Perl Module
Foreword: In this part of the series, I show you how data manipulation statements can be used with the PurePerl MySQL API
By: Chrysanthus Date Published: 28 Jan 2015
Introduction
Examples of Data Manipulation Statements
There are a good number of data manipulation statements. The main data manipulation statements are the SELECT, INSERT and UPDATE statements. I will use these statements to manipulate the pet table already created in the PetStore database, in the previous part of the series. You use the other data manipulation statements in a similar way, as I use these three: as argument to the query function. You should try all the code samples of this series.
The query() Function Syntax
The query function syntax is:
Boolean query("SQLstr");
You type a semicolon at the end of the query() function call and not at the end of the SQL statement. The SQL statement is typed in quotes without the ending semicolon. The function returns 1 if it succeeds or 0 if it fails. Any positive message developed is got from the variable, Message accessible from your script by, $Mysql::Message . Any error message is got by accessing $Mysql::Error . Any result set is accessible from your script by, $Mysql::Result[$row_index] – see later.
Inserting a Row
After connecting to the MySQL server and selecting the database, you can insert a row into a table in the database as the following code shows:
my $ins = "INSERT INTO pet (name, owner, species, sex, birth, death) VALUES ('Nail','Mary','hamster','f','2009-03-30',NULL)";
if (Mysql::query($ins) != 1)
{
print $Mysql::Error_msg, "\n";
}
If you have more than one row to insert, call the query() function more than once.
You can create a text file called, pet.txt containing one record per line, with values separated by tabs, and given in the order in which the columns are listed in the CREATE TABLE statement. For missing values (such as unknown sexes or death dates for animals that are still living), you use NULL values. To represent these in your text file, use \N (backslash followed by capital-N).
The text file to create and load is:
Boun John cat f 2003-02-04 \N
Claws Susan cat m 2004-03-17 \N
Buffy Harold dog f 1999-05-13 \N
Fang Benny dog m 2000-08-27 \N
Bows Diane dog m 1989-08-31 2005-07-29
Chirpy Susan bird f 2008-09-11 \N
Singer Susan bird \N 2007-12-09 \N
Fat Benny snake m 2006-04-29 \N
The white spaces between values in a row should be achieved by pressing the tab key on the keyboard.
The basic statement syntax to load the file values into their corresponding positions in the database table, from a directory in the server is:
LOAD DATA INFILE 'Drive:/path/pet.txt' INTO TABLE pet LINES TERMINATED BY '\r\n'
The portion, “LINES TERMINATED BY '\r\n'” depends on the text editor you are using. For some editors you have to omit it.
I prepared the above file and saved in the root directory (localhost). The following code segment was used to copy the data into the table.
my $lo = "LOAD DATA INFILE 'C:/pet.txt' INTO TABLE pet LINES TERMINATED BY '\\r\\n'";
if (Mysql::query($lo) != 1)
{
print $Mysql::Error_msg, "\n";
}
else
{
print $Mysql::No_Warnings, "\n";
print $Mysql::Message, "\n";
}
I tried the code and I had the following for successful loading:
Records: 8 Deleted: 0 Skipped: 0 Warnings: 0
This OK message is for the last command. So it does not include the INSERTED first row. The number of warnings is also for the last command.
With the LOAD DATA INFILE, it is possible to have some small complaints, even though the OK packet will be returned. In this case the user should know the number of warnings and any related message. That is why you have the two print lines in the true (1) block above. If no line was loaded, the false (0) block for “print $Mysql::Error_msg, "\n";” would have been executed to give you the error message.
To update, you put the SQL update statement, as argument to the query() function.
The following code updates the row of the pet whose name is, Bows.
my $up = "UPDATE pet SET birth = '1999-08-31' WHERE name = 'Bows'";
if (Mysql::query($up) != 1)
{
print $Mysql::Error_msg, "\n";
}
else
{
print $Mysql::No_Records_Changed, "\n";
print $Mysql::Message, "\n";
}
I tried the code and I had:
1
Rows matched: 1 Changed: 1 Warnings: 0
Even though the query was successful, the OK reply had the number of records changed and some message, as printed by the true (1) block above. The number of records changed, is for the last command.
Selecting
To select rows, you type the SQL statement as argument in the query statement. The following code segment illustrates this:
my $sel = "SELECT * FROM pet WHERE species = 'snake' OR species = 'bird'";
if (Mysql::query($sel) != 1)
{
print $Mysql::Error_msg, "\n";
}
else
{
print $Mysql::Result[1]{'owner'}, "\n";
}
What the SELECT and similar queries receive, is a result set. The else block of this code segment accesses and prints out the owner of the pet of the second row of the result set. I explain how to access data from the result set in the next part of the series.
That is it for this part of the series. We stop here and continue in the next part.
Chrys
Perl Mailsend
PurePerl MySQL API
Perl Course - Professional and Advanced
Major in Website Design
Web Development Course
Producing a Pure Perl Library
MySQL Course
BACK NEXT