Multiple SQL statements and procedural language reference?

[Former Member]

Hello,

I'm hoping you can point me in the right direction. I am looking for a way to write some logic within a series of SQL statements using the MaxDB command line tools.

For example, with Oracle I can include this in a text file:

VARIABLE l_count NUMBER;

BEGIN

execute immediate 'select count(*) from TABLE1' into :l_count;

if (:l_count < 1) then

BEGIN

execute immediate 'CREATE TABLE JUNK (NAME VARCHAR2(20))';

END;

end if;

END;

/

EXIT;

Then execute it through SQLPlus from the command line

Sqlplus user/pw@database @thisfile.txt

On MS SQL server, logic can be accomplished a little differently using sqlcmd, for example:

Sqlcmd -S server -d database -U user -P password -Q "if exists (select * from dbo.sysobjects where id = object_id(N'[TABLE1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) begin if not exists (select * from TABLE1 ) create table [JUNK] (NAME VARCHAR(20) end

And with MySQL

select count(*) into @count from TABLE1; set @sqlToExecute = if (@count = 0, 'CREATE TABLE JUNK (NAME VARCHAR(20))', 'set @doNothing=1'); select @sqlToExecute; PREPARE stmt1 FROM @sqlToExecute; EXECUTE stmt1; DEALLOCATE PREPARE stmt1;

Is it possible to use one of these methods with sqlcli or loadercli either from the command line or a batch file?

If so is there anywhere in the documentation that might contain examples? I've found one example of a sample database procedure in the manual but haven't found a syntax reference for the procedural language.

If I open, or save locally and then open, the Help file from the HTMLHelp file link here:

http://maxdb.sap.com/documentation/

I'm receiving a "Navigation to the webpage was canceled error". I can see the table of contents but cannot navigate to any pages so I have not found a way to search the documentation.

Thanks,

Troy

[lbreddemann]

> I'm hoping you can point me in the right direction. I am looking for a way to write some logic within a series of SQL statements using the MaxDB command line tools.

> Is it possible to use one of these methods with sqlcli or loadercli either from the command line or a batch file?

You can of course run a batch file in which you create a stored procedure that you then execute.

But what you describe really sounds as if you want to use pearl or python to run your statements.

And - just a note - loadercli is for exporting/importing data.

It's not meant to run arbitrary sql commands.

> If so is there anywhere in the documentation that might contain examples? I've found one example of a sample database procedure in the manual but haven't found a syntax reference for the procedural language.

The language is actually a kind of PL/I - not too complex.

This page in the documentation [Routine (routine)|http://maxdb.sap.com/doc/7_6/a7/41ee29605911d3a98800a0c9449261/content.htm] covers the main parts.

> If I open, or save locally and then open, the Help file from the HTMLHelp file link here:

> http://maxdb.sap.com/documentation/

> I'm receiving a "Navigation to the webpage was canceled error". I can see the table of contents but cannot navigate to any pages so I have not found a way to search the documentation.

Hmm--- I had no issues with downloading the help files.

Anyhow, there is a search facility available for the online help (same site) too.

Hope that helps,

Lars