Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 11 to 20 of 26 total |
Help Converting MS SQL Stored Procedure To ElevateDB |
Tue, Sep 6 2011 7:42 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Michael
See my response to John. Looking at what you've posted you're trying to insert a DATE variable into a DATE column and if that doesn't work you've screwed up somewhere else. Can you post your database and code into the binaries ng and I'll have a fiddle. Roy Lambert [Team Elevate] |
Tue, Sep 6 2011 9:42 AM | Permanent Link |
Michael Riley ZilchWorks | Roy
>> Err why are you casting things to varchar? << I thought EXECUTE IMMEDIATE executed dynamic sql. How do you write an EXECUTE IMMEDIATE statement that does the following: DECLARE MyDate DATE; SET MyDate = CURRENT_DATE + INTERVAL '1' MONTH; EXECUTE IMMEDIATE ' INSERT INTO MYTABLE ( MyTableDate) Values ( ''' + CAST(MyDate as varchar(20)) + ''' ) ' If this is not the proper way to perform an insert into a database table from inside a stored procedure then PLEASE show me the correct way to do this. I'm getting very frustrated. I feel like I'm sitting in the cockpit of a plane looking at the controls for THE FIRST TIME and all I hear is "Why did you push that button?" or "Why did you move that lever?" or "Have you RTFM?" I could not find one example of how to do this in any of the manuals or forum posts. If I did, I wouldn't be asking here. Roy, I'm already on the ground stop kicking me. SHOW ME how to do this or if you do not know how to do this then just say so. Michael Riley GySgt USMC Retired www.zilchworks.com |
Tue, Sep 6 2011 10:17 AM | Permanent Link |
Michael Riley ZilchWorks | Roy
>> See my response to John. Looking at what you've posted you're trying to insert a DATE variable into a DATE column and if that doesn't work you've screwed up somewhere else. Can you post your database and code into the binaries ng and I'll have a fiddle. << There are no binaries. I'm doing all of this inside the ElevateDB Manager. I'll figure it out after I get home from work. Michael Riley GySgt USMC Reried www.zilchworks.com |
Tue, Sep 6 2011 10:20 AM | Permanent Link |
Malcolm Taylor | Michael Riley wrote:
> I don't understand what you mean. If you could post a piece of sample > code that would be extrremely helpful. Michael, this has caught out a lot of users so don't feel too bad. From the SQL Help uinder Date and Time Types, see the Literals section. Specifically: SET "MyDateCol" = DATE '2011-09-06' Note the keyword DATE |
Tue, Sep 6 2011 10:29 AM | Permanent Link |
John Hay | Michael
> I don't understand what you mean. If you could post a piece of sample > code that would be extrremely helpful. The following should work (if embedded quotes are right EXECUTE IMMEDIATE ' INSERT INTO TempAmortization ( PmtNumber , -- PmtDate , PmtAmount , BalanceStart , PmtPrincipal , PmtInterest , BalanceEnd ) VALUES ( ' + CAST(PmtNumber as varchar(25)) + ' ,date ''' + CAST(PmtDate as varchar(25)) + ''' , ' + CAST(Payment as varchar(25)) + ' , ' + CAST(BalanceStart as varchar(25)) + ' , ' + CAST(PmtPrincipal as varchar(25)) + ' , ' + CAST(PmtInterest as varchar(25)) + ' , ' + CAST(BalanceEnd as varchar(25)) + ' ) '; John |
Tue, Sep 6 2011 10:58 AM | Permanent Link |
Raul Team Elevate | Michael,
I think Roy was asking why do you store them as varchars? Assuming that's what you need to do it's fine but you could store them as DATE columns and then use procedure to insert values as dates. For example if i had a imaginary table called MyTable that had 2 columns called NextPayment,ReviewDate and both were of DATE datatype then following would insert 2 date values - 1 for current date+1month and 1 literal date string for Dec 31,2011 : EXECUTE IMMEDIATE 'INSERT INTO MyTable (NextPayment,ReviewDate) VALUES (CURRENT_DATE + INTERVAL ''1'' MONTH, DATE ''2011-12-31'');'; Raul Michael Riley wrote: How do you write an EXECUTE IMMEDIATE statement that does the following: DECLARE MyDate DATE; SET MyDate = CURRENT_DATE + INTERVAL '1' MONTH; EXECUTE IMMEDIATE ' INSERT INTO MYTABLE ( MyTableDate) Values ( ''' + CAST(MyDate as varchar(20)) + ''' ) ' If this is not the proper way to perform an insert into a database table from inside a stored procedure then PLEASE show me the correct way to do this. I'm getting very frustrated. I feel like I'm sitting in the cockpit of a plane looking at the controls for THE FIRST TIME and all I hear is "Why did you push that button?" or "Why did you move that lever?" or "Have you RTFM?" I could not find one example of how to do this in any of the manuals or forum posts. If I did, I wouldn't be asking here. Roy, I'm already on the ground stop kicking me. SHOW ME how to do this or if you do not know how to do this then just say so. Michael Riley GySgt USMC Retired www.zilchworks.com |
Tue, Sep 6 2011 11:05 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Michael
>I feel like I'm sitting in the cockpit of a plane looking at the controls for THE FIRST TIME and all I hear is "Why did you push that button?" or "Why did you move that lever?" or "Have you RTFM?" My apologies, I missed the fact that you were using EXECUTE IMMEDIATE >I could not find one example of how to do this in any of the manuals or forum posts. If I did, I wouldn't be asking here. There are, but as with most manuals, OLH or forums you have to know what you're looking for first. There are a lot of times I've asked something on the CodeGear ngs to have someone give me a search string for Google that gave me what I wanted after I'd spent hours searching fruitlessly. >Roy, I'm already on the ground stop kicking me. SHOW ME how to do this or if you do not know how to do this then just say so. I'm not kicking, I just missed the key bit of information. First as John's example you'll need the DATE keyword in front of the date as string. Secondly, since you've already asked about best practice, I'd say best practice is not to use EXECUTE IMMEDIATE within a loop with 360 iterations. Something like SCRIPT BEGIN DECLARE Ins SENSITIVE CURSOR FOR Stmt; DECLARE Looper INTEGER; PREPARE Stmt FROM 'SELECT * FROM Fred'; OPEN Ins; SET Looper = 1; WHILE Looper < 10 DO INSERT INTO Ins VALUES(CAST(Looper AS VARCHAR(12)),'xx'); SET Looper = Looper + 1; END WHILE; END would probably be better. And just to prove there is stuff in the manual CREATE PROCEDURE UpdateState() BEGIN DECLARE CustCursor CURSOR WITH RETURN FOR Stmt; DECLARE State CHAR(2) DEFAULT ''; PREPARE Stmt FROM 'SELECT * FROM Customer'; OPEN CustCursor; FETCH FIRST FROM CustCursor ('State') INTO State; WHILE NOT EOF(CustCursor) DO IF (State = 'FL') THEN UPDATE CustCursor SET 'State' = 'NY'; END IF; FETCH NEXT FROM CustCursor ('State') INTO State; END WHILE; END OK its update rather than insert but its the one I remembered about. Roy Lambert [Team Elevate] |
Tue, Sep 6 2011 11:08 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Raul
The problem was I had a touch of blindness and missed the EXECUTE IMMEDIATE. Roy Lambert |
Tue, Sep 6 2011 12:20 PM | Permanent Link |
Michael Riley ZilchWorks | >>
When using a date literal you need to qualify it ie date'2011-09-15' instead of just '2011-09-15' << John, Thank you. I didn't know i needed the "Date" keyword. Michael Riley GySgt USMC Retired www.zilchworks.com |
Tue, Sep 6 2011 12:24 PM | Permanent Link |
Michael Riley ZilchWorks | "Malcolm" wrote:
>> Michael, this has caught out a lot of users so don't feel too bad. From the SQL Help uinder Date and Time Types, see the Literals section. Specifically: SET "MyDateCol" = DATE '2011-09-06' Note the keyword DATE << Thank you Malcom. I will check that out when I get home from work. Michael Riley GySgt USMC Retired www.zilchworks.com |
« Previous Page | Page 2 of 3 | Next Page » |
Jump to Page: 1 2 3 |
This web page was last updated on Friday, November 1, 2024 at 07:01 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |