Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 10 of 12 total |
Executing SQL in DB Manager 2.02 |
Thu, May 14 2009 10:15 AM | Permanent Link |
"Richard Speiss" | I'm really new to ElevateDB (not to SQL though. I have many years in MS
SQL) and I am just trying to populate a table in DB Manager with a few INSERT statements I opened a new statement window on my database and tried to execute multiple INSERT commands like so: INSERT INTO myMessages VALUES(1,2,'000','','Approved',1) INSERT INTO myMessages VALUES(2,2,'332','','Insufficient Points',1) When I execute it I get an error #700 An error was found in the statement at line 2 and column 1 (Expected end of expression but instead found INSERT). It looks like I need an end of statement delimiter. I saw in some examples, the use of a ; but this didn't make any difference What do I need to do in DBManager in order to execute multiple SQL commands? Many thanks Richard Speiss |
Thu, May 14 2009 10:33 AM | Permanent Link |
Fernando Dias Team Elevate | Richard,
To execute multiple SQL statements you must create a Script (New/Script). Your script should look like this: SCRIPT BEGIN EXECUTE IMMEDIATE 'INSERT INTO myMessages VALUES(1,2,''000','''',''Approved'',1)'; EXECUTE IMMEDIATE 'INSERT INTO myMessages VALUES(2,2,''332'','''',''Insufficient Points'',1)' ; END If you use New/Statement you can only execute one SQL statement at a time. -- Fernando Dias [Team Elevate] |
Thu, May 14 2009 11:11 AM | Permanent Link |
"Richard Speiss" | Thanks Fernando, that did the trick.
Is there a way in Elevate DB Manager to test if a table/view exists? e.g. I have a script that creates my needed tables/views. If the tables/views already exist I want to drop the existing table/view and create a new one. In MS SQL I would do something like: IF EXISTS(SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[myView]') AND OBJECTPROPERTY(id, N'IsView') = 1) DROP VIEW [myView] GO CREATE VIEW myView AS SELECT * FROM SOMTABLE GO Thanks Richard "Fernando Dias" <fernandodias.removthis@andthis.easygate.com.pt> wrote in message news:B996FECB-579F-48C6-9552-64373CC7B56D@news.elevatesoft.com... > Richard, > > > To execute multiple SQL statements you must create a Script (New/Script). > Your script should look like this: > > SCRIPT > BEGIN > > EXECUTE IMMEDIATE > 'INSERT INTO myMessages VALUES(1,2,''000','''',''Approved'',1)'; > > EXECUTE IMMEDIATE > 'INSERT INTO myMessages VALUES(2,2,''332'','''',''Insufficient > Points'',1)' ; > > END > > If you use New/Statement you can only execute one SQL statement at a time. > > -- > Fernando Dias > [Team Elevate] |
Thu, May 14 2009 11:43 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Richard
Not quite what you asked for but it should give you the idea. IF Mode <> 'Refresh' THEN PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?'; OPEN InfoCursor USING tblName; IF (ROWCOUNT(InfoCursor) > 0) THEN EXECUTE IMMEDIATE 'DROP TABLE "'+tblName+'"'; END IF; You can also just wrap the statement(s) in an EXCEPTION Finally you can use EXISTS - from the OLH Usage The EXISTS function returns the TRUE if a given sub-query returns any rows, or FALSE if the sub-query does not return any rows. EXISTS is useful in situations where you simply want to know if any rows are present for a given set of conditions, which would be expressed via the WHERE clause of the sub-query. Examples SELECT * FROM Customers WHERE EXISTS(SELECT * FROM Orders WHERE Orders.CustomerNo=Customers.CustomerNo) Roy Lambert [Team Elevate] |
Thu, May 14 2009 12:34 PM | Permanent Link |
"Richard Speiss" | I got it working for one table and decided that it should be a stored
procedure. I created this sp successfully CREATE PROCEDURE myDropTable (IN sTable VARCHAR(50)) BEGIN DECLARE TempCursor CURSOR FOR stmt; PREPARE stmt FROM 'SELECT * FROM Information.Tables WHERE NAME=?'; OPEN TempCursor USING sTable; IF (ROWCOUNT(TempCursor) > 0) THEN EXECUTE IMMEDIATE 'DROP TABLE "' + sTable + '"'; END IF; END But now I can't figure out how to call it in DB Manager. Just so you know, I want my end result to be a script that will drop existing tables and create them again. I have the code working that creates them but I wanted a general script that I could run at any time to recreate everything. e.g. EXECUTE IMMEDIATE 'myDropTable(''TABLEA'')'; EXECUTE IMMEDIATE 'myDropTable(''TABLEA'')'; .... EXECUTE IMMEDATE 'CREATE TABLE A (...)'; EXECUTE IMMEDATE 'CREATE TABLE B (...)'; So, how do I call my sp through DBManager? Thanks Richard |
Thu, May 14 2009 12:57 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Richard
I've posted a couple of full and working scripts below my sig. The first one is a generalised routine (thanks Tim) to create a memory table from a select statement and the second is the one that creates an in-memory table for use in my calling projects. To call something like these in EDBManager you need to: Create a new script in the right context. Look at the bottom of the screen there's a status bar showing where you are. To create a table you need to be in the context of the database you want to create it in. Delete the stuff that EDBManager creates automatically in the script Cut'n'Paste the text into the script. Press F9 Roy Lambert SCRIPT (IN SQLStatement VARCHAR, IN TableName VARCHAR, IN IdxSet VARCHAR) BEGIN DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt; DECLARE ResultCursor SENSITIVE CURSOR FOR ResultStmt; PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?'; OPEN InfoCursor USING TableName; IF (ROWCOUNT(InfoCursor) > 0) THEN EXECUTE IMMEDIATE 'DROP TABLE "'+TableName+'"'; END IF; CLOSE InfoCursor; PREPARE ResultStmt FROM 'CREATE TABLE "'+TableName+'" AS '+SQLStatement+' WITH DATA'; EXECUTE ResultStmt; IF IdxSet IS NOT NULL THEN PREPARE ResultStmt FROM 'CREATE INDEX Idx ON "'+TableName+'"('+IdxSet+')'; EXECUTE ResultStmt; END IF; END --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SCRIPT(IN tblname VARCHAR,IN ProjectID INTEGER,IN UserID VARCHAR,IN dbName VARCHAR,IN Mode VARCHAR, IN CallID INTEGER, IN HHStage VARCHAR) BEGIN DECLARE Creator VARCHAR; DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt; DECLARE ResultCursor SENSITIVE CURSOR FOR ResultStmt; IF Mode <> 'Refresh' THEN PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?'; OPEN InfoCursor USING tblName; IF (ROWCOUNT(InfoCursor) > 0) THEN EXECUTE IMMEDIATE 'DROP TABLE "'+tblName+'"'; END IF; CLOSE InfoCursor; SET STATUS MESSAGE TO 'Extracting project from Calls table'; END IF; SET Creator = 'SELECT ' +'_CallID, ' +'_fkContacts, ' +'_fkCompanies, ' +'_fkSites, ' +'_LastResult, ' +'_LastAttempt, ' +'_Stage, ' +'_HideAlarm, ' +'_AlarmSet,' +'_CallBack,' +'RAND (RANGE 999999) AS _Random, ' +'_DateAdded, ' +'_Done, ' +'_IsCandidate, ' +'J._Secretary, ' +'J._UnlistedCompany, ' +'J._JobTitle, ' +'C._Name AS _ListedCompany, ' +'M._MktDesc , ' +'O._Description AS _OrgDesc, ' +'SG._Description AS _SICGroupDesc, ' +'SC._Description AS _SICCodeDesc, ' +'COALESCE(J._MainPhone, Site._Switchboard) AS _MainPhone, ' +'COALESCE(W._Forename,'''')+'' ''+COALESCE(W._Surname,'''') AS _FullName, ' +'W._Title, ' +'W._Forename, ' +'W._Surname, ' +'W._HomePhone, ' +'W._Mobile, ' +'W._HomeEMail, ' +'W._Status, ' +'IFNULL(W._LatestCV THEN FALSE ELSE TRUE) AS _HasCV, ' +'J._EMail, ' +'J._fkJobCodes, ' +'IFNULL(J._Left THEN FALSE ELSE NULL) AS _StillEmployed, ' +'COALESCE(C._Name,J._UnlistedCompany) AS _CompanyName, ' +'Site._Town, ' +'Site._County, ' +'Site._Country, ' +'Site._SiteName, ' +'C._fkMarkets, ' +'C._fkOrgType, ' +'Site._Switchboard, ' +'S._Staff AS _Staffing, ' +'T._Turnover AS _Turnover, ' +'C._fkSICGroup, ' +'C._fkSICCode, ' +'C._Status AS _CompanyStatus, ' +'C._Website, ' +'IFNULL(P._Name THEN '''' ELSE ''Parent: ''+ P._Name) AS _Parent ' +'FROM "'+dbName+'".Calls Calls ' +'LEFT OUTER JOIN "'+dbName+'".Companies C ON Calls._fkCompanies = C._ID ' +'LEFT OUTER JOIN "'+dbName+'".Contacts W ON Calls._fkContacts = W._ID ' +'LEFT OUTER JOIN "'+dbName+'".Career J ON Calls._fkCareer = J._ID ' +'LEFT OUTER JOIN "'+dbName+'".SandT S ON C._fkSandT_Staffing = S._Band ' +'LEFT OUTER JOIN "'+dbName+'".SandT T ON C._fkSandT_Turnover = T._Band ' +'LEFT OUTER JOIN "'+dbName+'".Markets M ON C._fkMarkets = M._ID ' +'LEFT OUTER JOIN "'+dbName+'".OrgType O ON C._fkOrgType = O._ID ' +'LEFT OUTER JOIN "'+dbName+'".SICGroup SG ON C._fkSICGroup = SG._ID ' +'LEFT OUTER JOIN "'+dbName+'".SICCode SC ON C._fkSICCode = SC._ID ' +'LEFT OUTER JOIN "'+dbName+'".Companies P ON C._fkCompanies_Parent = P._ID ' +'LEFT OUTER JOIN "'+dbName+'".Sites Site ON _fkCompanies = Site._fkCompanies AND _fkSites = Site._SiteID ' +'WHERE '; IF Mode = 'Callbacks' THEN SET Creator = Creator + ' _CallBack'; ELSEIF Mode = 'Alarms' THEN SET Creator = Creator + ' _fkStaff = '''+ UserID + ''' AND _AlarmSet AND NOT _Done'; ELSEIF Mode = 'Headhunts' THEN SET Creator = Creator + '_Stage = ''' + HHStage + ''''; ELSEIF Mode = 'Project' THEN SET Creator = Creator + '_fkProjects = '+ CAST(ProjectID AS VARCHAR)+' ' + 'AND ' + '(_HideSet = FALSE OR _HideAlarm IS NULL OR _HideAlarm <= CURRENT_DATE) ' + 'AND ' + '(C._Name IS NOT NULL OR W._Surname IS NOT NULL) '; ELSEIF Mode = 'Refresh' THEN SET Creator = Creator + ' _CallID = ' + CAST(CallID AS VARCHAR); END IF; IF Mode <> 'Refresh' THEN EXECUTE IMMEDIATE 'CREATE TABLE "'+ tblName +'" AS '+ Creator + ' WITH DATA'; SET STATUS MESSAGE TO 'Creating indices'; IF Mode = 'Callbacks' THEN EXECUTE IMMEDIATE 'CREATE INDEX "Contact" ON "'+tblName+'" (_Surname,_Forename)'; ELSEIF Mode = 'Alarms' THEN EXECUTE IMMEDIATE 'CREATE INDEX "Alarm" ON "'+tblName+'" (_HideAlarm)'; ELSEIF Mode = 'Headhunts' THEN EXECUTE IMMEDIATE 'CREATE INDEX "Contact" ON "'+tblName+'" (_Surname,_Forename)'; EXECUTE IMMEDIATE 'CREATE INDEX "Company" ON "'+tblName+'" (_CompanyName)'; ELSEIF Mode = 'Project' THEN EXECUTE IMMEDIATE 'CREATE INDEX "Random" ON "'+tblName+'" (_Random)'; EXECUTE IMMEDIATE 'CREATE INDEX "Contact" ON "'+tblName+'" (_Surname,_Forename)'; EXECUTE IMMEDIATE 'CREATE INDEX "Company" ON "'+tblName+'" (_CompanyName)'; EXECUTE IMMEDIATE 'CREATE INDEX "ContactCode" ON "'+tblName+'" (_fkContacts)'; EXECUTE IMMEDIATE 'CREATE INDEX "CompanyCode" ON "'+tblName+'" (_fkCompanies)'; EXECUTE IMMEDIATE 'CREATE INDEX "Done" ON "'+tblName+'" (_Done)'; EXECUTE IMMEDIATE 'CREATE INDEX "Status" ON "'+tblName+'" (_Status)'; EXECUTE IMMEDIATE 'CREATE INDEX "Stage" ON "'+tblName+'" (_Stage)'; EXECUTE IMMEDIATE 'CREATE INDEX "On-Contact" ON "'+tblName+'" (_DateAdded,_Surname,_Forename)'; EXECUTE IMMEDIATE 'CREATE INDEX "On-Company" ON "'+tblName+'" (_DateAdded,_CompanyName)'; EXECUTE IMMEDIATE 'CREATE INDEX "LastTry" ON "'+tblName+'" (_LastAttempt)'; END IF; ELSE EXECUTE IMMEDIATE 'DELETE FROM "'+tblName+'" WHERE _CallID = ' + CAST(CallID AS VARCHAR); EXECUTE IMMEDIATE 'INSERT INTO "'+tblName+'" '+ Creator; END IF; END |
Thu, May 14 2009 1:09 PM | Permanent Link |
Fernando Dias Team Elevate | Richard,
In addition to Roy's answer: -To call your SP from inside a script or another SP: CALL myDropTable('TABLEA') ; -To call your SP from inside EDB Manager, just select the procedure in the explorer, right click it and then select "Execute". -As a suggestion, you can also create a function to test if tables exist: CREATE FUNCTION "TableExists" (IN "TableName" VARCHAR COLLATE ANSI) RETURNS BOOLEAN BEGIN DECLARE Cur1 CURSOR FOR Stmt1; PREPARE Stmt1 FROM 'SELECT Name FROM Information.Tables WHERE Name = ?'; OPEN Cur1 USING TableName ; RETURN ROWCOUNT(Cur1) <> 0 ; END And then use it in your SCRIPT or SP: BEGIN ... IF TableExists('TableA') THEN DROP TABLE TableA ; END IF; ... END -- Fernando Dias [Team Elevate] |
Thu, May 14 2009 1:43 PM | Permanent Link |
"Richard Speiss" | I am pointing at my database and created a new SQL Script window. I copied
your script but when I press F9 I get an error ElevateDB Error #700 An error was found in the statement at line 15 and column 25 (Missing SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, GRANT, REVOKE, BACKUP, RESTORE, SET BACKUPS, MIGRATE, REPAIR, OPTIMIZE, IMPORT, EXPORT, DISCONNECT, REMOVE, PUBLISH, UNPUBLISH, SAVE, LOAD, SET UPDATES, COPY FILE, RENAME FILE, DELETE FILE) But I am confused by the scripts. 1. SCRIPT (IN SQLStatement .... looks pretty much like a stored procedure definition but there is no name associated with it 2. The second SCRIPT(IN .... looks like stored procedure as well I still don't see how you are calling either one from a script in DBManager Sorry for my obtuseness. Changing DB environments can be a steep learning curve and I don't have a handle on ElevateDB yet Richard |
Thu, May 14 2009 1:54 PM | Permanent Link |
"Richard Speiss" | Thank you, the CALL did the trick.
I noticed that I didn't have to use 'EXECUTE IMMEDIATE ' to call my sp. I checked the docs but it didn't seem clear on when I need to use EXECUTE IMMEDIATE and when I don't need to use it. Can you clarify its use at all? Thanks again Richard "Fernando Dias" <fernandodias.removthis@andthis.easygate.com.pt> wrote in message news:E3BD778B-AAAB-4931-BF25-55AF0968CFCE@news.elevatesoft.com... > Richard, > In addition to Roy's answer: > > > -To call your SP from inside a script or another SP: > CALL myDropTable('TABLEA') ; > > -To call your SP from inside EDB Manager, just select the procedure in the > explorer, right click it and then select "Execute". > > -As a suggestion, you can also create a function to test if tables exist: > > CREATE FUNCTION "TableExists" (IN "TableName" VARCHAR COLLATE ANSI) > RETURNS BOOLEAN > BEGIN > DECLARE Cur1 CURSOR FOR Stmt1; > PREPARE Stmt1 FROM 'SELECT Name FROM Information.Tables WHERE Name = ?'; > OPEN Cur1 USING TableName ; > RETURN ROWCOUNT(Cur1) <> 0 ; > END > > And then use it in your SCRIPT or SP: > > BEGIN > ... > IF TableExists('TableA') THEN > DROP TABLE TableA ; > END IF; > ... > > END > > -- > Fernando Dias > [Team Elevate] |
Thu, May 14 2009 2:54 PM | Permanent Link |
Fernando Dias Team Elevate | Richard,
> I noticed that I didn't have to use 'EXECUTE IMMEDIATE ' to call my sp. > I checked the docs but it didn't seem clear on when I need to use > EXECUTE IMMEDIATE and when I don't need to use it. Can you clarify its > use at all? EDB uses dynamic sql for DML, DDL and administrative statement execution in procedures, functions, jobs and scripts. So, you need PREPARE/EXECUTE or EXECUTE IMMEDIATE for: * DDL Statements http://www.elevatesoft.com/manual?action=mancat&id=edb2sql&category=10 * DML Statements http://www.elevatesoft.com/manual?action=mancat&id=edb2sql&category=11 * Administrative Statements http://www.elevatesoft.com/manual?action=mancat&id=edb2sql&category=14 You don't need it for: *SQL/PSM Statements http://www.elevatesoft.com/manual?action=mancat&id=edb2sql&category=13 -- Fernando Dias [Team Elevate] |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |