Perl learning materials: Page 1/4

Null Value Judgment
$ T {type1id }=$ $ Pref {DBH}-> selectrow_array ("select type1id from enq1 where id =
3 ");
If ($ t {type1id} = 0 ){
Print "type1id is null \ n ";
}
==> This statement is incorrect if it is not a numerical value. Special value.
If (length ($ t {type1id}) = 0 ){
Print "type1id is null \ n ";
}
==> If null, this statement is incorrect.
If @ rec contains null, the following operation produces an error message.
$ T {line1} = join ('', @ REC );

($ T {old1}, $ t {new1p}, $ t {new1q}) = $ self-> DBH-> selectrow_array ("select
Type1id, partsid, qty from enq1 where id = $ t {enq1_id }");
91 ==> if ($ t {old1} = 0 ){
--------------------------------------------------
[Error] [client 127.0.0.1] use of uninitialized value in numeric eq (=)
./Pro/mscenq1.pl line 91, <config> line 11 .,
--------------------------------------------------
How to determine whether a project value is null (unsolved)
Solution! Put a constant (such as "B") at the first insert ")
Origin =>
637 ==>$ t {nu1 }=$ self-> DBH-> selectrow_array ("select parts_unit from parts_nu
Where id = $ t {nuid1 }");
--------------------------------------------------
[Wed May 14 17:27:51 2008] [Error] [client 127.0.0.1] DBD: mysql: DB
Selectrow_array failed: You have an error in your SQL syntax; check the manual
That corresponds to your MySQL Server version for the right syntax to use near
''At line 1 at./Pro/mscenq1.pl line 637, <config> line 11., Referer:
--------------------------------------------------
Consider the situation where $ t {nuid1} does not exist.

Consider id = C
591 =>
@ {$ T {p1 }}= $ self-> DBH-> selectrow_array ("select * from $ t {ptable}
Where id = $ t {pid1 }");
--------------------------------------------------
[Error] [client 127.0.0.1] DBD: mysql: DB selectrow_array failed: Unknown
Column 'C' in 'where clause' at./Pro/mscenq1.pl line 591, <config> line 11 .,
Referer:
--------------------------------------------------
Consider the situation where $ t {pid1} = 'C'
If ($ # {$ t {pid_list }== 0 & $ t {pid_list} [0] EQ 'C '){
Next;
}
Copy the subroutine of a project
Use strict;
Use dBi;
# Connecting to a database
My (% T, $ N, @ override, @ rec, $ Pref );
Print "this is test3.pl. \ n ";
# Connecting to a database
$ Pref {DSN} = "DBI: mysql: host = localhost; database = cookbook ";
$ Pref {DBH} = DBI-> connect ($ Pref {DSN}, "cbuser", "cbpass") or die "cannot
Connect to server \ n ";
$ Pref {DBH}-> do ("set names utf8 ");
If (! $ Pref {DBH }){
Print "SQL read error! \ N ";
Exit;
}
$ Pref {table} = 'enq2 ';
$ Pref {oldid} = 4;
($ Pref) = copy_one ($ Pref );
# Shut down the database
$ Pref {DBH}-> disconnect;
# Copy a project
Sub copy_one {
My ($ Pref) = @_;
My (% T, @ rec, $ N );

# Retrieve Columns
$ T {something }=$ $ Pref {DBH}-> prepare ("show columns from $ Pref {table }");
$ T {something}-> execute;
While (@ rec = $ t {something}-> fetchrow_array ){
Push (@ {$ t {columns }}, $ rec [0]);
}
$ T {something}-> finish;
# Retrieve data (remember a project that is not null)
@ {$ T {one }}=$ $ Pref {DBH}-> selectrow_array ("select * from $ Pref {table}
Where id = $ Pref {oldid }");

For $ n (1 .. $ # {$ t {one }}){
$ T {name }=$ t {columns} [$ N];
$ T {value }=$ t {one} [$ N];
If ($ t {value }){
$ T {value} = '"'. $ t {value }.'"';
Push (@ {$ t {names }}, $ t {name });
Push (@ {$ t {values }}, $ t {value });
}
}
$ T {name1} = join (',', @ {$ t {names }});
$ T {value1 }= join (',', @ {$ t {values }});

# Insert a new project
$ T {SQL} = 'insert'. $ Pref {table }.'(';
$ T {SQL}. = $ t {name1}. ') values (';
$ T {SQL}. = $ t {value1 }.')';

$ T {do }=$ $ Pref {DBH}-> do ($ t {SQL });

# Print "DO = $ t {do} \ n ";
Return ($ Pref );
}
# MySQL may have simple commands to execute the above operations. Already doneProgramPut it here.

--------------------------------------------------------------------------------
MySQL Operating Procedure 2
Return
--------------------------------------------------------------------------------
Do not allow repeated operations by ourref
$ T {enq1_id }=t t {q}-> param ("enq1_id ");
$ T {ourtrow_array ("select ourref from enq1 where id = $ t {enq1_id }");
# Obtain the enq1id data of all existing quo2 instances. If the same data exists, switch is not allowed.
# Enq1 and quo2 must be in a one-to-one relationship
# Retrieve all ourref
$ T {something }=$ self-> DBH-> prepare ("select enq1id from quo2 ");
$ T {something}-> execute;
While (@ rec = $ t {something}-> fetchrow_array ){
$ T {our1 }=$ self-> DBH-> selectrow_array ("select ourref from enq1 where id = $ rec [0]");
Push (@ {$ t {our1s }}, $ t {our1 });
}
$ T {something}-> finish;
$ T {our1_old} = join ('', @ {$ t {our1s }});
If ($ t {our1_old }!~ /$ T {ourrentnew }/){
$ T {SQL} = 'Update quo2 set enq1id = "';
$ T {SQL}. = $ t {enq1_id}. '"where id = "';
$ T {SQL}. = $ t {quo2_id }.'"';
$ T {do} = $ self-> DBH-> do ("$ t {SQL }");
}
Delete table content
Display the content of rows 309th to 362 in the table hull_no
Mysql> select * From hull_no where ID> = 309 and ID <= 362;
Delete hull_no from row 309th to row 362 of table hull_no
Mysql> Update hull_no set hull_no = "" where ID> = 309 and ID <= 362;
Query OK, 54 rows affected( 0.16 Sec)
Rows matched: 54 changed: 54 Warnings: 0
Delete the name from row 309th to row 362 of table hull_no
Mysql> Update hull_no set name = "" where ID> = 309 and ID <= 362;
Query OK, 54 rows affected( 0.01 Sec)
Rows matched: 54 changed: 54 Warnings: 0
Delete A table row
Mysql> show columns from quo2;
+ ----------- + --------- + ------ + ----- + --------- + ---------------- +
| FIELD | type | null | key | default | extra |
+ ----------- + --------- + ------ + ----- + --------- + ---------------- +
| ID | int (11) | no | pri | null | auto_increment |
| Time | date | Yes | null |
| Enq1id | int (11) | Yes | null |
| Originid | int (11) | Yes | null |
| Priceid | int (11) | Yes | null |
| Paymentid | int (11) | Yes | null |
| Delivery | text | Yes | null |
| Percent0 | int (11) | Yes | null |
| Percent | text | Yes | null |
| Price | text | Yes | null |
| Total | int (11) | Yes | null |
| Memo | text | Yes | null |
+ ----------- + --------- + ------ + ----- + --------- + ---------------- +
12 rows in SET (0.08 Sec)
Mysql> alter table quo2 drop enq1id;
Query OK, 6 rows affected (0.27 Sec)
Records: 6 duplicates: 0 Warnings: 0
Mysql> show columns from quo2;
+ ----------- + --------- + ------ + ----- + --------- + ---------------- +
| FIELD | type | null | key | default | extra |
+ ----------- + --------- + ------ + ----- + --------- + ---------------- +
| ID | int (11) | no | pri | null | auto_increment |
| Time | date | Yes | null |
| Originid | int (11) | Yes | null |
| Priceid | int (11) | Yes | null |
| Paymentid | int (11) | Yes | null |
| Delivery | text | Yes | null |
| Percent0 | int (11) | Yes | null |
| Percent | text | Yes | null |
| Price | text | Yes | null |
| Total | int (11) | Yes | null |
| Memo | text | Yes | null |
+ ----------- + --------- + ------ + ----- + --------- + ---------------- +
11 rows in SET (0.02 Sec)
Mysql> show columns from Order1;
+ ----------- + --------- + ------ + ----- + --------- + ---------------- +
| FIELD | type | null | key | default | extra |
+ ----------- + --------- + ------ + ----- + --------- + ---------------- +
| ID | int (11) | no | pri | null | auto_increment |
| Time | date | Yes | null |
| Orderno | text | Yes | null |
| Originid | int (11) | Yes | null |
| Priceid | int (11) | Yes | null |
| Paymentid | int (11) | Yes | null |
| Delivery | text | Yes | null |
| Price | text | Yes | null |
| Total | text | Yes | null |
| Memo | text | Yes | null |
+ ----------- + --------- + ------ + ----- + --------- + ---------------- +
10 rows in SET (0.02 Sec)
Mysql> alter table Order1 drop price;
Query OK, 10 rows affected (0.24 Sec)
Records: 10 duplicates: 0 Warnings: 0
Mysql> alter table Order1 drop total;
Query OK, 10 rows affected (0.17 Sec)
Records: 10 duplicates: 0 Warnings: 0
Mysql> show columns from Order1;
+ ----------- + --------- + ------ + ----- + --------- + ---------------- +
| FIELD | type | null | key | default | extra |
+ ----------- + --------- + ------ + ----- + --------- + ---------------- +
| ID | int (11) | no | pri | null | auto_increment |
| Time | date | Yes | null |
| Orderno | text | Yes | null |
| Originid | int (11) | Yes | null |
| Priceid | int (11) | Yes | null |
| Paymentid | int (11) | Yes | null |
| Delivery | text | Yes | null |
| Memo | text | Yes | null |
+ ----------- + --------- + ------ + ----- + --------- + ---------------- +
8 rows in SET (0.01 Sec)
Add a row to a table
Mysql> show columns from enq2;
+ ----------- + --------- + ------ + ----- + --------- + ---------------- +
| FIELD | type | null | key | default | extra |
+ ----------- + --------- + ------ + ----- + --------- + ---------------- +
| ID | int (11) | no | pri | null | auto_increment |
| Time | date | Yes | null |
| Enq1id | int (11) | Yes | null |
| Originid | int (11) | Yes | null |
| Priceid | int (11) | Yes | null |
| Paymentid | int (11) | Yes | null |
| Makerid | int (11) | Yes | null |
| Delivery | text | Yes | null |
| Type1id | text | Yes | null |
| Partsid | text | Yes | null |
| Qty | text | Yes | null |
| Memo | text | Yes | null |
+ ----------- + --------- + ------ + ----- + --------- + ---------------- +
12 rows in SET (0.06 Sec)
Mysql> alter table enq2 add languageid int after enq1id;
Query OK, 1 row affected (0.45 Sec)
Records: 1 duplicates: 0 Warnings: 0
Mysql> show columns from enq2;
+ ------------ + --------- + ------ + ----- + --------- + ---------------- +
| FIELD | type | null | key | default | extra |
+ ------------ + --------- + ------ + ----- + --------- + ---------------- +
| ID | int (11) | no | pri | null | auto_increment |
| Time | date | Yes | null |
| Enq1id | int (11) | Yes | null |
| Languageid | int (11) | Yes | null |
| Originid | int (11) | Yes | null |
| Priceid | int (11) | Yes | null |
| Paymentid | int (11) | Yes | null |
| Makerid | int (11) | Yes | null |
| Delivery | text | Yes | null |
| Type1id | text | Yes | null |
| Partsid | text | Yes | null |
| Qty | text | Yes | null |
| Memo | text | Yes | null |
+ ------------ + --------- + ------ + ----- + --------- + ---------------- +
13 rows in SET (0.00 Sec)
Mysql> show columns from quo1;
+ ---------- + --------- + ------ + ----- + --------- + ---------------- +
| FIELD | type | null | key | default | extra |
+ ---------- + --------- + ------ + ----- + --------- + ---------------- +
| ID | int (11) | no | pri | null | auto_increment |
| Time | date | Yes | null |
| Enq2id | int (11) | Yes | null |
| Makerref | text | Yes | null |
| Memo | text | Yes | null |
+ ---------- + --------- + ------ + ----- + --------- + ---------------- +
5 rows in SET (0.30 Sec)
Mysql> alter table quo1 add price text after makerref;
Query OK, 2 rows affected (0.67 Sec)
Records: 2 duplicates: 0 Warnings: 0
Mysql> show columns from quo1;
+ ---------- + --------- + ------ + ----- + --------- + ---------------- +
| FIELD | type | null | key | default | extra |
+ ---------- + --------- + ------ + ----- + --------- + ---------------- +
| ID | int (11) | no | pri | null | auto_increment |
| Time | date | Yes | null |
| Enq2id | int (11) | Yes | null |
| Makerref | text | Yes | null |
| Price | text | Yes | null |
| Memo | text | Yes | null |
+ ---------- + --------- + ------ + ----- + --------- + ---------------- +
6 rows in SET (0.02 Sec)
Modify a column (Rename and change the data definition)
Mysql> show columns from Order1;
+ ----------- + --------- + ------ + ----- + --------- + ---------------- +
| FIELD | type | null | key | default | extra |
+ ----------- + --------- + ------ + ----- + --------- + ---------------- +
| ID | int (11) | no | pri | null | auto_increment |
| Time | date | Yes | null |
| Quo2id | int (11) | Yes | null |
| Originid | int (11) | Yes | null |
| Priceid | int (11) | Yes | null |
| Paymentid | int (11) | Yes | null |
| Delivery | text | Yes | null |
| Price | text | Yes | null |
| Total | text | Yes | null |
| Memo | text | Yes | null |
+ ----------- + --------- + ------ + ----- + --------- + ---------------- +
10 rows in SET (0.16 Sec)
Mysql> alter table Order1 change quo2id orderno text;
Query OK, 6 rows affected (0.56 Sec)
Records: 6 duplicates: 0 Warnings: 0
Mysql> show columns from Order1;
+ ----------- + --------- + ------ + ----- + --------- + ---------------- +
| FIELD | type | null | key | default | extra |
+ ----------- + --------- + ------ + ----- + --------- + ---------------- +
| ID | int (11) | no | pri | null | auto_increment |
| Time | date | Yes | null |
| Orderno | text | Yes | null |
| Originid | int (11) | Yes | null |
| Priceid | int (11) | Yes | null |
| Paymentid | int (11) | Yes | null |
| Delivery | text | Yes | null |
| Price | text | Yes | null |
| Total | text | Yes | null |
| Memo | text | Yes | null |
+ ----------- + --------- + ------ + ----- + --------- + ---------------- +
10 rows in SET (0.02 Sec)