select, if vs utf-8Naime, neka je tabela definisana sa
Code (sql):
CREATE TABLE person
(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(16)
) DEFAULT CHARSET="utf8"
CREATE TABLE person
(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(16)
) DEFAULT CHARSET="utf8"
i neka je ubacen red
Code (sql):
INSERT INTO person(name) VALUES('Petrović')
INSERT INTO person(name) VALUES('Petrović')
Sledeci upit kada se poziva iz skripta ne vraca UTF8 vec ASCII:
Code (sql):
SELECT IF(name != '', name, id) FROM person
SELECT IF(name != '', name, id) FROM person
Ako se kastuje id u IF-u, onda vraca UTF8 kao sto je i ocekivano
Code (sql):
SELECT IF(name != '', name, CAST(id AS CHAR(4))) FROM person
SELECT IF(name != '', name, CAST(id AS CHAR(4))) FROM person
Upit
Code (sql):
SELECT name FROM person
SELECT name FROM person
naravno vraca UTF8.
Svi upiti se izvrsavaju iz Perl i Python skriptova, jos nisam probao kako radi iz C-a. Pri tome, poslednja grana u IF uopste se ne izvrsava jer name svakako nije prazan string!
Da li je problem u SQL-u odn. kolacijama/enkodingu, C klijent biblioteci koju Perl i Python drajveri obmotavaju ili nesto trece? Verzije MySQL-a na kojima sam isprobao su MySQL 5.1.41 i 5.1.38 64bit.
Evo i celog Perl koda koji ilustruje problem.
Code (perl):
#!/usr/bin/perl
use lib "/usr/local/perl5/cpan/DBD-mysql-4.013/lib64/perl5/site_perl/5.10.0/x86_64-linux-thread-multi";
use DBI;
my $status = undef;
my $dbh = DBI->connect("DBI:mysql:database=test;host=localhost", "karas");
if (!defined($dbh))
{
printf STDERR "cannot connect to database!\n";
exit(1);
}
$status = $dbh->do("DROP TABLE IF EXISTS person");
if (!defined($status))
{
printf STDERR "cannot drop 'person' table\n";
}
$status = $dbh->do('CREATE TABLE person(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(16)) DEFAULT CHARSET="utf8"');
if (!defined($status))
{
printf STDERR "cannot create 'person' table\n";
}
$status = $dbh->do("INSERT INTO person(name) VALUES('Petrović')");
if (!defined($status))
{
printf STDERR "cannot insert into 'person' table!\n";
}
#$sth = $dbh->prepare("SELECT IF(name != '', name, id) FROM person");
#$sth = $dbh->prepare("SELECT name FROM person");
$sth = $dbh->prepare("SELECT IF(name != '', name, CAST(id AS CHAR(4))) FROM person");
if (defined($sth))
{
$status = $sth->execute;
if (defined($status))
{
while (@row = $sth->fetchrow_array)
{
print "@row\n";
}
}
else
{
printf STDERR "cannot execute select for 'person' table!\n";
}
}
else
{
printf STDERR "cannot prepare select for 'person' table!\n";
}
#!/usr/bin/perl
use lib "/usr/local/perl5/cpan/DBD-mysql-4.013/lib64/perl5/site_perl/5.10.0/x86_64-linux-thread-multi";
use DBI;
my $status = undef;
my $dbh = DBI->connect("DBI:mysql:database=test;host=localhost", "karas");
if (!defined($dbh))
{
printf STDERR "cannot connect to database!\n";
exit(1);
}
$status = $dbh->do("DROP TABLE IF EXISTS person");
if (!defined($status))
{
printf STDERR "cannot drop 'person' table\n";
}
$status = $dbh->do('CREATE TABLE person(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(16)) DEFAULT CHARSET="utf8"');
if (!defined($status))
{
printf STDERR "cannot create 'person' table\n";
}
$status = $dbh->do("INSERT INTO person(name) VALUES('Petrović')");
if (!defined($status))
{
printf STDERR "cannot insert into 'person' table!\n";
}
#$sth = $dbh->prepare("SELECT IF(name != '', name, id) FROM person");
#$sth = $dbh->prepare("SELECT name FROM person");
$sth = $dbh->prepare("SELECT IF(name != '', name, CAST(id AS CHAR(4))) FROM person");
if (defined($sth))
{
$status = $sth->execute;
if (defined($status))
{
while (@row = $sth->fetchrow_array)
{
print "@row\n";
}
}
else
{
printf STDERR "cannot execute select for 'person' table!\n";
}
}
else
{
printf STDERR "cannot prepare select for 'person' table!\n";
}
Sveti Avgustin: "Dobar hrišćanin treba da se kloni matematičara i svih onih koji daju lažna proročanstva. Postoji opasnost da su matematičari već sklopili pakt sa Đavolom, da pomrače čovekov um i da ga okuju okovima pakla."
