Accessing MySQL database using Perl DBD:ODBC

Question

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.\n";
$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.)

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. You mean like mysqldump and mysql? See: Copy a database from one server to another — Matt Jacob
– Matt Jacob, Commented Oct 29, 2015 at 16:26

ikegami · Accepted Answer · 2015-10-29 17:12:33Z

2

You have an error in your SQL syntax; [...] near 'DROPTABLE IF EXISTS alerts;

As the error says, your SQL is invalid.

DROPTABLE IF EXISTS alerts;

should be

DROP TABLE IF EXISTS alerts;
    ^

You have an error in your SQL syntax; [...] near 'column VARCHAR{17))'

Similarly,

"CREATE TABLE $table (column VARCHAR{17));"

should be

"CREATE TABLE $table (column VARCHAR(17));"
                                    ^

Actually, it should be

"CREATE TABLE ".$dbh->quote_identifier($table)." (column VARCHAR(17));"

edited Oct 29, 2015 at 17:12

answered Oct 29, 2015 at 15:04

ikegami

391k17 gold badges291 silver badges555 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Compysaurus Over a year ago

Sorry, the DROPTABLE thing was a mistake on my part. When I tried to upload a comment to SO with the words separated, I got an error and it wouldn't let me post the question. The only way to fix it was to make it one word. I thought I had added a sentence explaining that but I guess I left it out. Not sure what the issue is because it gave me the same "An error occurred" warning when I tried to post this comment. But it's clearly no a SO thing because you posted it just fine in your answer.

Borodin · Accepted Answer · 2015-10-29 15:07:24Z

1

You can't execute more than one SQL statement at a time using DBI, so you need one call for DROP TABLE and another for CREATE TABLE

You say your error message says the syntax error is

near 'column VARCHAR{17))'

and the obvious solution is to change the opening brace to an opening parenthesis

answered Oct 29, 2015 at 15:07

Borodin

127k9 gold badges72 silver badges146 bronze badges

2 Comments

ikegami Over a year ago

Re "You can't execute more than one SQL statement at a time using DBI", That's driver-specific, and it looks like these drivers allow it (from the fact that it allowed the use test; before a second statement).

Compysaurus Over a year ago

Thanks, I didn't realize you had to have a separate call for each statement using DBI. Amatuer mistake. I changed it so each statement has a separate call and it works now. (Don't know why the brace was there instead of parentheses. Oops.)

Collectives™ on Stack Overflow

Accessing MySQL database using Perl DBD:ODBC

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related