f



bulk inserts from stored procedure

What is the best way to do bulk inserts from a SQL stored procedure or
one created by using C with embedded sql and host variables?

I need to call it from VB via ODBC

Does DB2 SQL handle arrays yet?

If anyone could point me to some examples that would be great!

Thanks in advance!

0
spoliskey (49)
11/30/2005 10:03:16 PM
comp.sys.ibm.as400.misc 9197 articles. 2 followers. Post Follow

0 Replies
336 Views

Similar Articles

[PageSpeed] 9

Reply:

Similar Artilces:

Stored procedure from stored procedure
Is it possible to create a stored procedure from a stored procedure? When I attempt this inanity, it doesn't blow up until syntax error at the first "end procedure" statement of the SP I'm trying to create within the outer SP. Can this be done? Using IDS 7.31-UC4 on Solaris 2.7/Intel (don't ask). On 24 Sep 2003 11:47:09 -0700, red_valsen@yahoo.com (Red Valsen) wrote: >Is it possible to create a stored procedure from a stored procedure? >When I attempt this inanity, it doesn't blow up until syntax error at >the first "end procedure" statement of the SP I'm trying to create >within the outer SP. Can this be done? > >Using IDS 7.31-UC4 on Solaris 2.7/Intel (don't ask). Write it out to flatfile and execute the create from command line using dbaccess??? John Carlson wrote: > On 24 Sep 2003 11:47:09 -0700, red_valsen@yahoo.com (Red Valsen) > wrote: > > >>Is it possible to create a stored procedure from a stored procedure? >>When I attempt this inanity, it doesn't blow up until syntax error at >>the first "end procedure" statement of the SP I'm trying to create >>within the outer SP. Can this be done? >> >>Using IDS 7.31-UC4 on Solaris 2.7/Intel (don't ask). > > > Write it out to flatfile and execute the create from command line > using dbaccess??? As John said, somewhat obliquely, there is...

calling a stored procedure from a stored procedure
Hi Folks, I'm trying to define a (I thought) trivial stored procedure (SP) as a front-end to the standard SP "dbms_system.set_ev", which I want to use to generate a 10046 tracing event. I'm trying to execute the following PL/SQL: create or replace procedure rob_enable_tracing ( sid in integer, serial in integer ) is begin dbms_system.set_ev(sid, serial, 10046, 4, ''); end; and getting the error: PLS-00201: identifier 'DBMS_SYSTEM.SET_EV' must be declared If I provide the schema in which "set_ev" is defined, in this case "SYS", as in: create or replace procedure rob_enable_tracing ( sid in integer, serial in integer ) is begin sys.dbms_system.set_ev(sid, serial, 10046, 4, ''); end; I get: PLS-00201: identifier 'SYS.DBMS_SYSTEM' must be declared Any suggestions? thanks, RU On May 10, 1:25 pm, RU <r...@vakuum.de> wrote: > Hi Folks, > > I'm trying to define a (I thought) trivial stored procedure (SP) as > a front-end to the standard SP "dbms_system.set_ev", > which I want to use to generate a 10046 tracing event. I'm trying > to execute the following PL/SQL: > > create or replace procedure rob_enable_tracing ( > sid in integer, > serial in integer > ) > is > begin > dbms_system.set_ev(sid, serial, 10046,...

Getting Data from a storeed procedure in a stored procedure
What I am looking to do is use a complicated stored procedure to get data for me while in another stored procedure. Its like a view, but a view you can't pass parameters to. In essence I would like a sproc that would be like this Create Procedure NewSproc AS Select * from MAIN_SPROC 'a','b',..... WHERE ......... Or Delcare Table @TEMP @Temp = MAIN_SPROC 'a','b',..... Any ideas how I could return rows of data from a sproc into another sproc and then run a WHERE clause on that data? Thanks Chris Auer Instead of using the complicated stored procedure, why don't you use a user defined function? Hi You may be better with a table function, but for a stored procedure you can create a (temporary) table and use the INSERT...EXEC statement (see Books online) e.g. CREATE TABLE #temporary_table (col1 char(1), col2 char(1) ) INSERT INTO #temporary_table (col1, col2 ) EXEC my_proc SELECT col1, col2 FROM #temporary_table John "Chris Auer" <chris.auer@gmail.com> wrote in message news:1117740278.709876.4550@o13g2000cwo.googlegroups.com... > What I am looking to do is use a complicated stored procedure to get > data for me while in another stored procedure. > > Its like a view, but a view you can't pass parameters to. > > In essence I would like a sproc that would be like this > > Create Procedure NewSproc > AS > > Select * from MAIN_SPROC 'a','b',..... > ...

Procedure in Stored procedure??
Hi all, Question from a guy who knows PL/SQL, but is starting with Transac-SQL on SQL Server.... Is it possible to have a sub procedure in a Stored Procedure, like we do in a PL/sql package ? Or better yet, have a kind of general Stored Procedure that would contain procedures called by more than one Stored Proc?? I can't find anything like this in the online books.... Thanks for your inputs.... Philip Hi SQL Server does not have packages and limited scope for shared variables. http://vyaskn.tripod.com/oracle_sql_server_differences_equivalents.htm http://tinyurl.com/n7v6 http://www.algonet.se/~sommar/share_data.html http://tinyurl.com/n7vc http://www.algonet.se/~sommar/dynamic_sql.html In general there should be no need to create a stored procedure within another stored procedure, therefore I think you should think about your requirements and design, rather than to do a parallel implementation that mimicks your Oracle code. John "Philip" <thethrill99@yahoo.com> wrote in message news:a2d3b5cf.0309121907.6c4070c4@posting.google.com... > Hi all, > Question from a guy who knows PL/SQL, but is starting with > Transac-SQL on SQL Server.... > > Is it possible to have a sub procedure in a Stored Procedure, like > we do in a PL/sql package ? Or better yet, have a kind of general > Stored Procedure that would contain procedures called by more than one > Stored Proc?? > > I can't find anything like this ...

DB2 COBOL stored procedure to UDB SQL stored procedure
I have been given the task of taking a 3,200 line COBOL stored procedure and duplicating the same functionality in UDB 7.2 on the Windows platform with a procedural SQL stored procedure. I have fiddled with procedural SQL stored procs on UDB, but mostly short,trivial ones. Looking for good advice, links, etc. on the best approach to this. There doesn't seem to be the concept of subroutines within a stored proc, so I am guessing one main stored proc that may be calling multiple smaller stored procs? Just found out about this need late this afternoon and wondering what the best approach is to doing such a task. Any and all ideas most welcome. thanks, fv <PJackson@txfb-ins.com> wrote in message news:1175743257.812351.234300@y80g2000hsf.googlegroups.com... >I have been given the task of taking a 3,200 line COBOL stored > procedure and duplicating the same functionality in UDB 7.2 on the > Windows platform with a procedural SQL stored procedure. I have > fiddled with procedural SQL stored procs on UDB, but mostly > short,trivial ones. Looking for good advice, links, etc. on the best > approach to this. There doesn't seem to be the concept of subroutines > within a stored proc, so I am guessing one main stored proc that may > be calling multiple smaller stored procs? Just found out about this > need late this afternoon and wondering what the best approach is to > doing such a task. > > Any...

Insert Query with stored procedure
Hi, is it possible to create an "INSERT INTO ..... "Select from stored procedure" Query? I want to create an temporary table. In this table I want to enter the data, which I can get from an stored procedure. But in the FROM-clause a stored procedure is not allowed? "Harald" <yixxu@yahoo.de> wrote in message news:bfhh70$jt5$1@online.de... > Hi, > > is it possible to create an "INSERT INTO ..... "Select from stored > procedure" Query? > > I want to create an temporary table. In this table I want to enter the data, > which I can get from an stored procedure. > But in the FROM-clause a stored procedure is not allowed? Use INSERT INTO/EXEC, e.g., INSERT INTO #TempTable (col1, col2, col3) EXEC MyStoredProcedure 1, 2 Regards, jag ...

Using stored procedure result set in another stored procedure
I've been developing a stored procedure that uses a user defined function in the query portion of the procedure. However, since the end product needs to allow for dynamic table names, the UDF will not work. I've been trying to get this to work with converting the UDF to a procedure, but I'm having no luck. Here is the background on what I'm trying to accomplish. I need to perform a sub-identity on a table, I have the normal identity set, but there are multiple duplicates in the table and I need each set of duplicates numbered also (1,2,3,4 for duplicate set 1, 1,2,3 for dup 2). Here is what I have using the UDF (the UDF returns a variable table with indetity and ID for each record) ********** UDF ************* CREATE FUNCTION dbo.setDuplicateTransactions(@accountNumber as varchar(50)) RETURNS @dupTransactions TABLE ( ID int IDENTITY, transactionID int ) AS BEGIN INSERT @dupTransactions SELECT t1.transactionID FROM providerTransactions t1 WHERE t1.accountNumber = @accountNumber ORDER BY t1.transactionID RETURN END ******** Stored Procedure ************ CREATE PROCEDURE dbo.sp_parseTransactions AS DECLARE @accountNumber varchar(50) DECLARE temp_cursor CURSOR FORWARD_ONLY FOR SELECT t1.accountNumber FROM providerTransactions t1 GROUP BY t1.accountNumber HAVING MAX(isNull(t1.duplicateCount,0)) != COUNT(t1.transactionID) ORDER BY t1.accountNumber OPEN temp_cursor FETCH NEXT FROM temp_cursor INTO @acco...

Oracle Stored Procedures VERSUS SQL Server Stored Procedures
I want to know the differences between SQL Server 2000 stored procedures and oracle stored procedures? Do they have different syntax? The concept should be the same that the stored procedures execute in the database server with better performance? Please advise good references for Oracle stored procedures also. thanks!! <jrefactors@hotmail.com> wrote in message news:1120793932.351921.5760@g43g2000cwa.googlegroups.com... > I want to know the differences between SQL Server 2000 stored > procedures and oracle stored procedures? Do they have different > syntax? The concept should be the same that the stored procedures > execute in the database server with better performance? > > Please advise good references for Oracle stored procedures also. > > thanks!! > SQL Server Stored procedures use transact sql and run on SQL Server Oracle Stored Procedures use pl/sql (modeled on ADA) and run on Oracle. Oracle!=SQL Server They use two very different concurrent models and the syntax is totally different. (eg you don't need to create temp tables in Oracle like in SQL Server) otn.oracle.com and look under documentation. Jim On Thu, 07 Jul 2005 20:38:52 -0700, jrefactors wrote: > I want to know the differences between SQL Server 2000 stored > procedures and oracle stored procedures? Do they have different > syntax? No, you just have to obtain Microsoft version of PL/SQL. -- http://www.mgogala.com On Thu, 07 Jul 2005 20:38:52 -0700...

Stored Procedure to Insert OR Update
Hello I have a simple stored procedure that takes couple of values and inserts a new record using them into a table. However, if the primary key, is duplicated it throws an error and doesn't insert anything. Good so far. However is it possible to change that SP so that if the record being inserted has a primary key that already exists in table, it treats is as an UPDATE and now searches for the primary key and updates the record with the new entries. Also is it possible to just UPDATE columns in the record that are different from the ones already in the table? Thanks for any help or assistance. >> Thanks for any help or assistance. << Look up the MERGE statement that was added to Standard SQL, DB2, Oracle and probably some other products by now. There should be a few article about the logic that it hides for an "UPSERT" (that was an old proprietary name for it). T-SQL will require you to write a lot of code for this. Here is one way to do this (insert/update). On SQL Server 2008 using MERGE will simplify things. I am not sure why you want to update columns only if the value has changed. Adding all conditions to check if each column value is different than the parameter value will not be efficient and will add unnecessary complexity. CREATE TABLE Foo ( keycol INT PRIMARY KEY, datacol CHAR(1)) -- Sample data INSERT INTO Foo VALUES (1, 'a') INSERT INTO Foo VALUES (2, 'b') INSERT INTO Foo VALUES (4, 'd') -- New...

Stored procedure Bulk replace
Hi all! I have the need to replace all occurrences of a string in all stored procedures of one user. is it possible to do it ? in practice we have an Oracle based application which uses Oracle cartidge to produce html Since Oracle cartridge will be dismissed we have to change all the cartridge Api with some other instructions. In this example : CREATE OR REPLACE PACKAGE BODY dummy AS ..........) IS BEGIN htp.p('<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional// EN">'); htp.p('<html><head><title>WIND R.D.C.P. MENU</title></head>'); htp.p('<frameset rows="70,*" framespacing="0" frameborder="0" style="border-color : Silver I need to replace "ht.p" instructions with something else (f.e. displayHtml() ) How to do it ? Thanks Francesco On May 3, 10:36 am, Laura <f.marchi...@pride.it> wrote: > Hi all! > I have the need to replace all occurrences of a string in all stored > procedures of one user. > is it possible to do it ? > > in practice we have an Oracle based application which uses Oracle > cartidge to produce html > > Since Oracle cartridge will be dismissed we have to change all the > cartridge Api with some other instructions. > > In this example : > > CREATE OR REPLACE PACKAGE BODY dummy AS > .........) IS > BEGIN > > htp.p('<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transit...

How to return a Pk value from one stored procedure to another stored procedure
Dear All, I have one stored procedure like sp_insertEmployee Employee Table Fileds Eno int pk, ename varchar(100), designation varchar In stored Procedure After inserting the ename and designation it has to return the Eno pk Id automatically I have another Department deptno int pk, Eno int fk, Depname varchar In this stored procedure I need to execute the sp_insertEmployee Stored procedure and we need that Pk return value after executing that By using that Id in this Department table we will insert the eno can u help me out on this issue Thanks CREATE PROCEDURE sp_insertEmployee ( @Ename VARCHAR(100), @Designation VARCHAR(100), --Length? @Eno INT OUTPUT ) AS BEGIN --Procedure INSERT Employee ( Ename VARCHAR(100), Designation VARCHAR(100) --?? ) VALUES ( @Ename, @Designation ) SET @Eno = SCOPE_IDENTITY() END --Procedure ------------------------------ When you execute this procedure from the procedure that inserts the record in the cross-reference table between department and employee, call it like so. EXEC sp_insertEmployee @Ename, @Designation, @Eno OUTPUT Alternately, you can also have the sproc RETURN the @Eno, instead of having it as an OUTPUT parameter. CREATE PROCEDURE sp_insertEmployee ( @Ename VARCHAR(100), @Designation VARCHAR(100) --Length? ) AS BEGIN --Procedure INSERT Employee ( Ename VARCHAR(100), Designation VARCHAR(100) --?...

Execute Stored procedure scripts in bulk
Hi, I have a folder containing about 150 stored procedures. Is there a way - through batch or command file to execute all of them at once..execute all files ending in .sql extension (inside a folder). Its not possible to list each SP name in a command file or execute each SP in management studio. please suggest. thanks > I have a folder containing about 150 stored procedures. Is there a way > - through batch or command file to execute all of them at > once..execute all files ending in .sql extension (inside a folder). A lowly Windows FOR command can help you here. The FOR command example below will run all "sql" in the current folder and redirect stdout and stderr output to a log file for later review: FOR %f IN (*.sql) DO SQLCMD -E -S MyServer -d MyDatabase -I -i "%f" 2>&1 >>"sqlcmd.log" You can also encapsulate this in command file for reuse. The difference is that you need to precede the variables with a two percent signs instead of one and pass the target server and database as command-line arguments: FOR %%f IN (*.sql) DO SQLCMD -E -S %1 -d %2 -I -i "%%f" 2>&1 >>"sqlcmd.log" Save the command as RunScripts.cmd in the same folder as your scripts and execute with: RunScripts MyServer MyDatabase See Windows help for more information on FOR and other useful commands. -- Hope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ "tirath" &...

Stored Procedure
Hello everyone, Against my better judgement I have been thrust into the duty of creating Crystal reports for my company. I have discovered rather quickly that I also need to become familar with SQL (creating views and stored procedures). Quick question. I have a stored procedure were I created a workfile. Several thousand records get inserted in this work file. Is it normally quicker to join all the tables I need for the insert or is it faster to get all the base records inserted into the workfile then do one or more updates to the workfile using the other tables that I didn't join. Any help appreciated Thanks Ray eighthman11 (rdshultz@nooter.com) writes: > Quick question. > I have a stored procedure were I created a workfile. Several thousand > records get inserted in this work file. Is it normally quicker to > join all the tables I need for the insert or is it faster to get all > the base records inserted into the workfile then do one or more > updates to the workfile using the other tables that I didn't join. If you join all tables at once, the optimizer has the chance to compute the best compute order. If you work with a temp table, and take table one by one, you determine the compute order, once and for all. The optimizer may pick one plan today, and another in six months when the data profile has changed. So joining all at once is usually better. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Links for SQL Server ...

INSERT or UPDATE
Hello, I've got sql statement: update lib/t1 set PRLT = (select detmin from lib/t2 where kod = prdc) where prdc in (select kod from lib/t2) and UNIT = 'SZT' -- and second one insert into lib/t1 ( select kod, 'PLN' , 'SZT' , detmin from ( select * from lib/t2 left outer join lib/t1 on prdc = kod ) z where prdc is NULL ) as You can imagine my point is insert not existing records (into t1) from t2 and update existing. SQL solution is ok, but looking for more elegan way I've found RPG useful (basically CHAIN opcode and WRITE if NOT %FOUND). Now I'm wondering if that is doable via stored procedure and i.e. dealing with 100 SQLCODE (record not found for update) or something. Any suggestions ? Regards. Grzegorz Goryszewski Grzegorz Goryszewski wrote: > Hello, > > I've got sql statement: > > update lib/t1 > set PRLT = (select detmin from lib/t2 > where kod = prdc) > where > prdc in (select kod from lib/t2) > and UNIT = 'SZT' > > -- and second one > > insert into lib/t1 ( > select kod, 'PLN' , 'SZT' , detmin from ( > select * from lib/t2 > left outer join lib/t1 > on prdc = kod ) z > where prdc is NULL ) > > as You can imagine my point is insert not existing records (into t1) from t2 > and update existing. > SQL solution is ok, but looking for more elegan way I've found > RPG useful (basically CHA...

insertion using stored procedure in oracle.
hi there, I need to write to a stored procedure in oracle 9i to insert values in a table in which: 1) Values in one column are to be retrived from a column in another table. 2)Getting three parameters as inputs in the procedure,and inserting those values in the same row which has the value that has been taken from the different table. am very new to sp and am really confused when do we use wat..like cursors,index,functions etc. so plzz provide some sample code to handle this insertion. This sounds like a homework assignment and homework should be done by the person it is assinged to. You can find examples of everything you need in the PL/SQL manaul. The manuals are available via http://www.oracle.com/technology/documentation/index.html >From your description I am not sure if you need to insert all the rows from one table into another or only the row that match the input parameters but you need to look up the following: create or replace procedure cursor Loop IF Some examples: procedure compare_struct ( p_db1 in varchar2 ,p_db2 in varchar2 ) is -- cursor c_drv is select distinct owner ,table_name from compare_struct cs where cs.database = v_db1; -- r_drv c_drv%rowtype; HTH -- Mark D Powell -- ad wrote: > hi there, > I need to write to a stored procedure in oracle 9i to insert values in > a table in which: > 1) Values in one column are to be retrived from a column in another > table. > 2)Getting three parameters...

how to call a stored procedure in an insert command
hi, i had a small doubt , i have a table xxx with two columns (a int,b int ) and i have inserted 5 rows my query is to add the two colums using a stored procedure and the result has to be displayed in an separate column --this has to be done only stored procedures ---i know how to solve the problem using computed columns concept like this create table yyy(a int ,b int ,total as a+b) insert into yyy values (12,13) select * from yyy i need the answer using stored procedures---is it possible pls help me satish create proc get_your_calc as begin select a, b, total = a + b from yyy end -- Tony Rogerson SQL Server MVP http://sqlserverfaq.com - free video tutorials "satish" <satishkumar.gourabathina@gmail.com> wrote in message news:1140593950.858980.60120@g44g2000cwa.googlegroups.com... > hi, > i had a small doubt , i have a table xxx with two columns (a int,b int > ) and i have inserted 5 rows my query is to add the two colums using a > stored procedure and the result has to be displayed in an separate > column --this has to be done only stored procedures ---i know how to > solve the problem using computed columns concept > > like this > > create table yyy(a int ,b int ,total as a+b) > > insert into yyy values (12,13) > select * from yyy > > i need the answer using stored procedures---is it possible > > pls help me > satish > ...

Stored procedure in database X, executes stored procedure in database Y, wrapped in transaction?
Is it possible to execute a stored procedure in one database, which then itself executes a stored procedure from another database? We have decide to split our data into a tree structure (DB1) and data blobs (DB2) (we are using MSDE and we have a 2gb limit with each DB so we've done it this way for that reason). I would like to, say, execute a stored procedure in DB1, passing in the data blob and other details, DB1 will create a tree node in DB1 and then add the blob record to DB2. DB1 will wrap in a transaction of course, as will DB2 when it adds the blob. Is this possible? [posted and mailed, please reply in news] Robin Tucker (r.tucker@thermoteknix.com) writes: > Is it possible to execute a stored procedure in one database, which then > itself executes a stored procedure from another database? We have > decide to split our data into a tree structure (DB1) and data blobs > (DB2) (we are using MSDE and we have a 2gb limit with each DB so we've > done it this way for that reason). I would like to, say, execute a > stored procedure in DB1, passing in the data blob and other details, DB1 > will create a tree node in DB1 and then add the blob record to DB2. DB1 > will wrap in a transaction of course, as will DB2 when it adds the blob. > Is this possible? Yes. You just call the second procedure using three-part notation: EXEC DB2..that_other_sp However, if you are smart, you don't hard-code the DB name, but you read it ...

inconsistent order by using insert into in a stored procedure
hi there, i am using sql server 7. below is the stored procedure that is giving me grief. its purpose it two-fold, depending on how it is called: either to return a pageset (based on page number and page size), or to return IDs of previous and next records (based on current record id). the problem is, that the order in which records are inserted into the temp table is inconsistent, even though the calling statement and the order by is always the same: sometimes records are ordered correctly, by project_number, and sometimes the order is broken starting at some record (which is always the same). i have no idea what is wrong here, i would appreciate any help! thanks so much. here is the calling statement: EXECUTE spProjects 2,null,'project_number','asc','',6,50 here is the proc: CREATE PROCEDURE spProjects @action int, @currID int, @sortBy varchar(50), @sortDir varchar(4), @searchBy varchar(255), @Page int, @RecsPerPage int AS SET NOCOUNT ON DECLARE @nextID int DECLARE @prevID int DECLARE @currRow int DECLARE @rowCount int DECLARE @firstRec int DECLARE @lastRec int DECLARE @total int DECLARE @more int DECLARE @sortBy2 varchar(50) -- setup temp table SELECT r.id as row, r.*, a.name agr_type, pu.name purpose, sp.name sponsor, pr.name prime, p.lname p_lname, p.fname p_fname, p.mname p_mi, p.email p_email, o.name org, convert(varchar(10), r.created_date, 101) adddate_c, convert(varc...

Stored Procedures: INSERT VALUES string as a parameter
How can I get this stored procedure to work? I want to pass all the INSERT parameters as one string. /*I want this to insert 2 records*/ DROP PROCEDURE IF EXISTS spTest$$ CREATE PROCEDURE spTest() BEGIN DECLARE pVal TEXT; SET pVal = "('a','b','c','d','e','f'), ('g','h','i','j','k','l')"; INSERT INTO test (fld1,fld2,fld3,fld4,fld5,fld6) VALUES pVal; END$$ /*I've tried prepared statements as well without success*/ DROP PROCEDURE IF EXISTS glTest$$ CREATE PRO...

ADO: Can I insert a stored procedure in a transaction
Hi, Can I mix ADO-code (insert, update recordsets) and stored procedures in a transaction? Filip Sure - no problem. Just use an ADO Command object to execute the stored procedure, and make sure everything is using the same Connection instance that is handling the transaction. Of course, you need to watch if the stored procedures themselves are doing BEGIN TRANSACTION, etc., since this could confuse the issue. On Thu, 05 Feb 2004 20:39:31 GMT, "Filips Benoit" <benoit.filips@pandora.be> wrote: >Hi, > >Can I mix ADO-code (insert, update recordsets) and stored procedures in a >transaction? > >Filip > ...

Where are Views, stored procedures, stored?
Where are sored procedures, views etc, stored? Are they objets for the OS/400 as Logical files or Phisical files? how to save and restore them? Thanks in advance. On Aug 16, 5:21 am, "CENTRINO" <ning...@nigunelandia.com> wrote: > Where are sored procedures, views etc, stored? Are they objets for the > OS/400 as Logical files or Phisical files? how to save and restore them? > > Thanks in advance. Tables are Physical Files Views are Logical Files Stored procedures are Program Objects All are stored in the library of choice, and get backed up when the library is backed up. SQL VIEW and TABLE are objects of type *FILE; a VIEW is implemented as a LF [Logical File] extended attribute, and the TABLE is implemented as a PF [Physical File] extended attribute. The SQL INDEX in DB2 for i5/OS is also of type *FILE, implemented as a keyed LF. SQL Stored Procedures are objects of type *PGM SQL Functions are modules as part of object type *SRVPGM External Stored Procedures and External User Defined Functions are entries in an SQL Catalog TABLE [the SYSROUTINE TABLE stores procedure and function entries as rows, SYSPROCS VIEW and SYSFUNCS VIEW divide those rows] which may or may not have a self-describing *PGM or *SRVPGM associated. If the external/referenced object exists before the CREATE statement, and the reference object is capable of becoming self-described [a message is issued during CREATE if not], then future s...

Stored Procedure und insert on a variable tables
create procedure createKonto($myKonto integer, $einzahlung double) BEGIN DECLARE $t TEXT; IF ( NOT EXISTS ( select * from konto where kontoNr = $myKonto ) ) THEN SET $t = CONCAT('konto_' , CAST($myKonto AS CHAR)); INSERT INTO konto (kontoNr, kontoStand) VALUES( $myKonto,$einzahlung ); INSERT INTO $t (buhung, erfolg) VALUES($einzahlung, 1); END IF; ...

Inserting data into Access form from SQL Stored Procedure
I am attempting to create an Access database which uses forms to enter data. The issue I am having is returning the query results from the Stored Procedure back in to the Access Form. tCetecM1CUST (SQL Table that contains the Customer Information) tAccountingDetail (SQL Table that contains the information in the form) frmAccountingEntry (Access form used to enter data) spGetCustomerInformation (Stored Procedure which returns data using variable CUSTOMER_NUMBER entered in the Access.) Scenario is this. Open form, Enter 'Job Number' and 'Customer Number', form uses 'AfterUpdate' to run this... Private Sub CUSTOMER_NUMBER_AfterUpdate() Set gcn = Nothing Dim sConnect As String sConnect = "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST SECURITY INFO=FALSE;INITIAL CATALOG=x;DATA SOURCE=x" Set gcn = New ADODB.Connection gcn.CursorLocation = adUseClient gcn.Open sConnect 'On Error GoTo ExitProcedure Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset 'Call doConnect Dim cmd As ADODB.Command Set cmd = New ADODB.Command With cmd .CommandText = "spGetCustomerInformation" .CommandType = adCmdStoredProc .Parameters.Append .CreateParameter("@CUSTOMER_NUMBER", adVarChar, adParamInput, 6, Forms!frmAccountingEntry!CUSTOMER_NUMBER.Value) Set .ActiveConnection = gcn End With Set rs = New ADODB.Recordset rs.CursorLocation = adUseServer rs.Open cmd, , adOpenStatic, adLockRe...

Inserting a record using values from another Stored Procedure
Hello, I'm trying to accomplish 3 things with one stored procedure. I'm trying to search for a record in table X, use the outcome of that search to insert another record in table Y and then exec another stored procedure and use the outcome of that stored procedure to update the record in table Y. I have this stored procedure (stA) CREATE PROCEDURE procstA (@SSNum varchar(9) = NULL) AS SET NOCOUNT ON SELECT OType, Status, SSN, FName, LName FROM Customers WHERE (OType = 'D') AND (Status = 'Completed') AND (SSN = @SSNum) GO. Then, I need to create a new record in another table (Y) using the SSN, FName and Lname fields from this stored procedure. After doing so, I need to run the second stored procedure (stB) Here it is: CREATE PROCEDURE procstB( @SSNum varchar(9) = NULL) AS SET NOCOUNT ON -- select the record SELECT OrderID, OrderDate, SSN FROM Orders GROUP BY OrderID, OrderDate, SSN HAVING (ProductType = 'VVSS') AND (MIN(SSN) = @SSNum) GO. After running this, I need to update the record I created a moment ago in table Y with the OrderDate and OrderID from the second stored procedure. Do you guys think that it can be done within a single stored procedure? Like for example, at the end of store procedure A creating an insert statement for the new record, and then placing something like exec procstB 'SSN value'? to run stored procedure B and then having a update statement to update that new reco...

Web resources about - bulk inserts from stored procedure - comp.sys.ibm.as400.misc

Invasiveness of surgical procedures - Wikipedia, the free encyclopedia
There are three main categories which describe the invasiveness of surgical procedures . These are: non-invasive procedures , minimally invasive ...

Eye tattoos: Tattooist Luna Cobra defends practice following calls to ban procedure
IT SOUNDS like the really gross scene in a horror movie.

Survivors of female genital mutilation outraged by 'compromise' which could legalise procedure
Survivors of female genital mutilation are fighting back against a so-called compromise, which could let a modified form of the procedure continue. ...

Doctors Perform Medical Procedure On Wrong Newborn
The procedure was meant for another child.

Butt procedures, male breast reduction growing slices of plastic surgery
Every 30 min of 2015, a rump was remodeled. Men had 40% of all breast reductions.

"Gynecologists Kavita Shah Arora and Allan Jacobs said procedures that slightly changed the look of a ...
"... were comparable to male circumcision or cosmetic procedures in Western countries like labiaplasty. Laws against mild modifications were ...

Mix-up leads to surgical procedure on wrong baby
Tennessee mom says her newborn was mistakenly given treatment he didn't need

New Chipotle Food Safety Procedures Include Shutting Down Restaurant If Anyone Barfs
... what happened inside, but it’s pretty much exactly what you would expect: repetitive instructional videos about food safety and cleaning procedures, ...

New Video Series Shows The Reality Of Abortion Procedures
New Video Series Shows The Reality Of Abortion Procedures

Yay going in for kidney stone procedure soon!
There is a G-d i have been in the hospital with stone pain and bowel pain the past week. the bowel issue resolved by itself without surgery ...

Resources last updated: 3/24/2016 1:55:06 PM