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

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]

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]

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]

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

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

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]

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

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]

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]