Version Stored Procedures
while calling stored procedures from VB, it displays as
you have error in sql syntax..............near'{call spallocatedock1(61754444,",",1.00000000000000," )}at line 1
will odbc 3.51.12 support stored procedures with output parameters.
View Complete Forum Thread with Replies
Related Forum Messages:
Stored Procedures V 5.0.18?
Is there an issue with stored procedure and mySQL v5.0.18 ? We are programming an application that need stored procedures and triggers and our programmer can't make them work, is it a know issue or our programmer missed something?
View Replies !
IDE For Stored Procedures
I am trying to create stored procedures in MySQL. Does anyone have a suggestion on a good IDE to use. I have been working with the MySQL Query Browser but it generates errors very often and is shut down by Windows. When I make changes to my procedure, it doesn't seem to always change what is being executed. Furthermore, sometimes the procedure just disappears from my database. I am starting to think that the stored procedure language in MySQL is just not reliable. Am I wrong on this?
View Replies !
C Api And Stored Procedures
i've been looking into using stored proecdures, and have got some up and running correctly from the command line, i need to use them from within a c program using the c api. i have the c api set up correctly, and connect to the db and can run queries fine. but if i change my query to calling a stored procedure it doesn't work: "mysql_real_query( mysql, query, strlen(query) )", query = "CALL myStoredProc(21);" can the c api even call a stored proc? i thought this would be okay as running a query is running a mysql query, and i assume this will execute any mysql statement. however my stored proc ALWAYS returns 0 rows affected. the help docs have something called prepared statements - though i think these are different from precompiled stored procs.
View Replies !
Stored Procedures In PHP
How can I call a stored procedure in PHP? I did it like this but it doesn't work. @mysql_connect('lhost','user','pass') or die("ERROR: Unbale to connect to MySQL."); @mysql_select_db("asterisk"); $result = mysql_query("CALL select_all_vicidial_list()"); $num = mysql_num_rows($result);
View Replies !
No Stored Procedures
it looks like there's no support for stored procedures in 4 and lower versions of MySql... so when using the .NET connection to insert data into a table, I have to send some kind of command text insert statement??? is that the only way or is there another way to do this?
View Replies !
GUI For Stored Procedures In MySQL?
I have a website that is using MS SQL Server 2000 with ASP scripting. I want to switch over to MySQL, but I'm just sticking my toes in the water at the moment to see if it makes sense. One of my favorites things about MS SQL is being able to use the Enterprise Manager as a GUI in constructing VIEWS that I later turn into stored procedures. EM makes it super simple to link tables and create a query. It basically writes the source code for you as you go and you can test run it interactively until you get the results you want. I then take the resultant source code and paste it into a stored procedure, which in turn is called by my website script. This allows a relative novice like me to manage a somewhat complex database structure. My question is: does the MySQL GUI allow for the management of stored procedures in a similar manner? Can I create a view and just "cut-and-paste" the source into a dialog box that will then create the stored procedure for me? Does MySQL allow for the visual management of stored procedures within its GUI?
View Replies !
MySQL 5 Stored Procedures
I've recently downloaded the Windows mySQL 5 binaries. Is there any GUI that supports the creation of Stored procedures? I'm using mySQLcc 0.9.3 and this doesn't seem to like them (from a SQL window) unless there is a trick that I'm missing.
View Replies !
Creating Stored Procedures
I need information on "Creating Stored Procedures in MySql 5". Like how it is created and accessed throught PHP.... To begin with lets take database table as "student" with columns roll int(10) primary key + auto increment name varchar(255) address text
View Replies !
Stored Procedures Efficiency
We have a function that generates a large set of data based on certain business logic. Curretly this business logic is handle by php. A very complex php page is written to fetch data from mysql when client browser initiates the process, do the necessary calculations and then write to a new table and then display the output of that table on client browser. Whenw e ar edealing with about 250,000 records and between the time that user initiates the process and see the final result, there is along delay. I am juts wondering if PHP is the best application to use business logic in this type of scenario of is anyone things using mysql stored poceudures may help to handle the business logic. My questions about mysql SPs: 1. Dooes anyone thing it provides more speed and efficiency over our current method. 2. Is it reliable o use SP in Mysql 5? 3. Why people use SP typically?
View Replies !
Run Stored Procedures As Root?
I want to run procedures but i am not able to run it with normal users, i need root access, i can run them without root? Here is the error i am getting: Warning: mysqli::mysqli() [function.mysqli-mysqli'>function.mysqli-mysqli]: (28000/1045): Access denied for user 'root'@'localhost' (using password: YES) in /home/cmela/public_html/database/connections.php on line 4 Error connecting MySQL: Access denied for user 'root'@'localhost' (using password: YES) I need to know can i run procedures without root?
View Replies !
Stored Procedures And Source
I'm trying to script a stored procedure using a .sql file. Having no problems writing the same thing from the command line but I need to have all sp's under source control. This is the syntax in the file I'm using: USE db_1; DROP PROCEDURE IF EXISTS test; CREATE PROCEDURE test() BEGIN SELECT * FROM note; END
View Replies !
STORED Procedures And FUNCTION
CREATE OR REPLACE FUNCTION zap_firm(p_id_firm IN INTEGER) RETURNS INTEGER IS BEGIN DECLARE v_zaplacono NUMBER; FOR cur_fakt IN (SELECT id_fakt FROM Faktury WHERE id_firm = p_id_firm) LOOP FOR cur_wpl IN (SELECT * FROM Wplaty WHERE id_wplaty = cur_fakt.id_fakt) LOOP v_zaplacono = v_zaplacono + cur_wpl.kwota; END LOOP; END LOOP; END;
View Replies !
Stored Procedures Versions?
1) Does the My SQL version numbers vary with operating systems (I mean does the MySQL 5.0 supports same features on linux and Windows)? 2)Do any version MySQL support the "Stored Procedures" on Redhat linux ver 9?
View Replies !
Stepping Through Stored Procedures
I have been working with stored procedures and would very much like to "step" through them, much as you can in the VBA module in microsoft Access. It looks like there is functionality built into MySQL Query Browser but I cannot for the life of me work out how to create break points anywhere other than the beginning of my stored procedures. I've tried using begin / end blocks but to no avail, not discounting the fact that I am doing something wrong with them.
View Replies !
Stored Procedures Result Set
Simple question, however I don't find answers anywhere, neither the reference. I have a stored procedure that works fine, it's called from a script and returns a dataset with, for example, 100 rows. Procedure is called, result set is returned, and from now on, ¿how can I access these rows from the same script? I miss something like: -- Sorry about this heresy! SELECT OneColumn from (CALL MyProcedure())
View Replies !
Stored Procedures & Transactions
I wonder whether it is possible to use transactions like this: CREATE PROCEDURE testproc BEGIN START TRANSACTION; CALL another_proc(); COMMIT; END; Or is there anything I need to take care of? The reason I am asking is, I have a project with lots of SPs and I need to add transactions now.
View Replies !
Example Scripts For Stored Procedures?
I was wondering if there are somewhere example scripts available that make heavy use of stored procedures / triggers (InnoDB)? It's easier to learn from examples than reading a book that will take forever to finish.
View Replies !
Transactions In Stored Procedures
I am handling transactions in stored procedures. Now, if due to some error in the middle of the stored procedure, control comes out of the stored procedure. But, my transaction that was started in the beginning of the procedure has not ended. Therefore, it is going to effect my application. Please tell me what should be done. Whether some error handling is the solution just like in java, .net languages. Or what else?
View Replies !
Alternative To Stored Procedures?
I would like to experiment with procedures, but in my shared hosting service I don't have write access to 'mysql.proc'. Is there any alternative that I can use that doesn't require super user privileges? I'm just experimenting with "advanced features" right now, just to experiment. I wish I could make a BEGIN-END block outside a procedure, but apparently I can't. Ditto for DECLARE, WHILE and all the neat things that make procedures special.
View Replies !
PHP5 And Stored Procedures
Not sure if this is the right forum as it's partly a PHP question. I have set up the mysqli extension and created all the correct permissions in the database. The problem is that I have several queries on the page, only one of which is a stored procedure (the first one). As soon as I run the SP the other normal queries fail, with no error message. If i change back from the SP and replace it with a straight query everything works.
View Replies !
Stored Procedures In A Client
Does anyone know if you can gain access to OUT variables in a client application when calling a stored procedure through the C API. I could just use SELECT to pass the results of my query back and then use binding to get these, but I would love to use the OUT variables.
View Replies !
Variables In Stored Procedures
I need a stored procedure to accept a table name as an argument and create the table if it doesn't exist. When I use the following syntax the table created is named "TableName", not the value of the variable TableName. How do I do this? CREATE DEFINER=`root`@`localhost` PROCEDURE `MakeTable`(TableName varchar(16)) BEGIN CREATE TABLE IF NOT EXISTS TableName(FirstRow varchar(16)); END
View Replies !
Understanding Stored Procedures
I've never used stored procedures and need to understand what they are exactly. I want to know if a stored procedure can do this: Looking at the picture above, is the stored procedure table placed in the database? I'd like a way to access the average ratings fast without bogging down my server. For instance, if the averaged ratings table is updated once an hour even once a day, I'd be happy. I just don't want the averages being calculated everytime any user wants to see another users average. Note that I may not need all of the users ratings all the time. I may just need one, five, or ten at a time. Think of these averages as the star ratings each book has on amazon.com. Their servers would crash if they had to keep calculating the exact star rating for each book. Is this what stored procedures do? Someone please shed some light on the subject.
View Replies !
DB Structure And Stored Procedures
I`ve got a few questions about DB structure for which diagram I`ve attached to this post. 1. What do you think about it, generally 2. I expect a lot of records to be stored in News and Events tables, so how should I manage with the oldest ones? I think, to optimize receiving latest data, the oldest records should be removed from main tables News/Events to tables like ArchivedNews/ArchivedEvents. But what about tables: News_has_ImageFiles, Events_has_ImageFiles. They should be created together with Archived* ones? Or maybe I got it to complicated. All I know is that all data is important and after a while they should be dropped into archives. 3. What do you think about stored procedures especially in case of simple queries like "select * from x". I study with guy who loves SP so much that he creates it for even such queries. He explains that all logic is on the DB side and that`s why it`s easier to manage and secure db. He is a C# + MSSQL programmer. But, how about SP in PHP+MySQL environment? On the one hand less data is sent by client to server. On the other hand DB is doing everything so it might be overloaded when too many requests are sent. What is your opinion about it?
View Replies !
Returning Msg From Stored Procedures
I`m learning how to use SP in MySQL and have some doubts about returning msg from SP. I`ve done sth like this: SQL DELIMITER $$ DROP PROCEDURE IF EXISTS `site`.`addContact` $$CREATE DEFINER=`root`@`localhost` PROCEDURE `addContact`(IN c_type VARCHAR(30)) BEGIN DECLARE msg VARCHAR(100); IF TRIM(c_type)<>'' THEN IF (SELECT COUNT(*) FROM contacts WHERE `type`=c_type)=0 THEN INSERT INTO `contacts` (type) VALUES (c_type); SET msg='Successfuly added' ELSE SET msg='Already exists' END IF; ELSE SET msg='Type name must be provided.' END IF; SELECT msg; END $$ DELIMITER ; SP returns always one msg and it`s easy to use it later in app code i.e. PHP. I`d like to ask if this aproach (with select returning msg) is acceptible? Or maybe I should use OUTPUT variable inside SP to get such info?
View Replies !
Question About Stored Procedures
I have never used stored procedures in the past. Just the other day I started to ponder if SPs are beneficial for getting around long query parsing. For example, on my website I have a query that is 300 characters in length and selects 30 columns. THIS IS A HUGE query; so I would assume that the database query parser has to do a heck of a job computing the query. SELECT column1,column2...column30 FROM profiles WHERE user_id=[USER_ID] LIMIT 1 Also since the database is on different server, the PHP server has break it up into packets and then send the request to the database server. Then you know what happens from there. But if Stored Procedures were to be used, then the the query would only be CALL profiles([USER_ID]); now this query is 16 chars in min and 22 in max (ranging from user_id 1 to 100,000+). Soooooooo, when a stored procedure is setup in MySQL, is the original query just masked with a function? Or does MySQL optimize the query to a more native database request (something that the database can understand more efficiently than a regular query)? And is this solution going to benefit me in any way?
View Replies !
Stored Procedures + Permissions
I'm on a shared host with MySQL 5.0.33. I want to use stored procedures and functions. Unfortunately I'm getting an error message when I try to create a new function. For example: SQL CREATE FUNCTION `gutenTag`() RETURNS varchar(20) CHARSET utf8 RETURN 'Guten Tag' ... ends with: #1044 - Access denied for user 'blah'@'%' to database 'blahblah' How can I check if I have permissions to create procedures/function?
View Replies !
ASP.net And MySQL And Stored Procedures
Hopefully this is simple and I'm making too much of it... here it goes... First part of the question (which I'm hoping most can answer) I have googled and googled and gave up in the end of trying to find a clear example of creating a stored procedure USING a parameter in MySQL. ANY help or simple direction to a tutorial would be awesome. Second... I have downloaded the evaluation of Visual Studio 2005 to get used to asp.net. I'm using MySQL database, please note I have installed and patch all the revelant dot net components and plugins to get it to create a connection string to MySQL Server. Though it doesnt look liek the VS GUI wants to communicate with MySQL leaves it up to you to create the SQL. (Which isn't a problem.) But... I want it to work with Stored Procedures (you see Visual DOES let you use exisiting stored procedures contained within your database server) .... Which points back to question one, but anyway the point of question two is has anyone else experience an attempt to get MS Visual Studio 2005 and MySQL working together? Note: I can already pull in data from MySQL using coded connection string within the backend code of a .net page.
View Replies !
Debugging Mysql Stored Procedures
I serfed web to find some kind of mysql stored procedure debugger. But I found nothing. Last week at last a tool with integrated debugger was released. It uses debug engine. like an oracle dbms_debug, and supports breakpoints, watches, call stack, and stepping through code facilities. May be it can help you developing complex stored routines.
View Replies !
Huge INSERTs And Stored Procedures
Started with PHP and CSS this year and got into it quickly, so I was thinking of starting a big project (a statistics site for an MMO with about 10000 players) to teach myself MySQL(+Stored Procedures) and AJAX also. The load of data turned out to be a higher bigger than expected, so I'd need some advice on how to handle bigger quantities of rows simultaneously. The entire process is like this: ...
View Replies !
Stored Procedures & Filesystem Functions
I've seen this done with MSSQL, I'm wondering if there is a way to do it with MySQL 5.0. I want to have a table where each record stores some meta data about a file. Instead of having a longblob column to store the binary of the file in, I'd prefer to just write each file to disk and name it with the auto increment value of the table. If I delete a row, I want it to automatically delete the associated file from the disk as well. The problem is that I don't see any filesystem functions. Is this functionality missing from MySQL?
View Replies !
Creating Transaction In Stored Procedures
I am working on a project at University using php, mysql and apache. The web application should should interract with the database system using stored procedures and transactions. I know how to create stored procedures in mySQL but I am not sure how to implement the transactions into it. The following php code needs to be stored procedure in mySQL: <? php $query = "SELECT * FROM item ORDER BY name"; @mysql_query("BEGIN"); $result=mysql_query($query, $this->db_connection); if(!$result) { @mysql_query("ROLLBACK"); return $result; } else { @mysql_query("COMMIT"); return $result; } ?>
View Replies !
Stored Procedures - Locking Tables
I've been searching the net for an example of a stored procedure which also locks a table while retrieving Data. I tried the LOCK and UNLOCK commands but received an error , that these commands cant be used in a stored procedure. I´ve read other posts to place the LOCK and UNLOCK outside of the procedure,.. Can somebody give me a simple example of locking and unlocking. DELIMITER $$ DROP PROCEDURE IF EXISTS `tests`.`SpLocking` $$ CREATE PROCEDURE `tests`.`SpLocking` () BEGIN /* Place a LOCK while reading data.*/ SELECT * from Customers; /*Unlock once read*/ END $$ DELIMITER ;
View Replies !
Stored Procedures From Text File.
I have written stored procedures using SQLyog. and I am running these stored procedures with the help of C#.Net. My C# program create tables and insert values with the help of C# using stored procedures. But for this I have created the stored procedure in database with the help of SQLyog.Now if I want to run this C# program on some other m/c then I need to again create or copy or restore the stored procedure in the database ( which I dont want to do). My problem can be solved if I can save the stored procedure in some text file or some other file and then will create the stored procedures in database by using C# with the help of this text file.
View Replies !
Configuring Security For Stored Procedures
I have a test DB with 4 tables. I also have around 20 Stored Procedures that manipulate these tables. My test application uses only these Stored Routines to SELECT,INSERT,UPDATE and DELETE data from 4 tables. In other words, all the interaction with the Test DB is performed through these Stored Routines. My application is an ASP.NET project which uses .NET connector to talk to MySQL. I decided to tighten the security on my system and created a test user account. (CREATE USER `TestMySQLUser`@`localhost` IDENTIFIED BY PASSWORD '6872364823746FC76CA82G73648237BB462876'; -- I made up the hash here but you get an idea) I then granted this test account the "Execute" permission to the 20 Stored Routines. The idea here was to only allow this test account call the SPROCS and nothing more. The SPROCS then will do all the work. However, when I try to run my app, I am getting an error: System.ObjectDisposedException: Cannot access a closed Stream. Basically, if I play with the security and allow my test account to have SELECT access to some of the tables in my DB as well as in MySQL db then things work. Is this expected? Am I doing something wrong? As I mentioned above, I only want the test account to have "EXECUTE" permission to the SPROCS. nothing else. What is the minimum privileges I need to accomplish the above?
View Replies !
Mysql And Triggers/stored Procedures
I have a question regarding the use of triggers. I have difficulties implementing trigger that let's say reacts before/after select statement (select of user), and increments the counter value (of the selected user). Is there a way to achieve this through triggers, or it's not possible?
View Replies !
Stored Quieries / Procedures And Relationships
I've only ever used Access, and i started using mysql on monday, two questions i have? How do i create stored procedures? I know in access, this speeds things up abit, is it the same for mysql? Is it worth the trouble, as mysql is more robust and can handle more users?and how to i creat relationships between tables?
View Replies !
|
TRACKER
