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)做過的程序就放在這兒了。
]]>
返回
靖西县|
成都市|
静乐县|
依兰县|
无为县|
北碚区|
焦作市|
嵊泗县|
牙克石市|
泸西县|
芜湖县|
巫溪县|
鹿泉市|
黄冈市|
崇文区|
郓城县|
云阳县|
无极县|
天长市|
万全县|
新昌县|
株洲县|
三亚市|
长沙县|
富顺县|
榆中县|
新闻|
岫岩|
安图县|
永新县|
会同县|
吴川市|
蓬溪县|
丹巴县|
静宁县|
吉隆县|
佛坪县|
平泉县|
洪江市|
蚌埠市|
正镶白旗|