sqlmetal issue with mysql

[El]

Hi,

I'm testing out Linq to MySql. I downloaded the latest MySQL
Connector/Net from the MySQL site. I have a simple mysql database
with 1 table in the schema.

When I executed the command below:

sqlmetal /provider:MySql /server:localhost /database:MySqlSampleDb /
user:hansel /password:rpt4wr5 /pluralize /code=mysqlsampledb.cs

I get this as the output and I don't get any file.

DbLinq Database mapping generator 2008 version 0.20
for Microsoft (R) .NET Framework version 3.5
Distributed under the MIT licence (http://linq.to/db/license)

>>> Reading schema from MySQL database
sqlmetal: Client does not support authentication protocol requested by
server; consider upgrading MySQL client

I have the latest Mysql.Data.dll in my GAC. I even copied the file in
the local directory where I run sqlmetal. I saw some links that
suggested adding the /databaseConnectionProvider. So I did and change
it as below. I also added the debug option to get the stack trace.

sqlmetal /provider:MySql /
databaseConnectionProvider="MySql.Data.MySqlConnection, MySql.Data" /
server:localhost /database:MySqlSampleDb /user:hansel /
password:rpt4wr5 /debug /pluralize /code=mysqlsampledb.cs

This is what I got...

DbLinq Database mapping generator 2008 version 0.20
for Microsoft (R) .NET Framework version 3.5
Distributed under the MIT licence (http://linq.to/db/license)

>>> Reading schema from DBML file '/databaseConnectionProvider=MySql.Data.MySqlConnection, MySql.Data'
sqlmetal: Could not find file "/
databaseConnectionProvider=MySql.Data.MySqlConnection, MySql.Data".

I even copied the DLL locally and installed it in the GAC. Below is
the trace output with /debug:

sqlmetal: System.IO.FileNotFoundException: Could not find file "/
databaseConnectionProvider=MySql.Data.MySqlConnection, MySql.Data".
File name: '/databaseConnectionProvider=MySql.Data.MySqlConnection,
MySql.Data'
at System.IO.FileStream..ctor (System.String path, FileMode mode,
FileAccess access, FileShare share, Int32 bufferSize, Boolean
anonymous, FileOptions options) [0x00000] in <filename unknown>:0
at System.IO.FileStream..ctor (System.String path, FileMode mode,
FileAccess access, FileShare share) [0x00000] in <filename unknown>:0
at (wrapper remoting-invoke-with-check) System.IO.FileStream:.ctor
(string,System.IO.FileMode,System.IO.FileAccess,System.IO.FileShare)
at System.IO.File.OpenRead (System.String path) [0x00000] in
<filename unknown>:0
at DbMetal.Generator.Implementation.Processor.ReadSchema
(DbMetal.Parameters parameters, System.String filename) [0x00000] in
<filename unknown>:0
at DbMetal.Generator.Implementation.Processor.ReadSchema
(DbMetal.Parameters parameters, ISchemaLoader& schemaLoader) [0x00000]
in <filename unknown>:0
at DbMetal.Generator.Implementation.Processor.ProcessSchema
(DbMetal.Parameters parameters) [0x00000] in <filename unknown>:0

I don't know what else to try. Any help appreciated.

[James Darbyshire]

Is this with the mono bundled dblinq or the download at google code?

Regards,

James Darbyshire

Sent from my Samsung Droid™

--
You received this message because you are subscribed to the Google Groups "DbLinq" group.
To post to this group, send email to dbl...@googlegroups.com.
To unsubscribe from this group, send email to dblinq+un...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/dblinq?hl=en.

[El]

Both. I started with the mono bundle but that was troublesome. Then
I got it through google code. The mono bundle referenced the ByteFX
connector so I downloaded the MySql Connector/Net from the MySql site
too and reference it using the databaseConnectionProvider option is
sqlmetal. Have you successfully got this on mono to work? If so, can
you share the steps to get it going? I know it's not the mysql
provider connector because it works just fine using ADO.NET style
programming. Thanks.


On Sep 22, 3:32 am, James Darbyshire <jamesdarbysh...@gmail.com>
wrote:

> Is this with the mono bundled dblinq or the download at google code?
>
> Regards,
>
> James Darbyshire
>
> Sent from my Samsung Droid™
>

> dblinq+un...@googlegroups.com <dblinq%2Bunsu...@googlegroups.com>.

[Jonathan Pryor]

On Tue, 2010-09-21 at 20:32 -0700, El wrote:
> When I executed the command below:
>
> sqlmetal /provider:MySql /server:localhost /database:MySqlSampleDb /
> user:hansel /password:rpt4wr5 /pluralize /code=mysqlsampledb.cs

...

> >>> Reading schema from MySQL database
> sqlmetal: Client does not support authentication protocol requested by
> server; consider upgrading MySQL client
>
> I have the latest Mysql.Data.dll in my GAC.

Not sufficient. sqlmetal is, for all intents and purposes, DbMetal,
thus the following applies:

http://code.google.com/p/dblinq2007/wiki/Installation#To_run_DbMetal

Specifically the "There are three ways that the ADO.NET provider can be
used with DbMetal.exe" section:

If your ADO.NET provider is present within the Global Assembly
Cache, you can edit DbMetal.exe.config (in the DbLinq
installation directory) to use an assembly-qualified type name
in the /configuration/providers/provider/@databaseConnection
attribute.

The default ADO.NET provider for MySQL is the ByteFX.Data provider. The
advantage is that ByteFX is bundled with Mono, but it's also ancient
(hence your authentication error). Thus, you should edit
e.g. /usr/lib/mono/2.0/sqlmetal.exe.config and change the MySQL provider
value. Alternatively...

> I saw some links that
> suggested adding the /databaseConnectionProvider.
>

> sqlmetal /provider:MySql /
> databaseConnectionProvider="MySql.Data.MySqlConnection, MySql.Data" /
> server:localhost /database:MySqlSampleDb /user:hansel /
> password:rpt4wr5 /debug /pluralize /code=mysqlsampledb.cs

If your mono is anything recent, it's not /databaseConnectionProvider,
it's --with-dbconnection. See 'sqlmetal /?' for details.. Furthermore,
the value needs to be a fully qualified name, which yours isn't. Thus:

sqlmetal /provider:MySql \
--with-dbconnection="MySql.Data.MySqlClient.MySqlConnection, MySql.Data, Version=5.0.8.1, Culture=neutral, PublicKeyToken=c5687fc88969c44d" \
/server:localhost /database:MySqlSampleDb \
/user:user /password:password /debug /pluralize \
/code:mysqlsampledb.cs

Note that the above value comes from MySql.Data v5.0.8.1, so things may
have changed [0].

- Jon

[0] Pro tip: to easily get the assembly qualified name,
use /usr/bin/csharp:

$ csharp -r:path/to/MySql.Data.dll
csharp> typeof(MySql.Data.MySqlClient.MySqlConnection).AssemblyQualifiedName;
"MySql.Data.MySqlClient.MySqlConnection, MySql.Data, Version=5.0.8.1, Culture=neutral, PublicKeyToken=c5687fc88969c44d"

[El]

Thanks. It worked. My project compiles. But running it caused an
error:

Unhandled Exception: MySql.Data.MySqlClient.MySqlException: 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 '[ISBN],
[Title], [AvailableDate]
FROM [MySqlSampleDb].[Book]' at line 1
at MySql.Data.MySqlClient.MySqlStream.ReadPacket () [0x00000] in
<filename unknown>:0
at MySql.Data.MySqlClient.NativeDriver.GetResult (System.Int32&
affectedRow, System.Int32& insertedId) [0x00000] in <filename unknown>:
0

Here's my simple test code:

class MainClass
{
public static void Main (string[] args)
{
linqstylecode();
}

private static void linqstylecode()
{
IDbConnection dbcon;
dbcon = (IDbConnection) new MySqlConnection("Server=localhost;
Database=MySqlSampleDb; User=user; Password=password;");

using (var db = new MySqlSampleDb(dbcon))
{

var books = from bk in db.Book
select (new {bkIsbn = bk.Isbn, bkTitle=bk.Title,
bkAvailableDt=bk.AvailableDate});

foreach(var mybook in books)
{
Console.WriteLine(mybook.bkIsbn + ": " + mybook.bkTitle);
}
}
}
}

The MySql provider is fine because I tested it using ADO.NET as
below...

private static void adostylecode()
{
IDbConnection dbcon;
dbcon = (IDbConnection) new MySqlConnection("Server=localhost;
Database=MySqlSampleDb; User=user; Password=password ");
dbcon.Open();
IDbCommand dbcmd = dbcon.CreateCommand();
string sql = "SELECT * FROM Book";
dbcmd.CommandText = sql;
IDataReader reader = dbcmd.ExecuteReader();
while(reader.Read())
{
string _isbn = reader.GetString(0);
string _title = reader.GetString(1);
// string _availableDt = reader.GetString(2);
Console.WriteLine("ISBN: " + _isbn + " Title:" + _title);
}
// cleanup
reader.Close();
reader = null;
dbcmd.Dispose();
dbcmd = null;
dbcon.Close();
dbcon = null;
}

I understand what the error is referring to but I am drawing blank as
to what part of the above code is causing this. Have anyone encounter
such issue with MySQL and DbLinq. Any ideas how to make it work?
Thanks.

[Jonathan Pryor]

On Thu, 2010-09-23 at 10:42 -0700, El wrote:
> Thanks. It worked. My project compiles. But running it caused an
> error:
>

> Unhandled Exception: MySql.Data.MySqlClient.MySqlException: ...
...

> Here's my simple test code:

...

> dbcon = (IDbConnection) new MySqlConnection("Server=localhost;
> Database=MySqlSampleDb; User=user; Password=password;");

http://code.google.com/p/dblinq2007/wiki/Installation#To_use_DbLinq

Your problem is that your connection string is missing the
DbLinqProvider parameter. Consequently DbLinq assumes you're targeting
Microsoft SQL Server, and thus produces MSSQL-style SQL instead of
MySQL-style SQL.

The fix is to add a DbLinqProvider=MySql parameter:

var dbcon = new MySqlConnection(
"Server=localhost;" +
"Database=MySqlSampleDb;" +
"User=user; Password=Password;" +
"DbLinqProvider=MySql");
...

- Jon

[El]

> The fix is to add a DbLinqProvider=MySql parameter:
>
> var dbcon = new MySqlConnection(
> "Server=localhost;" +
> "Database=MySqlSampleDb;" +
> "User=user; Password=Password;" +
> "DbLinqProvider=MySql");
> ...

MySql does not like the "DbLinqProvider" option pass to its
MySqlConnection(). It gave me an error complaining that it was not a
valid MySql connection option. However, passing the connection string
to the generated DataContext as shown below worked.

var db = new MySqlSampleDb("DbLinqProvider=MySql; " +
"DbLinqConnectionType=MySql.Data.MySqlClient.MySqlConnection,
MySql.Data, Version=6.3.4.0, Culture=neutral,
PublicKeyToken=c5687fc88969c44d; " +

"Server=localhost; Database=MySqlSampleDb; User=user;
Password=password;")

Thanks much for your kind assistance.
-El

[walt...@gmail.com]

Great post. really helful.

Thanks mate!