Callable code installed on a database manager, exposing an API through which it can be invoked. Normally written in the native query language, some DBMS platforms support other languages as well.

learn more… | top users | synonyms

3
votes
0answers
64 views

creating deadlock for distributed database in mysql-(Testing purpose)

I need to create a deadlock for distributed database in MySQL for testing purpose with a simple sql query(without front end), can anyone good with MySQL query help me out in this with sample query. ...
2
votes
0answers
33 views

Mysql stored procedure SQLEXCEPTION

Below is my sample query, in which I first create new folder then update count then create a new tag and finally get new id. insert into folder (name)values("new"); update folder set count = count + ...
2
votes
0answers
33 views

Mysql log table

I have a logging table that contains 3 fields Id action time The time column is 11 digit integer. Our goal is that each month the logs of the prior month will be deleted. It is possible to do with a ...
2
votes
0answers
24 views

Insufficient Prilivege for Function

I'm using a Function that I can't read, when I try to open it, I can't see the definition. I get following error Error : Insufficient privilege. Nevertheless, it is being executed by my app ...
2
votes
0answers
595 views

Rolling Back Stored Procedures in Entity Framework

I currently working a on project that operates within a transaction in entity framework. Before now all of the changes were made using a DataContext. I need to add a call to a stored procedure using ...
2
votes
0answers
125 views

Why does MySQL force the tradeoff between swallowing errors and leaking commits in transactions?

Referring specifically to the choice of whether to use DECLARE EXIT HANDLER FOR SQLEXCEPTION. If you use it, you swallow errors and there doesn't seem to be any way to access them programmatically, ...
2
votes
0answers
1k views

How to return all values from a stored procedure casted as varchar(max) in sql server due to PHP mssql_query() ntext issue?

This is more of a concept/how-to question rather than about actual code. I can do the programming but I just need to know what direction to head... I was going to post this on StackOverflow but it's ...
2
votes
0answers
157 views

Requesting advice on creating a table controlled SQL job scheduler

I have been tasked with cleaning up a series of SQL jobs that send email updates about various data and system conditions in my company. Currently, there are a ton of jobs, each with its own SQL code ...
1
vote
0answers
40 views

Stored procedure not running on the same database when using different login

I am trying to run a stored procedure on the same database using two different login users. While using the Windows authentication , I'm able to run the stored and proc and see all the data available. ...
1
vote
0answers
46 views

MySql - view out params of stored procedure when it selects from table

I have stored procedure: DELIMITER // CREATE PROCEDURE prcGetFemaleUsers() begin select firstName, lastName from tblUser where gender = 'female'; end // DELIMITER ; How can I get ...
1
vote
0answers
93 views

MySQL: Slow Query Performance - Parameter Sniffing?

For a personal weather project, I am trying to define and run the following stored procedure: DROP PROCEDURE IF EXISTS history_between$$ CREATE PROCEDURE history_between (IN date_start DATETIME, ...
1
vote
0answers
27 views

Oracle 11g XE - Giving granting privileges to a stored procedure

I need to use a stored procedure to give certain privilges over a view to users. However I don't know how to give the procedure the necessary privileges to grant users privileges.
1
vote
0answers
11 views

Adding new sproc to the exisiting database using connectors?

The project I'm currently working on is mostly pythonic, by this reason I'll likely use mysqldb python connector or sqlalchemy. The question arose when I've tried to find out the way of adding new ...
1
vote
0answers
226 views

Why my sql query run faster than my stored procedure?

I am trying to write a script in MYSQL Workbench to update my database every day using stored procedure but I find it too slow compared normal query or temporary tables. If i use temporary table the ...
1
vote
0answers
49 views

Read show create table in mysql stored procedure

How can I read the output of following query and assign to a stored procedure's variable? SHOW CREATE TABLE users; Or, is there is an information schema from where I can draw the full table ...
1
vote
0answers
256 views

Return both Result set AND a warning from a stored procedure

In Data Studio when you call SYSPROC.ADMIN_CMD() directly and it returns a warning, the results panel shows both the warning and the result set: CALL SYSPROC.ADMIN_CMD('EXPORT TO ...
1
vote
0answers
484 views

Clarification on usage of Serially Reusable Pragma for Oracle 10g

I need to make packages for Oracle 10g that can be changed while users are still logged in an using an application that makes database calls through PL/SQL. Unfortunately, anytime a package is updated ...
1
vote
0answers
51 views

When will store procedure/functions support result set as return type

I have noticed that MySql doesn't support result-set on a store procedure and functions, which means return a table. So I can't do things like CALL sp_my_store_procedure() ORDER BY id ASC; Or ...
1
vote
0answers
49 views

Is there a way to call sp_spaceused in a WHILE loop?

A legacy Sybase script moves records from production to archive in 1000-row batches, but it does not check for space exhaustion on the archive. Is there a way, maybe by calling sp_spaceused, to ...
1
vote
0answers
32 views

TSQL Stored Procedure - Granting general user access to specific sproc

I'm attempting to alter a sproc that needs the line added : grant exec on dbo.sproc_name to exec_proc. I believe the admin setup the exec_proc as a group. I added this grant line following the final ...
1
vote
0answers
227 views

How to access another db from a stored procedure in db2

I want to query a table in another db from within a stored procedure in db2. For example, I am connected to db_a1, and call a stored procedure from within which I need to connect to a table in db_a2. ...
1
vote
0answers
1k views

exec sp_getapplock - Different behaviour using go or without go

we have a strange behaviour while using sp_getapplock (we tried to use this to solve a problem with a deadlock involving some insert while another session was trying to execute a ALTER SWITCH ...
1
vote
0answers
249 views

Storing changes to stored procedure and triggers in PostgreSQL

I want a mechanism where I can store/track changes in stored procedure and triggers. I want to track these logs in some table. What is the best possible way ?
1
vote
0answers
285 views

user rights validation inside stored procedure?

You have an application with a Users table, and the Users table has various columns to indicate what rights a User has within the application. Ignoring for the purposes of this question what should ...
1
vote
0answers
147 views

Using #temp to work around Reporting Services blocking issue

Having utterly failed to resolve why a perfectly acceptable but admitidly large stored procedure was suffering the blocking process lck_m_s when called via a report in SQL Reporting Services I have ...
1
vote
0answers
220 views

Cumulative Function in MySQL

i have a problem creating cumulative function in mysql database. let say i have a stored procedure which getting data and counting quantity/sum_quantity example A table consist of consumer goods ...
1
vote
0answers
2k views

Does MySQL close cursor if stored procedure encounters an exception?

I've seen two different approaches of cursor handling is MySQL stored procedures. Now I'm curious about their stability, due to potential MYSQLEXCEPTION risks in stored routines. Assuming, that ...
1
vote
0answers
628 views

Create stored procedure to select the data?

I have four tables user, shops, offer, merchant in that id is unique and present in all table shop ------ id shops 1 shop1 1 shop2 1 shop3 user ----- id name 1 abc merchant ...
1
vote
0answers
32 views

What might cause all objects in an Oracle database to suddenly have quoted identifiers?

I have an Oracle schema in which, at some point in the past ("the event"), all of the procedural code objects in the database have been re-created with quoted identifiers. All of the objects are ...
1
vote
0answers
1k views

stored procedure returns ERROR 1305 (42000): FUNCTION does not exist

I have a stored procedure and function as below CREATE PROCEDURE ProcName() begin .... DECLARE curs CURSOR FOR select A.PList, B.nid from persons A inner join articles B on A.aid=B.id; .... ...
0
votes
0answers
8 views

Every “INSERT” query check a combination of column's records

Unfortunately, my mind has stacked right now and i would like to design a really simple DB schema. What i want? In a simple table, for instance Table1, there are two different columns, Series and ...
0
votes
0answers
10 views

Is it possible to call java API from mysql stored procedure?

I am using stored procedure to migrate data from one schema to another schema. I need to use some complex logic which is already implemented as java API. Is it possible to call java API from mysql ...
0
votes
0answers
16 views

Is there an alternative to delete a procedure inside a procedure?

For my Bachelor Thesis I try to automate some recurring tasks in MySQL. One task consists of the deletion of a specific stored procedure. Normally I would create a stored procedure which takes one ...
0
votes
0answers
18 views

Syncing DB's between local and live server?

I need to sync two tables between MySQL databases. I have read about MySql Replication. But not sure whether it can be used in this scenario. What I want to achieve is explained below. There are two ...
0
votes
0answers
10 views

Fire trigger when Routine was created in MySQL

I try to track changes of Stored Procedures and UDFs in MySQL. That means, if a SP or UDF is created, I want to copy the information into another table. I tried to clone the ...
0
votes
0answers
33 views

Change MySQL stored procedure 'Database Collation' name

I have imported a DB from Local machine to server machine. While importing the Database, the character set values of the DB are set by system default to "Latin". I have changed the character set to ...
0
votes
0answers
15 views

calling multiple procedures from my application Vs calling procedures inside another procedure

I have a scenario of saving user details into my database(MySQL 5.1); i have three tables for saving user details; user_data,user_contact,user_address, so if i insert one user data i have to check for ...
0
votes
0answers
27 views

MySQL performance affected by calling functions from inside procedures?

this is the very first question I post, so please bear with me ... When working on a project, I tried to get the LAST_INSERT_ID for a table, by invoking it from inside a procedure right after an ...
0
votes
0answers
40 views

MySQL prepared statement error

Good afternoon, I'm trying to use a prepared statement within a stored procedure in MySQL, but when I attempt to link the variable I get a syntax error. Here is the code SET _sql = ...
0
votes
0answers
28 views

Set a variable outside loop and fetch it every item of loop

My oracle version : Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi I'm trying to set a variable outside loop and make its value change as the each item of loop. (Just like we ...
0
votes
0answers
34 views

MySQL procedure with sys_eval (mysqludf) called by trigger

I'm executing a trigger after a row in a certain table has been updated. That trigger calls another procedure which is supposed to do some quick checks before executing a small pythonscript. If the ...
0
votes
0answers
51 views

Creating audit triggers in a generic way for mysql tables

We have many many tables in our operational database. We would like to create an audit table for each one of them. We would like to create a trigger for each of our tables that will simply insert an ...
0
votes
0answers
12 views

Automatic creation of MySQL triggers

I would like to automatically create triggers for several table as part of the deployment process. trigger example: CREATE DEFINER=`root`@`localhost` TRIGGER xxx_admin.payment_AINS AFTER INSERT ON ...
0
votes
0answers
55 views

Trigger (insert/update) and procedure Required to encrypt data in MySQL

Is there any good way of doing encryption/decryption by use of triggers and procedure in MySQL. Looking for following to achieve in sequence Unload data for specific column to some other ...
0
votes
0answers
55 views

Postgres asynchronous procedure execution?

I have an ETL script entirely contained within a Postgres 9.3 procedure. The script takes several hours to process, which is fine for our purposes. What I haven't been able to find out is if there ...
0
votes
0answers
32 views

I currently have a stored procedure that contains 5 joins, would it be more efficient to implement it as a view?

My current stored procedure is badly written and needs to be refactored. I've managed to find the time to do that. This procedure will be called every two minutes or so, and as more products are sold ...
0
votes
0answers
47 views

Varchar array as an input to Oracle stored procedure

I'm new to dynamic SQL execution. I'm working on an application which will take a list of input from an excel and generate a report out of it. I'm preparing the input data in the format required by ...
0
votes
0answers
87 views

Method to economically insert Bill of Materials BOM items into a Stock Table?

I’m new to the wonderful world of database design and I’m currently experimenting with an implementation for my little brother relating to Model Train Sets. [I will be using SQL Server 2012 / Asp.Net ...
0
votes
0answers
46 views

stored procedure to display error messages & success when a sp_refreshview is ran

How can I convert this stored procedure to display error messages when a sp_refreshview fails? I need to know which view failed. I would also like to know which "database_name.SQL_View_object_name" ...
0
votes
0answers
70 views

call a stored procedure from sql window in PHPMyAdmin

I have a simple stored procedure that selects rows from a table. In phpMyAdmin, in the list of my procedures, if I click the Execute option, it executes properly, returning the desired rows, and ...