create function with odbc

[Former Member]

Hi all,

I'd like to create a function using the CREATE FUNCTION statement. The MAXDB help file gives a sample that works fine when I use SQL Studio. But when I try to define that function using my application, which connects to the maxdb using the odbc driver, then nothing happens, the command is not accepted. The semicolon seems to be a problem.

Because the function contains several commands that are separated by semicolon ";" I suppose I have to define another delimiter temporarily. But I could't find any help on that.

Any ideas?

[Former Member]

Hi,

I'm using OMNIS7 for development. In SQL Studio the following code works:

CREATE FUNCTION pat_list_names (pat INTEGER) RETURNS VARCHAR AS

VAR aName VARCHAR(50);

aCSV VARCHAR(100);

TRY

SET aName = ''; SET aCSV = '';

SELECT pll_name FROM DBADMIN.f_patlist, DBADMIN.f_patlists WHERE pll_id=pl_id and pl_pa_id = :pat;

WHILE $rc = 0 DO BEGIN

FETCH INTO :aName;

IF aCSV='' THEN

SET aCSV = aName

ELSE

SET aCSV = aCSV & ', ' & aName;

END;

CATCH

IF $rc <> 100 THEN STOP ($rc, 'unexpected error');

RETURN aCSV;

From within OMNIS7 I use the following code that should be the exact equivalent:

Set current session

Begin SQL script

SQL: CREATE FUNCTION PAT_LIST_NAMES (pat INTEGER) RETURNS VARCHAR AS

SQL: VAR aName VARCHAR(50);

SQL: aCSV VARCHAR(100);

SQL: TRY

SQL: SET aName = ''; SET aCSV = '';

SQL: SELECT pll_name FROM DBADMIN.f_patlist, DBADMIN.f_patlists

SQL: WHERE pll_id=pl_id and pl_pa_id = :pat;

SQL: WHILE $rc = 0 DO BEGIN

SQL: FETCH INTO :aName;

SQL: IF aCSV='' THEN

SQL: SET aCSV = aName

SQL: ELSE

SQL: SET aCSV = aCSV & ', ' & aName;

SQL: END;

SQL: CATCH

SQL: IF $rc <> 100 THEN STOP ($rc, 'unexpected error');

SQL: RETURN aCSV;

End SQL script

Execute SQL script

If flag false

OK message (High position,Large size) {Failure ! Function could not be created...}

End If

Call procedure P_SQL.1

Whenever I send a command that contains the semicolon ";" the command is not accepted. I don't know if this is a restriction of the odbc driver, of OMNIS7 or whatever. I thought of a delimiter problem but I didn't found a delimiter command for MAXDB.

[lbreddemann]

Thanks for the code-snippets.

I don't think that this is an ODBC related issue. SQL Studio also uses ODBC for the connection to the database.

I rather think that the way OMNIS parses your sql script is causing the effect.

As I don't know OMNIS I don't know if it should be able to create procedures in the database like this.

Does it e.g. work with an Oracle database?

BTW: creating database objects in the SYSDBA schema (that's likely DBADMIN in your database) isn't considered to be a good idea.

Better create your own user/schema and put everything required for your application in there.

regards,

Lars

[Former Member]

Hi Lars,

Thank you for your response. I'll try to find a solution, maybe I can find a code snippet on this. In general it shouldn't be a problem.

Chris

[lbreddemann]

Hi there,

can you post the coding for this issue to reproduce please?

thanks,

Lars