Unable to prepare statement with underscore

Hi,

I'm trying to run the following statement:
GRANT ALL PRIVILEGES ON user\_abcd.* TO 'user_abcd'@'192.168.2.1'
IDENTIFIED BY 'password'

This is the code:

my $query = "GRANT ALL PRIVILEGES ON user\\_abcd.* TO
'user_abcd'\@'192.168.2.1' IDENTIFIED BY 'password'";

print $query;
$dbh->do($query);

But I get the error: DO: Something wrong while try to prepare query 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 '\_abcd.* TO
'user_abcd'@'192.168.2.1' IDENTIFIED BY 'password'' at line 1

If I run the exact same command directly with the mysql client I get no
error at all.

What am I doing wrong or doesn't the DBD handle underscores in database
names?

Thanks,
-Patrik



--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Unable to prepare statement with underscore

Patrik Jansson wrote:

> I'm trying to run the following statement: GRANT ALL PRIVILEGES ON
> user\_abcd.* TO 'user_abcd'@'192.168.2.1' IDENTIFIED BY 'password'
>
> This is the code:
>
> my $query = "GRANT ALL PRIVILEGES ON user\\_abcd.* TO
> 'user_abcd'\@'192.168.2.1' IDENTIFIED BY 'password'";
>
> print $query; $dbh->do($query);
>
> But I get the error: DO: Something wrong while try to prepare query
> 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 '\_abcd.* TO 'user_abcd'@'192.168.2.1' IDENTIFIED BY 'password''
> at line 1
>
> If I run the exact same command directly with the mysql client I get
> no error at all.
>
> What am I doing wrong or doesn't the DBD handle underscores in
> database names?


Hi Patrik,

Remove one of the \ characters in the $query variable:

GRANT ALL PRIVILEGES ON user\_abcd.* TO......

--
Kenny


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Unable to prepare statement with underscore

Hi Kenny
Thanks for the answer,

Code:
my $query = "GRANT ALL PRIVILEGES ON user\_abcd.* TO
'user_abcd'\@'192.168.2.1' IDENTIFIED BY 'password';
Printing the string gives:
GRANT ALL PRIVILEGES ON user_abcd.* TO 'user_abcd'@'192.168.2.1'
IDENTIFIED BY 'password'
And the result:
DO: Something wrong while try to prepare query This command is not
supported in the prepared statement protocol yet

-Patrik

Kenny Scott skrev:
> Patrik Jansson wrote:
>
>> I'm trying to run the following statement: GRANT ALL PRIVILEGES ON
>> user\_abcd.* TO 'user_abcd'@'192.168.2.1' IDENTIFIED BY 'password'
>>
>> This is the code:
>>
>> my $query = "GRANT ALL PRIVILEGES ON user\\_abcd.* TO
>> 'user_abcd'\@'192.168.2.1' IDENTIFIED BY 'password'";
>>
>> print $query; $dbh->do($query);
>>
>> But I get the error: DO: Something wrong while try to prepare query
>> 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 '\_abcd.* TO 'user_abcd'@'192.168.2.1' IDENTIFIED BY 'password''
>> at line 1
>>
>> If I run the exact same command directly with the mysql client I get
>> no error at all.
>>
>> What am I doing wrong or doesn't the DBD handle underscores in
>> database names?
>
>
> Hi Patrik,
>
> Remove one of the \ characters in the $query variable:
>
> GRANT ALL PRIVILEGES ON user\_abcd.* TO......
>

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Unable to prepare statement with underscore

Patrik Jansson wrote:
> Hi Kenny
> Thanks for the answer,
>
> Code:
> my $query = "GRANT ALL PRIVILEGES ON user\_abcd.* TO
> 'user_abcd'\@'192.168.2.1' IDENTIFIED BY 'password';
> Printing the string gives:
> GRANT ALL PRIVILEGES ON user_abcd.* TO 'user_abcd'@'192.168.2.1'
> IDENTIFIED BY 'password'
> And the result:
> DO: Something wrong while try to prepare query This command is not
> supported in the prepared statement protocol yet
>

Hi Patrik,

It works for me using MySQL 5.0.24, libdbd 3.0007. What versions are
you using?

--
Kenny


> -Patrik
>
> Kenny Scott skrev:
>> Patrik Jansson wrote:
>>
>>> I'm trying to run the following statement: GRANT ALL PRIVILEGES ON
>>> user\_abcd.* TO 'user_abcd'@'192.168.2.1' IDENTIFIED BY 'password'
>>>
>>> This is the code:
>>>
>>> my $query = "GRANT ALL PRIVILEGES ON user\\_abcd.* TO
>>> 'user_abcd'\@'192.168.2.1' IDENTIFIED BY 'password'";
>>>
>>> print $query; $dbh->do($query);
>>>
>>> But I get the error: DO: Something wrong while try to prepare query
>>> 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 '\_abcd.* TO 'user_abcd'@'192.168.2.1' IDENTIFIED BY 'password''
>>> at line 1
>>>
>>> If I run the exact same command directly with the mysql client I get
>>> no error at all.
>>>
>>> What am I doing wrong or doesn't the DBD handle underscores in
>>> database names?
>>
>>
>> Hi Patrik,
>>
>> Remove one of the \ characters in the $query variable:
>>
>> GRANT ALL PRIVILEGES ON user\_abcd.* TO......
>>


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Unable to prepare statement with underscore

> It works for me using MySQL 5.0.24, libdbd 3.0007. What versions are
> you using?
>
Well, this particular server is running MySQL 4.1.14. How can I see
which libdbd version it is?
I need this script to run on different machines, on with a MySQL server
as old as 4.0.14.

-Patrik

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Unable to prepare statement with underscore

Patrik Jansson wrote:
>
>> It works for me using MySQL 5.0.24, libdbd 3.0007. What versions are
>> you using?
>>
> Well, this particular server is running MySQL 4.1.14. How can I see
> which libdbd version it is?
> I need this script to run on different machines, on with a MySQL
> server as old as 4.0.14.

Hi,

I just ran the same thing on MySQL 4.1.11 and again, it worked.

From your script you can get the version of the library by doing this:

print $DBD::mysql::VERSION, "\n";

--
Kenny


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Unable to prepare statement with underscore

>>> It works for me using MySQL 5.0.24, libdbd 3.0007. What versions
>>> are you using?
>>>
>> Well, this particular server is running MySQL 4.1.14. How can I see
>> which libdbd version it is?
>> I need this script to run on different machines, on with a MySQL
>> server as old as 4.0.14.
>
> Hi,
>
> I just ran the same thing on MySQL 4.1.11 and again, it worked.
>
> From your script you can get the version of the library by doing this:
>
> print $DBD::mysql::VERSION, "\n";
Thanks for your help,
libdbd seems to be 3.0002_1
I try to update by running cpan install DBD::mysql but it fails on the
test because
it is not able to login as root, duh! How can I change username / password?

-Patrik

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Unable to prepare statement with underscore

Patrik Jansson wrote:
>
> >>> It works for me using MySQL 5.0.24, libdbd 3.0007. What
> >>> versions are you using?
> >>>
> >> Well, this particular server is running MySQL 4.1.14. How can I
> >> see which libdbd version it is? I need this script to run on
> >> different machines, on with a MySQL server as old as 4.0.14.
> >
> > Hi,
> >
> > I just ran the same thing on MySQL 4.1.11 and again, it worked.
> >
> > From your script you can get the version of the library by doing
> > this:
> >
> > print $DBD::mysql::VERSION, "\n";
> Thanks for your help, libdbd seems to be 3.0002_1

Hi,

That's the same version I ran it with, on MySQL 4.1.11. Something is
amiss here... Can you re-send the Perl script that you are running as an
attachment? I can't understand how we're getting different results if we
are running the same thing.

> I try to update by running cpan install DBD::mysql but it fails on
> the test because it is not able to login as root, duh! How can I
> change username / password?

Absolutely no idea, I'm afraid, but I don't think you need to update the
library at the moment as I don't think that's the problem.

--
Kenny


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Unable to prepare statement with underscore

--------------010409040308010100040206
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Thanks for taking the time.
The script did lots of other stuff to so I have rewritten a new simplier
script which does only this thing.
Of course I have tried to run the script and it gives the same result.

This is the result:
# perl grant.pl
3.0002_1
DO: Something wrong while try to prepare query This command is not
supported in the prepared statement protocol yet
GRANT ALL PRIVILEGES ON user_abcd.* TO 'user_abcd'@'192.168.2.1'
IDENTIFIED BY 'secret'

-Patrik

Kenny Scott skrev:
> Patrik Jansson wrote:
>>
>> >>> It works for me using MySQL 5.0.24, libdbd 3.0007. What
>> >>> versions are you using?
>> >>>
>> >> Well, this particular server is running MySQL 4.1.14. How can I
>> >> see which libdbd version it is? I need this script to run on
>> >> different machines, on with a MySQL server as old as 4.0.14.
>> >
>> > Hi,
>> >
>> > I just ran the same thing on MySQL 4.1.11 and again, it worked.
>> >
>> > From your script you can get the version of the library by doing
>> > this:
>> >
>> > print $DBD::mysql::VERSION, "\n";
>> Thanks for your help, libdbd seems to be 3.0002_1
>
> Hi,
>
> That's the same version I ran it with, on MySQL 4.1.11. Something is
> amiss here... Can you re-send the Perl script that you are running as
> an attachment? I can't understand how we're getting different results
> if we are running the same thing.
>
>> I try to update by running cpan install DBD::mysql but it fails on
>> the test because it is not able to login as root, duh! How can I
>> change username / password?
>
> Absolutely no idea, I'm afraid, but I don't think you need to update
> the library at the moment as I don't think that's the problem.
>

--------------010409040308010100040206
Content-Type: text/plain;
name="grant.pl"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="grant.pl"

#!/usr/local/bin/perl -w

use strict;
use DBI;


my $dsn = "DBI:mysql:database=mysql:host=localhost";
my $dbh = DBI->connect($dsn, "username", "password");
print $DBD::mysql::VERSION, "\n";
my $query = "GRANT ALL PRIVILEGES ON user\_abcd.* TO 'user_abcd'\@'192.168.2.1' IDENTIFIED BY 'secret'";
print $query;
$dbh->do($query);
$dbh->disconnect();


--------------010409040308010100040206
Content-Type: text/plain; charset=us-ascii


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org
--------------010409040308010100040206--

Re: Unable to prepare statement with underscore

Hi Patrik,

I'm guessing that it is something to do with server-side prepared
statements, which isn't something I've worked with before. From
googling the error, it seems to come up in those situations. I can't
look in to it further right now but hopefully someone else will come
back with something to try for you... I've tried to replicate the error
with mysql_emulated_prepare in the connect string but I just can't get
it to blow up like you can :(

If no-one comes back to you I'll attempt to work it out later on tonight,

Cheers,

--
Kenny


Patrik Jansson wrote:
> Thanks for taking the time.
> The script did lots of other stuff to so I have rewritten a new
> simplier script which does only this thing.
> Of course I have tried to run the script and it gives the same result.
>
> This is the result:
> # perl grant.pl
> 3.0002_1
> DO: Something wrong while try to prepare query This command is not
> supported in the prepared statement protocol yet
> GRANT ALL PRIVILEGES ON user_abcd.* TO 'user_abcd'@'192.168.2.1'
> IDENTIFIED BY 'secret'
>
> -Patrik
>
> Kenny Scott skrev:
>> Patrik Jansson wrote:
>>>
>>> >>> It works for me using MySQL 5.0.24, libdbd 3.0007. What
>>> >>> versions are you using?
>>> >>>
>>> >> Well, this particular server is running MySQL 4.1.14. How can I
>>> >> see which libdbd version it is? I need this script to run on
>>> >> different machines, on with a MySQL server as old as 4.0.14.
>>> >
>>> > Hi,
>>> >
>>> > I just ran the same thing on MySQL 4.1.11 and again, it worked.
>>> >
>>> > From your script you can get the version of the library by doing
>>> > this:
>>> >
>>> > print $DBD::mysql::VERSION, "\n";
>>> Thanks for your help, libdbd seems to be 3.0002_1
>>
>> Hi,
>>
>> That's the same version I ran it with, on MySQL 4.1.11. Something is
>> amiss here... Can you re-send the Perl script that you are running as
>> an attachment? I can't understand how we're getting different results
>> if we are running the same thing.
>>
>>> I try to update by running cpan install DBD::mysql but it fails on
>>> the test because it is not able to login as root, duh! How can I
>>> change username / password?
>>
>> Absolutely no idea, I'm afraid, but I don't think you need to update
>> the library at the moment as I don't think that's the problem.
>>
> ------------------------------------------------------------ ------------
>
> #!/usr/local/bin/perl -w
>
> use strict;
> use DBI;
>
>
> my $dsn = "DBI:mysql:database=mysql:host=localhost";
> my $dbh = DBI->connect($dsn, "username", "password");
> print $DBD::mysql::VERSION, "\n";
> my $query = "GRANT ALL PRIVILEGES ON user\_abcd.* TO 'user_abcd'\@'192.168.2.1' IDENTIFIED BY 'secret'";
> print $query;
> $dbh->do($query);
> $dbh->disconnect();
>


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Unable to prepare statement with underscore

Hi,
Thanks for your help anyway. I might write this in php or python instead
because I really have to get this going as soon as possible.
If someone have any ideas I'll be happy to try them out anyway just to
straighten out the problem.
-Patrik


Kenny Scott skrev:
> Hi Patrik,
>
> I'm guessing that it is something to do with server-side prepared
> statements, which isn't something I've worked with before. From
> googling the error, it seems to come up in those situations. I can't
> look in to it further right now but hopefully someone else will come
> back with something to try for you... I've tried to replicate the
> error with mysql_emulated_prepare in the connect string but I just
> can't get it to blow up like you can :(
>
> If no-one comes back to you I'll attempt to work it out later on tonight,
>
> Cheers,
>


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Unable to prepare statement with underscore

Patrik Jansson wrote:
>
>>>> It works for me using MySQL 5.0.24, libdbd 3.0007. What versions
>>>> are you using?
>>>>
>>> Well, this particular server is running MySQL 4.1.14. How can I see
>>> which libdbd version it is?
>>> I need this script to run on different machines, on with a MySQL
>>> server as old as 4.0.14.
>>
>>
>> Hi,
>>
>> I just ran the same thing on MySQL 4.1.11 and again, it worked.
>>
>> From your script you can get the version of the library by doing this:
>>
>> print $DBD::mysql::VERSION, "\n";
>
> Thanks for your help,
> libdbd seems to be 3.0002_1
> I try to update by running cpan install DBD::mysql but it fails on the
> test because
> it is not able to login as root, duh! How can I change username / password?

with cpan, if you decide any of the failed tests are ok you can force
install:
cpan> force install

--
Fridh


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Unable to prepare statement with underscore

>
> with cpan, if you decide any of the failed tests are ok you can force
> install:
> cpan> force install
>
I decided to try to force install the module and guess what, now the
grant query works like a charm.
Although I can't run a GRANT query with a database including \_. I have
to remove the backslash
just like Kenny said earlier. Even though the already existing rows in
the db table include a backslash
before underscore I guess they are not necessary. Seems to work with and
without.

Thank you both,
-Patrik

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Unable to prepare statement with underscore

On Tuesday 14 November 2006 13:08, Patrik Jansson wrote:
> I try to update by running cpan install DBD::mysql but it fails on the
> test because
> it is not able to login as root, duh! How can I change username / password?

When running 'perl Makefile.PL ' use these options.

--testuser= Use the username for running the test suite;
defaults to no username
--testpassword= Use the password for running the test suite;
defaults to no password

Note, the user will need have permissions to create and drop tables and
perform DML in the selected database (defaults to 'test').

There are lots of other options that can be used with Makefile.PL, type:

perl Makefile.PL --help

to see them.

cheers Simon
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Simon Rees | tech-lists @t zodiac2000 co uk |
ORA-03113: end-of-file on communication channel
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Unable to prepare statement with underscore

In article <4559BFD5.7060105-sRU9wVaEhJjLoDKTGw+V6w@public.gmane.org>,
Patrik Jansson writes:

>> print $DBD::mysql::VERSION, "\n";
> Thanks for your help,
> libdbd seems to be 3.0002_1
> I try to update by running cpan install DBD::mysql but it fails on the
> test because

> it is not able to login as root, duh! How can I change username / password?

The DBD-mysql-xxxx directory CPAN created for you contains an
INSTALL.html which tells you how to do that.


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Unable to prepare statement with underscore

> The DBD-mysql-xxxx directory CPAN created for you contains an
> INSTALL.html which tells you how to do that.
I read about running perl Makefile.PL but didn't really know where, but
now I get it.
Thanks!
-Patrik

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org