Comments - how can i transfer data use cursor
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.
explain better what you want i didn't understood
do you want a c# example?
about cursors you should see http://dev.mysql.com/doc/refman/5.6/en/cursors.html
and this for general information about SQL and cursors http://en.wikipedia.org/wiki/Cursor_(databases)
could you explain better what you want?
for example table a has follow data: name val para jam 12 blue kim 15 green amy 11 pink marry 9 red and i want create a procedure to transfer all data ,in Oracle create or replace procedure tran( rs out sys_refcursor) is begin open rs for select * from a; end; in c# i can define a dataset to get rs when the procedure executed. and how can i do in MariaDB?
hum, well from what i know, you have client side and server side cursors for c# you will use (maybe other) ado.net connector http://www.mysql.com/products/connector/
i didn't tested it, but first you should tell what client library you are using, and what kind of cursor you will use
for server side if think you should do something like:
DECLARE rs CURSOR FOR SELECT * FROM a; OPEN rs; ... fetch .. ... CLOSE rs;
i don't have many information but i think it's a nice way to start...
here the connector-net suppoer community page at mysql manual
i think there's many information there
thank you and i will try it.
please when found the solution, post here :)
may be you doesn't understand my question. i just want MariaDB transfer the result data set to other program,so how can i do it?
from what i know, you should use a mysql client via mysql commmand line program, another mysql program language library (php, perl, python, c, etc...) or another mysql gui program (mysql workbench, heidisql, etc..)
explain what you will use in mysql command line you can do a SELECT * FROM TABLE; and get results on screen... with lib you must write a script or a program (and compile) and write a output for your "result set"
give more information about what you use
Thank you rspadim,the reason for me to ask this question is ,i want change my db from Oracle to MariaDB,in Oracle i have a procedure to transfer the result to other program,for exampler c#. In Oracle i create a procedure to do that,use a cursor for output the result set.but i find MariaDB 's cursor can't do this thing,i mean in MariaDB cursor can't be an output parameter in procedrue. From many references, procedure in Mysql or MariaDB can transfer one result such as int/double or other type,but i doesn't find a example to transfer a data set.Could you give me a example? For example,i have a emp table,the table with 1000 employee's info,such as name,education,birthdate,city and so on,i want find how who's city is "LA".
for select sentence like :select name,education,birthdate from emp where city='LA';
and how can i make a procedure to do that?
well, i don't see a "must use cursor" to a single select fields from table...
check this example with c#... http://www.codeproject.com/Articles/43438/Connect-C-to-MySQL
it's using some commands at c#:
MySqlCommand cmd = new MySqlCommand(query, connection);
Create a data reader and Execute the command
MySqlDataReader dataReader = cmd.ExecuteReader();
Read the data and store them in the list
list.Add(dataReader["id"] + "");
list.Add(dataReader["name"] + "");
list.Add(dataReader["age"] + "");
close Data Reader
could this work for you? i'm thinking this question could be placed at a c# mysql forum, or something like it, since mariadb/mysql at client side are near the same api, just some queries (SQL COMMANDS) that must be rewrite, but most command runs without changes
another doubt... are you talking about pl/sql? or another mysql server side script language? the cursor and procedure are server side command? there's cursors at client/server side, and procedure at client/server side... i think the most common case is the client side cursor/procedure... the example i send is a nice start point, it have select,update,delete,insert, all you need to make some CRUD programs
i think this can help :)
Thanks again Mr rspadim,you have give me a answer for my next question that hasn't been asked:). The question asked above is in server side,i search for something about that and find maybe in Mysql or Mariadb the cursor can't transfer data like Oracle,for another choice,may be i should use temporary table to fetch the result,so if i can make a dynamic temorary table to get the result? For my example, with differert column,i mean in above example, in procedure named as procedure_one,
select name,education,birthdate from emp where city='LA'; and open a cursor for fetch the data to data_table1(temporary table) and next time,i make another procedure named as procedure_two,with the sql sentence like:
select name,birthdate from emp where city='LA'; and i also open a cursor to put the result to data_table1. the second result data set has different column ,in the web reference, the data_table1 should be created before the procedure done,is it really? in Oracle, i just define a cursor and doesn't need to know the result structure,any select result can be send to the cursor,for example:
create or replace procedure test( rs out sys_refcurosr)
begin open rs for select * from emp; end;
no need to define the "rs" before,and MariaDB or Mysql can do the same thing?or must define a temporary table before?
well everything implemented about cursors at server side is here (from what i know):
i understood that you is trying to implement a 'global' cursor, in other words, thread (connection) 1 create the cursor and 'save' it, thread (conneciton) 2 read this cursor value
well i don't know if mysql/mariadb cursors are 'global' (like tables) or if they only exists at procedure scope, but if they aren't i just see the table solution for your problem... create a table, and 'share' this table between server-side procedures, yes the table must be dropped / created in every interaction (INSERT/DELETE/UPDATE/ALTER add column/ALTER drop column)
maybe the best thing to do is send a oracle script example here, and try to rewrite it to mysql, this is better (faster) than trying to answer theory problems (not pratical problems)
check this: http://dev.mysql.com/doc/refman/5.6/en/cursor-restrictions.html
ok i will make a example for that: create table emp ( name varchar(20), age number(2), city varchar(20) );
in oracle create a procedurue:
create or replace procedure find( rs out sys_refcursor) is begin
open rs for select * from emp;
execute this procedure in TOAD i can get the all info about my employees,and how to translate it to MariaDB?
and what you want with procedure find? what you will do next? how other thread will read it in oracle?
i think this is a bit 'impossible' since in mariadb the rs variable is only to function scope, not to global scope... but must check
use pastebin to send examples of code, it's better than this wiki markup
sorry i can't connect to that site.
may be you can send your email address to me.my email address is firstname.lastname@example.org.
in my current db,we use many cursor for transfer data.so if i must create temporary table,that will be a big trouble to me. could you give me some advice? Thank you!