MySQL操作程序一
返回
NULL值的判斷
<![CDATA[
$t{type1id} = $$pref{dbh}->selectrow_array("SELECT type1id FROM enq1 WHERE id =
3");
if ( $t{type1id} == 0 ) {
print "Type1id is NULL\n";
}
==>不是數(shù)值項(xiàng)的話,這個(gè)語句有問題。數(shù)值項(xiàng)專用。
if ( length($t{type1id}) == 0 ) {
print "Type1id is NULL\n";
}
==>如果Null的話,這個(gè)語句有問題
如果@rec含有NULL的話,下面的操作要出錯(cuò)誤信息
$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 (==) at
./pro/mscenq1.pl line 91, <CONFIG> line 11.,
--------------------------------------------------
如何判斷一個(gè)項(xiàng)目的值是否是NULL(未解決)
解決!第一次INSERT時(shí),放一個(gè)常數(shù)(比如"B")
起源==>
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:
--------------------------------------------------
要考慮$t{Nuid1}不存在的情況
]]>
考慮id=C的情況
<![CDATA[
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:
--------------------------------------------------
要考慮$t{pid1}='C'的情況
if ( $#{ $t{pid_list} } == 0 && $t{pid_list}[0] eq 'C' ) {
next;
}
]]>
COPY一個(gè)項(xiàng)目的subroutine
<![CDATA[
use strict;
use DBI;
# 連接數(shù)據(jù)庫
my(%t,$n,@fld,@rec,$pref);
print "This is test3.pl.\n";
# 連接數(shù)據(jù)庫
$$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);
# 關(guān)閉數(shù)據(jù)庫
$$pref{dbh}->disconnect;
# COPY一個(gè)項(xiàng)目
sub copy_one {
my($pref) = @_;
my(%t,@rec,$n);
# 取出COLUMNS
$t{sth} = $$pref{dbh}->prepare("SHOW COLUMNS FROM $$pref{table}");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
push(@{ $t{columns} },$rec[0]);
}
$t{sth}->finish;
# 取出數(shù)據(jù)(同時(shí)記住不是NULL的項(xiàng)目)
@{ $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} });
# 插入新項(xiàng)目
$t{sql} = 'INSERT INTO ' . $$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存在很簡(jiǎn)單的命令執(zhí)行上面的操作。已經(jīng)做過的程序就放在這兒了。
]]>
返回
平邑县|
桐城市|
云南省|
昌图县|
郑州市|
临夏县|
开封市|
托里县|
二连浩特市|
柘城县|
岑巩县|
平遥县|
白城市|
宁国市|
姜堰市|
巴林右旗|
岳西县|
木兰县|
竹北市|
泸水县|
古浪县|
宾阳县|
西乌珠穆沁旗|
邳州市|
张掖市|
克什克腾旗|
肃宁县|
襄垣县|
紫金县|
称多县|
新余市|
日喀则市|
克山县|
蓝山县|
乃东县|
葵青区|
沽源县|
天津市|
桐柏县|
泰和县|
开封县|