C# Table Adapter Query using mysql and the ';' symbol

Question

4.00/5 (1 vote)

See more:

I am using some tableadapters with a couple of queries per adapter and I have some issues with getting the queries to work.

This is a project using Visual Studio 2010 pro with the 6.3.5 of the mysql connector.

Normal ones work fine: like "select * from d_table where id=@idIwant" etc.

However, I am unable to find how to add multiple lines to the query like this:
(just as an example)

SQL

SET @id:=0;
select *,@id:=@id+1 as rowid from d_table;

While this query works find in a mysql sql window Visual studio fails whenever add the ';' symbol to the end of a line in a table adapter query. AND it will not accept standard mysql ':=" constructs..

I must be missing something silly as I could not find info on this error or I missed the fact that table adapters in C# are limited to ONE line of query and can not use standard mysql formats.

Anyone have any ideas?

(I am fixing legacy code passed off to me...)

THANKS
rcp

Posted 21-May-11 10:50am

RonParkJr

Updated 21-May-11 17:29pm

thatraja

v2

Add a Solution

Comments

Christian Graus 22-May-11 8:07am

Does MySQL support stored procs ? Perhaps this code needs to be moved in to one ?

RonParkJr 23-May-11 18:57pm

Thanks! That is the answer.. It was not quite a "snap" but it does in fact work.. and it works well.

1. Upgrade their mysql installation. MYSQL 5.1 and up for stored procedures.. That actually went very smoothly on the server end. Just make sure the backups are good!

2. I created the stored procedure and tried it out on the mysql query browser. I got an error about running out of thread stack memory. I checked the my.cnf file and found it set to 192. I set it to 256 and restarted the server and it still would not run.. I have found that if you comment out the line: "thread_stack=192" and then restart, that it works. IN THIS VERSION ON THIS SERVER at least.

A further upgrade to mysql 5.5 MAY do the trick there. I have not investigated that further.

3. I then went to the Visual Studio data set designer and created a new query for the table adapter and used the "Use Existing Stored Procedure".

Problem solved.

rcp

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kim Togo · Answer 1 · 2011-05-23T22:21:00

Solution 1

A comment about "it will not accept standard mysql ':=" constructs".
There is an option in the connection string called "Allow User Variables" - Default to false.

Setting this to true indicates that the provider expects user variables in the SQL. This option was added in Connector/NET version 5.2.2.

But you probably have solved your problem :-)

Posted 23-May-11 22:21pm

Kim Togo

Comments

RonParkJr 24-May-11 14:44pm

Thanks... I did upgrade them to use the Connector/Net 6.3.6. The Visual Studio Table Adapter still does not accept a query such as

"select @id:=@id+1 as rid, table_variable from data_table"

It fails right at the ":=" contruct in it. It gives you one of those "can not parse query at ':' " errors.

Moving the sql code out to the stored procedure turnned out to work great.

again thanks!
rcp

C# Table Adapter Query using mysql and the ';' symbol

1 solution

Solution 1

Add your solution here

Preview 0

C# Table Adapter Query using mysql and the ';' symbol

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us