Search Results

Search found 8893 results on 356 pages for 'stored'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 28/356 | < Previous Page | 24 25 26 27 28 29 30 31 32 33 34 35  | Next Page >

  • Storing PLSQL stored-procedure values in Oracle memory caches for extended periods

    - by Ira Baxter
    I am collecting runtime profiling data from PLSQL stored procedures. The data is collected as certain stored procedures execute, but it needs to accumululate across multiple executions of those procedures. To minimize overhead, I'd like to store that profiling data in some PLSQL-accessable Oracle memory-resident storage somewhere for the duration of the data collection interval, and then dump out the accumulated values. The data collection interval might be seconds or hours; its ok not to store this data across system boots. Something like session state in web servers would do. What are my choices for storing such data? The only method I know about are contexts in dbms_sessions: procedure set_ctx (value in varchar8) as begin dbms_session.set_context ( 'Test_Ctx', 'AccumulatedValue', value, NULL, 'ProfilerSessionId' ); end set_ctx; This works, but takes some 50 milliseconds(!) per update to the accumulated value. What I'm hoping for is a way to access/store an array of values in some Oracle memory using vanilla PLSQL statements, with access times typical of array accesses made to package-local arrays.

    Read the article

  • calling a stored proc over a dblink

    - by neesh
    I am trying to call a stored procedure over a database link. The code looks something like this: declare symbol_cursor package_name.record_cursor; symbol_record package_name.record_name; begin symbol_cursor := package_name.function_name('argument'); loop fetch symbol_cursor into symbol_record; exit when symbol_cursor%notfound; -- Do something with each record here, e.g.: dbms_output.put_line( symbol_record.field_a ); end loop; CLOSE symbol_cursor; When I run this from the same DB instance and schema where package_name belongs to I am able to run it fine. However, when I run this over a database link, (with the required modification to the stored proc name, etc) I get an oracle error: ORA-24338: statement handle not executed. The modified version of this code over a dblink looks like this: declare symbol_cursor package_name.record_cursor@db_link_name; symbol_record package_name.record_name@db_link_name; begin symbol_cursor := package_name.function_name@db_link_name('argument'); loop fetch symbol_cursor into symbol_record; exit when symbol_cursor%notfound; -- Do something with each record here, e.g.: dbms_output.put_line( symbol_record.field_a ); end loop; CLOSE symbol_cursor;

    Read the article

  • Creating stored procedure having different WHERE clause on different search criteria without putting

    - by Muhammad Kashif Nadeem
    Is there any alternate way to create stored procedure without putting all query in one long string if criteria of WWHERE clause can be different. Suppose I have Orders table I want to create stored procedure on this table and there are three column on which I wnat to filter records. 1- CustomerId, 2- SupplierId, 3- ProductId. If user only give CustomerId in search criteria then query should be like following SELECT * FROM Orders WHERE Orders.CustomerId = @customerId And if user only give ProductId in search criteria then query should be like following SELECT * FROM Orders WHERE Orders.ProductId = @productId And if user only all three CustomerId, ProductId, and SupplierId is given then all three Ids will be used in WHERE to filter. There is also chance that user don't want to filter record then query should be like following SELCT * FROM Orders Whenever I have to create this kind of procedure I put all this in string and use IF conditions to check if arguments (@customeId or @supplierId etc) has values. I use following method to create procedure DECLARE @query VARCHAR(MAX) DECLARE @queryWhere VARCHAR(MAX) SET @query = @query + 'SELECT * FROM Orders ' IF (@originationNumber IS NOT NULL) BEGIN BEGIN SET @queryWhere =@queryWhere + ' Orders.CustomerId = ' + CONVERT(VARCHAR(100),@customerId) END END IF(@queryWhere <> '') BEGIN SET @query = @query+' WHERE ' + @queryWhere END EXEC (@query) Thanks.

    Read the article

  • Listing down stored records localStorage | Google Chrome Extensions

    - by Jamie
    How do I get to list down the stored records in the for loop? Basically I want it to list down the records like this: '<div id="record_' + number + '">' + localstorage value + '</div>' The number in the class should add 1 every record, e.g. 1, 2, 3, 4 every record it lists down, and so on. The localstorage value should show the localStorage[] but the problem is, the localStorage name has the same, e.g. (clicks on button) it will save the value of the URL into a localStorage I then open the application and shows the window.html inside there is list of stored records by using this: '<div id="record_' + number + '">' + localstorage value + '</div>' INCLUDING the record number to add per record 1, 2, 3, etc... like this: <div id="record_1">localstorage value</div> <div id="record_2">localstorage value</div> <div id="record_3">localstorage value</div> <div id="record_4">localstorage value</div> <div id="record_5">localstorage value</div> <div id="record_6">localstorage value</div> etc...

    Read the article

  • Entity Framework and stored procedure returning temp table Issues

    - by kaplooeymom
    (Disclaimer - I'm not the database designer. I'm just the poor developer what has to make this work.) There are 17 (at the moment) tables with identical structure - name, address, phone number. Given a phone number, I have to check to see if there's a matching entry in any of the tables, then return that address. So, I created a view to get the list of tables (there's a ref table that holds that info), then I created a stored procedure to create a temp table, using cursors, check each table in the view for the phone number, using sql concatenation. If a record is found, insert it into the temp table. return the rows from the temp table. This all works in straight T-SQL. Now, I'm trying to use Entity Framework 4+ to call the stored procedure. But the function import interface won't generate columns. It says return type = none, and the LINQ code expects an int and won't compile. Any ideas on how to make this work? I know I can move the check tables part to code, if I absolutely have to, but I'd rather have the above method work.

    Read the article

  • Passing Binary Data to a Stored Procedure in SQL Server 2008

    - by Joe Majewski
    I'm trying to figure out a way to store files in a database. I know it's recommended to store files on the file system rather than the database, but the job I'm working on would highly prefer using the database to store these images (files). There are also some constraints. I'm not an admin user, and I have to make stored procedures to execute all the commands. This hasn't been of much difficulty so far, but I cannot for the life of me establish a way to store a file (image) in the database. When I try to use the BULK command, I get an error saying "You do not have permission to use the bulk load statement." The bulk utility seemed like the easy way to upload files to the database, but without permissions I have to figure a work-a-round. I decided to use an HTML form with a file upload input type and handle it with PHP. The PHP calls the stored procedure and passes in the contents of the file. The problem is that now it's saying that the max length of a parameter can only be 128 characters. Now I'm completely stuck. I don't have permissions to use the bulk command and it appears that the max length of a parameter that I can pass to the SP is 128 characters. I expected to run into problems because binary characters and ascii characters don't mix well together, but I'm at a dead end... Thanks

    Read the article

  • Predefined column names in SQL Server pivot table

    - by Marcos Buarque
    Hi, the other day I opened a topic here in StackOverflow (stackoverflow.com/questions/4663698/how-can-i-display-a-consolidated-version-of-my-sql-server-table). At that time I needed help on how to show data on a pivot table. From the help I got here in the forum, my research led me to this page about dynamic SQL: www.sommarskog.se/dynamic_sql.html. And then it led me to this awesome SQL script by Itzik Ben-Gan that will create a stored procedure that outputs a pivot table exactly the way I want: sommarskog.se/pivot_sp.sp. Well, almost. I need one change in this stored procedure. Instead of having dynamic column names pulled from the @on_cols variable in the SPROC, I need the output table to hold generic column names in simple ASC order. Could be, for example, col1, col2, col3, col4 ... The dynamic column names are a problem for me. So I need them named by their index in the order they appear. I have tried all sorts of things changing this great SQL script, but it won't work. I did not paste the code from the author because it is too long, but the link above will get us there. Any help appreciated. Thank you very much

    Read the article

  • Stored Procedure Does Not Fire Last Command

    - by jp2code
    On our SQL Server (Version 10.0.1600), I have a stored procedure that I wrote. It is not throwing any errors, and it is returning the correct values after making the insert in the database. However, the last command spSendEventNotificationEmail (which sends out email notifications) is not being run. I can run the spSendEventNotificationEmail script manually using the same data, and the notifications show up, so I know it works. Is there something wrong with how I call it in my stored procedure? [dbo].[spUpdateRequest](@packetID int, @statusID int output, @empID int, @mtf nVarChar(50)) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @id int SET @id=-1 -- Insert statements for procedure here SELECT A.ID, PacketID, StatusID INTO #act FROM Action A JOIN Request R ON (R.ID=A.RequestID) WHERE (PacketID=@packetID) AND (StatusID=@statusID) IF ((SELECT COUNT(ID) FROM #act)=0) BEGIN -- this statusID has not been entered. Continue SELECT ID, MTF INTO #req FROM Request WHERE PacketID=@packetID WHILE (0 < (SELECT COUNT(ID) FROM #req)) BEGIN SELECT TOP 1 @id=ID FROM #req INSERT INTO Action (RequestID, StatusID, EmpID, DateStamp) VALUES (@id, @statusID, @empID, GETDATE()) IF ((@mtf IS NOT NULL) AND (0 < LEN(RTRIM(@mtf)))) BEGIN UPDATE Request SET MTF=@mtf WHERE ID=@id END DELETE #req WHERE ID=@id END DROP TABLE #req SELECT @id=@@IDENTITY, @statusID=StatusID FROM Action SELECT TOP 1 @statusID=ID FROM Status WHERE (@statusID<ID) AND (-1 < Sequence) EXEC spSendEventNotificationEmail @packetID, @statusID, 'http:\\cpweb:8100\NextStep.aspx' END ELSE BEGIN SET @statusID = -1 END DROP TABLE #act END Idea of how the data tables are connected:

    Read the article

  • SQL Server stored procedure + set error message from the records of a table

    - by lucky
    Hello, My question is I have a table with the set of records. I am calling a stored procedure for some other purpose. But when ever it finds some duplicate records. It need to return as error message back to php. C1 C2 c3 abc 32 21.03.2010 def 35 04.04.2010 pqr 45 30.03.2010 abc 12 04.05.2010 xyz 56 01.03.2010 ghi 21 06.05.2010 def 47 17.02.2010 klm 93 04.03.2010 xyz 11 01.03.2010 For the above set it need to check for the records that has the same c1. The stored procedure should return as abc,def,xyz are duplicate. I tried something like this. This will not work it has more than 1 set of duplicate records. Please help me to enhance this to solve the purpose. SET @duplicate = (SELECT c1 FROM temp GROUP BY c1 HAVING count(c1) > 1) --Check for duplicate concession Nr. IF(len(@duplicate) > '1') BEGIN SET @error = @error + ' Duplicate C1 Number:- ' + @duplicate SET @errorcount = @errorcount + 1 END As this one type error I am checking for errorcount. IF @errorcount <> '0' BEGIN GOTO E_General_Error END -- If an error occurs, rollback and exit E_General_Error: PRINT 'Error' SET @error = @error IF @@error <> 0 SET @error = 'Database update failed' ROLLBACK TRANSACTION update_database RETURN END Now it is able to return Duplicate c1 number abc. If there are more than 1 problem comes. Thanks in advance!

    Read the article

  • .NET Error with Dynamic T-SQL

    - by Phillip Theriault
    I am trying to create a TableAdapter in .NET using a stored procedure, but I'm getting an error "Incorrect syntax near the keyword 'WHERE'". I have narrowed down the problem in the stored procedure to the following lines: IF @NumRows > 0 SET @SQL += N' WHERE T1.RowNumber BETWEEN 1 and 10' ELSE SET @SQL += N' WHERE T1.RowNumber > 0' @NumRows is an input parameter that defaults to 0. However, if I remove either of those lines everything works fine (both WHERE clauses work fine by themselves when there is no IF/ELSE statement). It almost looks like .NET is ignoring the IF/ELSE and attempting to add the WHERE clause twice. It also works fine if I change it like so: IF @NumRows > 0 SET @SQL += N'' ELSE SET @SQL += N' WHERE T1.RowNumber > 0' One of the WHERE clauses has been removed, and there's no longer a conflict on the .NET side. So I'm pretty sure it has to do with the 2 WHERE clauses, and not with anything else. The sproc runs perfectly fine in SQL Server, and it even runs in .NET despite the error if I click "Preview Data". The only problem is that it won't auto-populate the list of fields, which I need for creating a report. Has anybody seen this before and have a suggestion?

    Read the article

  • SQL Server job (stored proc) trace

    - by Jit
    Hi Friends, I need your suggestion on tracing the issue. We are running data load jobs at early morning and loading the data from Excel file into SQL Server 2005 db. When job runs on production server, many times it takes 2 to 3 hours to complete the tasks. We could drill down to one job step which is taking 99% of the total time to finish. While running the job step (stored procs) on staging environment (with the same production database restored) takes 9 to 10 minutes, the same takes hours on production server when it run at early morning as part of job. The production server always stuck up at the very job step. I would like to run trace on the very job step (around 10 stored procs run for each user in while loop within the job step) and collect the info to figure out the issue. What are the ways available in SQL Server 2005 to achieve the same? I want to run the trace only for these SPs and not for certain period time period on production server, as trace give lots of information and it becomes very difficult for me (as not being DBA) to analyze that much of trace information and figure out the issue. So I want to collect info about specific SPs only. Let me know what you suggest. Appreciate your time and help. Thanks.

    Read the article

  • Get Multiple Values From Database ASP.NET/C#

    - by user1043177
    I am trying to get/return multiple values from an SQL-Server database using and display them on an ASP.NET page. I am using a stored procedure to perform the SELECT command on the Database side. I am able to return the first value that matches the variable @PERSON but only one row is returned each time. Any help would be much appreciated. Database handler class public MainSQL() { _productConn = new SqlConnection(); _productConnectionString += "data source=mssql.database.co.uk;InitialCatalog=test_data;User ID=username;Password=password"; _productConn.ConnectionString = _productConnectionString; } public string GetItemName(int PersonID) { string returnvalue = string.Empty; SqlCommand myCommand = new SqlCommand("GetItem", _productConn); myCommand.CommandType = CommandType.StoredProcedure; myCommand.Parameters.Add(new SqlParameter("@PERSON", SqlDbType.Int)); myCommand.Parameters[0].Value = PersonID; _productConn.Open(); returnvalue = (string)myCommand.ExecuteScalar(); _productConn.Close(); return (string)returnvalue; } Stored Procedure USE [test_data] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [ppir].[GetItem] ( @PERSON int ) AS /*SET NOCOUNT ON;*/ SELECT Description FROM [Items] WHERE PersonID = @PERSON RETURN return.aspx namespace test { public partial class Final_Page : System.Web.UI.Page { MainSQL GetInfo; protected void Page_Load(object sender, EventArgs e) { int PersonId = (int)Session["PersonID"]; GetInfo = new MainSQL(); string itemname = GetInfo.GetItemName(PersonId); ReturnItemName.Text = itemname; } // End Page_Load } // End Class } // End Namespace

    Read the article

  • Dynamic SQL and Funtions

    - by Unlimited071
    Hi all, is there any way of accomplishing something like the following: CREATE FUNCTION GetQtyFromID ( @oricod varchar(15), @ccocod varchar(15), @ocmnum int, @oinnum int, @acmnum int, @acttip char(2), @unisim varchar(15) ) AS BEGIN DECLARE @Result decimal(18,8) DECLARE @SQLString nvarchar(max); DECLARE @ParmDefinition nvarchar(max); --I need to execute a query stored in a cell which returns the calculated qty. --i.e of AcuQry: select @cant = sum(smt) from table where oricod = @oricod and ... SELECT @SQLString = AcuQry FROM OinActUni WHERE (OriCod = @oricod) AND (ActTipCod = @acttip) AND (UniSim = @unisim) AND (AcuEst > 0) SET @ParmDefinition = N' @oricod varchar(15), @ccocod varchar(15), @ocmnum int, @oinnum int, @acmnum int, @cant decimal(18,8) output'; EXECUTE sp_executesql @SQLString, @ParmDefinition, @oricod = @oricod, @ccocod = @ccocod, @ocmnum = @ocmnum, @oinnum = @oinnum, @acmnum = @acmnum, @cant = @result OUTPUT; RETURN @Result END The problem with this approach is that it is prohibited to execute sp_excutesql in a function... What I need is to do something like: select id, getQtyFromID(id) as qty from table The main idea is to execute a query stored in a table cell, this is because the qty of something depends on it's unit. the unit can be days or it can be metric tons, so there is no relation between the units, therefore the need of a specific query for each unit.

    Read the article

  • # how to split the column values using stored procedure

    - by user1444281
    I have two tables table 1 is SELECT * FROM dbo.TBL_WD_WEB_DECK WD_ID WD_TITLE ------------------ 1 2 and data in 2nd table is WS_ID WS_WEBPAGE_ID WS_SPONSORS_ID WS_STATUS ----------------------------------------------------- 1 1 1,2,3,4 Y I wrote the following stored procedure to insert the data into both the tables catching the identity of main table dbo.TBL_WD_WEB_DECK. WD_ID is related with WS_WEBPAGE_ID. I wrote the cursor in update action to split the WS_SPONSORS_ID column calling the split function in the cursor. But it is not working. The stored procedure is: ALTER procedure [dbo].[SP_Example_SPLIT] ( @action char(1), @wd_id int out, @wd_title varchar(50), @ws_webpage_id int, @ws_sponsors_id varchar(250), @ws_status char(1) ) as begin set nocount on IF (@action = 'A') BEGIN INSERT INTO dbo.TBL_WD_WEB_DECK(WD_TITLE)VALUES(@WD_TITLE) DECLARE @X INT SET @X = @@IDENTITY INSERT INTO dbo.TBL_WD_SPONSORS(WS_WEBPAGE_ID,WS_SPONSORS_ID,WS_STATUS) VALUES (@ws_webpage_id,@ws_sponsors_id,@ws_status) END ELSE IF (@action = 'U') BEGIN UPDATE dbo.TBL_WD_WEB_DECK SET WD_TITLE = @wd_title WHERE WD_ID = @WD_ID UPDATE dbo.TBL_WD_SPONSORS SET WS_STATUS = 'N' where WS_SPONSORS_ID not in (@ws_sponsors_id) and ws_webpage_id = @wd_id BEGIN /* Declaring Cursor to split the value in ws_sponsors_id column */ Declare @var int Declare splt cursor for /* used the split function and calling the parameter in that split function */ select * from iter_simple_intlist_to_tbl(@WS_SPONSORS_ID) OPEN splt FETCH NEXT FROM splt INTO @var WHILE (@@FETCH_STATUS = 0) begin if not exists(select * from dbo.TBL_WD_SPONSORS where WS_WEBPAGE_ID = @wd_id and WS_SPONSORS_ID = @var) begin insert into dbo.TBL_WD_SPONSORS (WS_WEBPAGE_ID,WS_SPONSORS_ID) values(@wd_id,@var) end end CLOSE SPONSOR DEALLOCATE SPONSOR END END END The result I want is if I insert the data in WD_ID and IN WS_SPONSORS_ID column the data in the WS_SPONSORS_ID column should split and I need to compare it with WD_ID. The result I need is: WD_ID WD_TITLE --------------------- 1 TEST 2 TEST1 3 WS_ID WS_WEBPAGE_ID WS_SPONSORS_ID WS_STATUS -------------------------------------------------------- 1 1 1 Y 2 1 2 N 3 1 3 Y If I pass the string in WS_SPONSORS_ID as 1,2,3 it has to split like the above. Can you help?

    Read the article

  • [SQLServer JDBC Driver][SQLServer]Could not find stored procedure 'master..xp_jdbc_open2'.

    - by Vijaya Moderator -Oracle
    When connecting to MS SQL Server Database via Weblogic Datasource and using XA jdbc driver, the following error is thrown. <Jun 3, 2014 5:16:49 AM PDT> <Error> <Console> <BEA-240003> <Console encountered the following error java.sql.SQLException: [FMWGEN][SQLServer JDBC Driver][SQLServer]Could not find stored procedure 'master..xp_jdbc_open2'. at weblogic.jdbc.sqlserverbase.ddb_.b(Unknown Source)at weblogic.jdbc.sqlserverbase.ddb_.a(Unknown Source)at weblogic.jdbc.sqlserverbase.ddb9.b(Unknown Source)at weblogic.jdbc.sqlserverbase.ddb9.a(Unknown Source)at weblogic.jdbc.sqlserver.tds.ddr.v(Unknown Source)at weblogic.jdbc.sqlserver.tds.ddr.a(Unknown Source)at weblogic.jdbc.sqlserver.tds.ddq.a(Unknown Source)at weblogic.jdbc.sqlserver.tds.ddr.a(Unknown Source)at weblogic.jdbc.sqlserver.ddj.m(Unknown Source)at weblogic.jdbc.sqlserverbase.ddel.e(Unknown Source)at weblogic.jdbc.sqlserverbase.ddel.a(Unknown Source)  The cause behind the issue is that  the MS SQL Server was not installed with the Stored procedures to enable JTA/XA Solution To connect to SQL Server via XA Driver from WLS Datasource you need to install Stored Procedures for JTATo use JDBC distributed transactions through JTA, your system administrator should use the following procedure to install Microsoft SQL Server JDBC XA procedures. This procedure must be repeated for each MS SQL Server installation that will be involved in a distributed transaction.To install stored procedures for JTA:1. Copy the appropriate sqljdbc.dll and instjdbc.sql files from the WL_HOME\server\lib directory to the SQL_Server_Root/bin directory of the MS SQL Server database server, where WL_HOME is the directory in which WebLogic server is installed, typically c:\Oracle\Middleware\wlserver_10.x.  Note:  If you are installing stored procedures on a database server with multiple Microsoft SQL Server instances, each running SQL Server instance must be able to locate the sqljdbc.dll file.Therefore the sqljdbc.dll file needs to be anywhere on the global PATH or on the application-specific path. For the application-specific path, place the sqljdbc.dll file into the :\Program Files\Microsoft SQL Server\MSSQL$\Binn directory for each instance. 2. From the database server, use the ISQL utility to run the instjdbc.sql script. As a precaution, have your system administrator back up the master database before running instjdbc.sql. At a command prompt, use the following syntax to run instjdbc.sql:  ISQL -Usa -Psa_password -Sserver_name -ilocation\instjdbc.sql  where:  sa_password is the password of the system administrator.  server_name is the name of the server on which SQL Server resides.  location is the full path to instjdbc.sql. (You copied this script to the SQL_Server_Root/bin directory in step 1.)  The instjdbc.sql script generates many messages. In general, these messages can be ignored; however, the system administrator should scan the output for any messages that may indicate an execution error. The last message should indicate that instjdbc.sql ran successfully. The script fails when there is insufficient space available in the master database to store the JDBC XA procedures or to log changes to existing procedures.

    Read the article

  • Use a stored procedure parameter for unit parameter of DATE_SUB

    - by Mike
    I would like to know if it's possible to pass a parameter to a mysql stored procedure and use this parameter as the unit parameter of the DATE_SUB function. It seems that the unit parameter is a reserved word so I don't know if there's a type for unit. Exemple of what I'm trying to do : DELIMITER $$ DROP PROCEDURE IF EXISTS `test` $$ CREATE DEFINER=`root`@`%` PROCEDURE `test`(param1 unit) BEGIN select DATE_SUB(now(), INTERVAL 1 param1); END $$ DELIMITER ;

    Read the article

  • Where are Eclipse Bookmarks stored?

    - by Tom Tresansky
    Does anyone know where bookmarks are stored in the Eclipse IDE? I had to delete a Java project from my workspace, then use the Import Existing project option to reset some configuration settings, and now my bookmarks are gone. I'm trying to understand how this affected my bookmarks, since everything on the file system except for the Eclipse .project and .classpath files should have remained unchanged.

    Read the article

  • 'The default schema does not exist' on deploy of SQL CLR assembly onto SQL Server 2008

    - by abatishchev
    I'm deploying an example SQL CLR stored procedure which has a SQL CLR type as parameter using Visual Studio 2008 and menu Project -> Deploy. public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void TakeTariff(TariffInfo tariffInfo) { } } public class TariffInfo { public SqlDecimal Amount { get; private set; } } but getting next strange error: The default schema does not exist. How can I fix that? My user was created this way: CREATE USER myUser FOR LOGIN myLogin_mod WITH DEFAULT_SCHEMA = mySchema

    Read the article

  • java.sql.SQLException: Parameter number X is not an OUT parameter

    - by Frederik
    Hi guys, I'm struggling with getting the result OUT variable from a MySQL stored procedure. I get the following error: java.sql.SQLException: Parameter number 3 is not an OUT parameter The stored procedure looks like this: CREATE DEFINER=`cv_admin`@`localhost` PROCEDURE `CheckGameEligibility`( IN gID INT(10), IN uID INT(10), OUT result TINYINT(1) ) BEGIN # Do lots of stuff, then eventually: SET result = 1; END My java function takes an array of strings* and creates the CallableStatement object dynamically: public static int callAndReturnResult( String sql , String[] values ) { int out = 0 ; try { // construct the SQL. Creates: CheckGameEligibility(?, ?, ?) sql += "(" ; for( int i = 0 ; i < values.length ; i++ ) { sql += "?, " ; } sql += "?)" ; System.out.println( "callAndReturnResult("+sql+"): constructed SQL: " + sql ); // Then the statement CallableStatement cstmt = DB.prepareCall( sql ); for( int i = 0 ; i < values.length ; i++ ) { System.out.println( " " + (i+1) + ": " + values[ i ] ) ; cstmt.setString(i+1, values[ i ] ); } System.out.println( " " + (values.length+1) + ": ? (OUT)" ) ; cstmt.registerOutParameter( values.length + 1 , Types.TINYINT ); cstmt.execute(); out = cstmt.getInt( values.length ); cstmt.close(); } catch( Exception e ) { System.out.println( "*** db trouble: callAndReturnResult(" + sql + " failed: " + e ); e.printStackTrace() ; } return out ; } *) I suppose I should be using an int array instead of a string array, but it doesn't seem to be what the error message was about. Anyway, here's the output it generates: callAndReturnResult(CheckGameEligibility(?, ?, ?)): constructed SQL: CheckGameEligibility(?, ?, ?) 1: 57 2: 29 3: ? (OUT) *** db trouble: callAndReturnResult(CheckGameEligibility(?, ?, ?) failed: java.sql.SQLException: Parameter number 3 is not an OUT parameter at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929) at com.mysql.jdbc.CallableStatement.checkIsOutputParam(CallableStatement.java:692) at com.mysql.jdbc.CallableStatement.registerOutParameter(CallableStatement.java:1847) at org.apache.commons.dbcp.DelegatingCallableStatement.registerOutParameter(DelegatingCallabl>eStatement.java:92) at Tools.callAndReturnResult(Tools.java:156) Any ideas what might be the problem? :) Thanks in advance!

    Read the article

  • stored procedure vs UDF

    - by TheObserver
    I have a select statement and in a couple of the fields, I want to check if an entry for the record exists in another table and if it does, output 1 value and if it doesn't, provide another value. What would be the best way to do it? When would you use a stored procedure and when would you use a UDF?

    Read the article

  • Add information through a stored procedure and PHP?

    - by theband
    I have a form which contains a lot of elements, my DB guy has generated an SP and now i need to call that stored procedure. My form has twenty elements, when i click on submit the data must be saved in database. I know how to do this on insert query, but how to call an SP and perform this operation.

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 24 25 26 27 28 29 30 31 32 33 34 35  | Next Page >