Brand new to MySQL and I m trying to write a Perl script to access a MySQL database on my own machine (eventually hoping to use the script to copy data from a SQL Server DB on a remote machine to the MySQL DB on my own machine, but first I need to get it to access my server).
I wrote the following code which works fine in MySQL Workbench:
use test; DROPTABLE IF EXISTS alerts;
Please not that I wrote DROPTABLE as one word here because when I write it as 2 words stackoverflow won t let me post it and gives me an error instead. In the actual code it is 2 words. If anyone can edit this question to make it 2 words please do, but I get the same error I got when posting when I try to edit it.
CREATE TABLE alerts (
alertid INT NOT NULL,
alertdttm DATE NOT NULL,
alerttype VARCHAR(256) NOT NULL,
userid INT NOT NULL,
alerttext VARCHAR(1024) NULL
);
However, when I use the following Perl script I get an error:
$sql_my = << END_SQLmy ;
use test;
DROPTABLE IF EXISTS alerts;
CREATE TABLE alerts (
alertid INT NOT NULL,
alertdttm DATE NOT NULL,
alerttype VARCHAR(256) NOT NULL,
userid INT NOT NULL,
alerttext VARCHAR(1024) NULL
);
END_SQLmy
$dbh2 = DBI->connect( "dbi:ODBC:MySQL57") || die "Cannot connect: $DBI::errstr";
print "Connecting to MySQL database.
";
$sth2 = $dbh2->prepare($sql_my);
$sth2->execute;
DBD::ODBC::st execute failed: [MySQL][ODBC 5.3(w) Driver][mysqld-5.7.9-log]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 DROPTABLE IF EXISTS alerts;
I played around with the code and changed it to
my $table = "alerts1";
$dbh2 = DBI->connect($dsn, $user, $pw);
$dbh2->do("CREATE TABLE $table (column VARCHAR(17));");
$dbh2->disconnect;
Which also connects just fine, but gives me an error for the CREATE TABLE line,
BDB::mysql::db do failed: You have an error in your SQL syntax; check the manual the corresponds to your MySQL server version for the right syntax to use near column VARCHAR{17)) at line 1 at testmysql.pl line 51.
so I think that s where my syntax error is coming from, but I can t figure out how to fix it. I checked permissions for the user and it seems to have correct permissions and it drops the table just fine, but it can t create it. When I run the same query in MySQL Workbench it creates the table, though, so I know the syntax is okay. Any idea what the problem is? (FYI I set up the ODBC with localhost, port 3306, and the root user and password, using the MySQL 5.3 unicode driver. I m on a Windows 7 machine.)
(Also, if anyone knows of a really great way of copying data from a remote SQL Server to a local MySQL database, feel free to chime in. Right now the best idea I ve come up with is pulling the data from the remote machine with Perl and then pushing it onto the local database, which hopefully I can do in one quick script.)
