stored-procedures

versioning stored procedures/PLSQL?

Is there an efficient way to version store procedures written in PL/SQL? (I only mention PL/SQL because there may exist a particular tool, any answer directed to versioning of stored procedures is ideal). ...

populating bean objects from the resultset

I am facing a problem regarding populating bean objects from the resultset. Description:Resultset contains the result of a stored procedure which is join of 3 tables B, BO, and BOV. I have 3 POJO's corresponding to tables. The relation between tables is: B can have 0 or more BO's, BO can have 0 or more BOV's. So totally in resultset I ...

How to convert int to char with leading zeros ?

I need to convert int data table field to char leading zeros example: 1 convert to '001' 867 convert to '000867' thx. ( This is my response 4 Hours later ... ) I tested this T-SQL Script and work fine for me ! DECLARE @number1 INT, @number2 INT SET @number1 = 1 SET @number2 = 867 SELECT RIGHT('000' + CAST(@number1 AS NCHAR(3)...

Capture Stored Procedure print output in .NET (Different model!)

Basically, this question with a difference... Is it possible to capture print output from a TSQL stored procedure in .NET, using the Entity Framework? The solution in the other question doesn't work for me. It works with the connection type from System.Data.SqlClient but I'm using the one from System.Data.EntityClient which does not ha...

Hibernate and stored procedures

As my understanding on setting hibernate, I need to create table meta data file (person.hbm.xml), include all the fields mapping java object (person.java) If we use stored procedures for all transaction, do we still need the above configuration? It seems hibernate and stored procedures will overlap, We set up the stored procedur...

Does MySQL have time-based triggers?

I would like to execute a procedure periodically, how to do that in MySQL? ...

Trying to insert a row using stored procedured with a parameter binded to an expression.

Environment: asp.net 3.5 (C# and VB) , Ms-sql server 2005 express Tables Table:tableUser ID (primary key) username Table:userSchedule ID (primary key) thecreator (foreign key = tableUser.ID) other fields I have created a procedure that accepts a parameter username and gets the userid and inserts a row in Table:userSchedule Problem: ...

Export products and variants from SQL Server

I have a SQL Server DB that has a table of products, and another table which contains a list of the sku variants of each product if it has one. I want to export all the products and their SKU's into excel. At the moment, I have a helper SQL function which performs the subquery against a product_id and concatenates all the SKU's into a c...

SimpleJdbcCall ignoring JdbcTemplate fetch size

We are calling the pl/sql stored procedure through Spring SimpleJdbcCall, the fetchsize set on the JdbcTemplate is being ignored by SimpleJdbcCall. The rowmapper resultset fetch size is set to 10 even though we have set the jdbctemplate fetchsize to 200. Any idea why this happens and how to fix it? Have printed the fetchsize of resultse...

Long Running Stored Proc - Report Progress Using BackgroundWorker & Timer

While a long running stored proc (RMR_Seek) is executing (called via a Linq-To-SQL data context) I am trying to call another stored proc (RMR_GetLatestModelMessage) to check a table for the latest status message. The long running stored proc updates the table in question with status messages as it executes. I want to display the status...

Is it necessary to escape parameters for stored procedures?

Or the call to real_escape_string() in such cases is not needed? /Email value comes from user input./ function findUser($email) { $mysqli = connectDB(); $email = $mysqli->real_escape_string($email); $query = "CALL FindUser('{$email}')"; // ... } ...

ASP.NET MVC 2.0 Stck Overflow when loding sproc in partial view

Hi! Please forgive any newbie mistakes. I'm writing a receipt detail lookup tool in MVC 2.0 to pull up receipt details from our POS database. I have the stored procedures written that return the data that I need. I have my sprocs loaded in a repository. When I try and load these sprocs in a partial view I get a stack overflow error. If ...

ExecuteNonQuery on a stored proc causes it to be deleted

This is a strange one. I have a Dev SQL Server which has the stored proc on it, and the same stored proc when used with the same code on the UAT DB causes it to delete itself! Has anyone heard of this behaviour? SQL Code: -- Check if user is registered with the system IF OBJECT_ID('dbo.sp_is_valid_user') IS NOT NULL BEGIN DROP...

Code a stored procedure that get one single record or all records from a table

I'm searching for the cleanest solution here. I would like to code a stored procedure that should retrieve one record if the proper key is passed in input, or all the records if it is called without parameter. Table FOO has two fields, CODE and DESCRIPTION. In Sql server 2008, i usually create a stored procedure with like this: CREATE...

How can I call a PL/SQL procedure without specifying a variable to hold its OUT parameter?

I'd like to call a PL/SQL stored procedure that has an OUT parameter specified, but I don't care about the return value. I just care that the procedure executed successfully, i.e. no exceptions thrown. Do I have to define a dummy variable in my calling PL/SQL block to receive the out parameter even though I don't want it? It clutters ...

How to call MySQL stored procedure by using prepared statements in PHP via mysqli?

Can someone provide a working example in which stored procedure returns a recordset and is called by using prepared statemet? ...

Calling oracle stored procedure from Excel - VBA

I have to execute an Oracle stored procedure from vba (Excel) with around 38 input parameters. The stored procedure will insert some values in the destination table once that is executed. When it is executed through VBA the number of fields which is inserted is less than when it is executed directly from the backen...

Oracle storeprocedures

Hi, Since I am new to oracle , please tell me what different ways to find packages , store procedures, triggers ,functions, indexes, tablespaces Thanks ...

How hard would you try to make your SQL queries secure?

I am in a situation where I am given a comma-separated VarChar as input to a stored procedure. I want to do something like this: SELECT * FROM tblMyTable INNER JOIN /*Bunch of inner joins here*/ WHERE ItemID IN ($MyList); However, you can't use a VarChar with the IN statement. There are two ways to get around this problem: (The Wr...

Sync Framework: Can I syncronise Stored Procedure

Does the sync framework allow me to synchronise (download only) the Store Procedures on a sql server 2008 server with the Stored procedures on the SQL Server 2008 client? i.e. overwrite the SP on the client when the server SP's have been modifed or new ones created? Thanks ...