Does go_ibm_db support running `CREATE OR REPLACE FUNCTION` about go_ibm_db HOT 11 CLOSED

See you can clearly see that it is not accepting ';' at the end of the statement so remove it in the program.

But when it comes to .go program you are using fmt.Sprintf(``) and you added \ at the start and end of days('1970-01-01-00.00.00.000000') so you have to remove \ and ; in the go program.

make the changes and run the program.

If you face any issues let me know.

Thanks,
Akhil

@akhilravuri1 Thanks I removed both the \ and ; and it worked for the

CREATE OR REPLACE FUNCTION %[1]s (in db2ts timestamp)
    		returns bigint
    		language sql
    		deterministic
    		no external action
    		return (
        		bigint((days(db2ts - current timezone) - days(\'1970-01-01-00.00.00.000000\')) * 86400 + midnight_seconds(db2ts - current timezone))*1000 + microsecond(db2ts)/1000
			);
	

Will try the other one now and see what happens, the other SUBSTRING_INDEX one is a little more complicated.

from go_ibm_db.

Other one worked as well after removing the ;; at the end. Thanks

from go_ibm_db.

Hi @devanshah2
we don't have test cases for creating a function but it should be working as it is an SQL query.

Thanks,
Akhil

from go_ibm_db.

Hi @devanshah2
we don't have test cases for creating a function but it should be working as it is an SQL query.

Thanks,
Akhil

@akhilravuri1

I had tried a quick example as follows:

const query := '--#SET TERMINATOR ;;
CREATE OR REPLACE FUNCTION SUBSTRING_INDEX(in varchar(2000), delimit varchar(200), n Int)
                  returns varchar(2000)
                  deterministic
                    no external action
                    language sql
                    contains sql
                    begin atomic
                        declare out varchar(2000);
                        declare dem varchar(2000);
                        declare num int;
                        declare pos int;
                        declare temp varchar(2000);

                        set dem=delimit;
                        set temp=In;
                        set num=n;
                        set pos=1;

                        if(num<0) then
                            while(locate(delimit,temp)!=0) do
                                set temp=substr(temp,locate(delimit,temp)+1);
                                set num=num+1;
                            end while;
                            set num=num+1;
                            set temp=In;
                        end if;

                        while (num>0) do
                            set pos=pos+locate(delimit,temp)-1;
                            set temp=substr(temp,locate(delimit,temp)+1);
                            set num=num-1;
                        end while;

                        if(n>0) then
                            return substr(In,1,pos);
                        else
                            return substr(In,pos+1);
                        end if;
                    end;;
--#SET TERMINATOR ;'

	db, err := sql.Open("go_ibm_db", con)
	if err != nil {
		log.Error(err)
		return nil, err
	}

db.Exec(query)

and it throws error when I run it:

SQLExecute: {42601} [IBM][CLI Driver][DB2/LINUXX8664] SQL0104N An unexpected token "begin atomic declare out" was found following "contains sql ". Expected tokens may include: "". LINE NUMBER=60. SQLSTATE=42601

from go_ibm_db.

Hi @devanshah2

This error is thrown by the server. So please try the same sql with db2cmd and check whether the sql is accepted or not.

Thanks
Akhil

from go_ibm_db.

Hi @devanshah2

This error is thrown by the server. So please try the same sql with db2cmd and check whether the sql is accepted or not.

Thanks
Akhil

The same works just fine in the db2cmd and using db2 -tfw sample.sql

Anything else that I can try? to verify this?

from go_ibm_db.

@devanshah2 can you please send the sample program to reproduce the issue.

Thanks,
Akhil

from go_ibm_db.

@devanshah2 can you please send the sample program to reproduce the issue.

Thanks,
Akhil

Following is a runable program:

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/ibmdb/go_ibm_db"
)

func createCon(con string) *sql.DB {
	db, err := sql.Open("go_ibm_db", con)
	if err != nil {

		fmt.Println(err)
		return nil
	}
	return db
}

func execquery(db *sql.DB) error {

	query := fmt.Sprintf(`
		CREATE OR REPLACE FUNCTION %[1]s (in db2ts timestamp)
    		returns bigint
    		language sql
    		deterministic
    		no external action
    		return (
        		bigint((days(db2ts - current timezone) - days(\'1970-01-01-00.00.00.000000\')) * 86400 + midnight_seconds(db2ts - current timezone))*1000 + microsecond(db2ts)/1000
			);
	`, "utcmillis")

	// Execute DDL Command/Query
	_, err := db.Exec(query)

	// Handle error from query execution
	if err != nil {
		fmt.Errorf("Unable to execute query due to error: %v", err)
		return err
	}

	return nil
}

func main() {
	con := "HOSTNAME=host;DATABASE=name;PORT=number;UID=username;PWD=password"

	db := createCon(con)

	err := execquery(db)
	if err != nil {
		fmt.Println(err)
	}
}

Which is able to re-produce error:

SQLExecute: {42601} [IBM][CLI Driver][DB2/LINUXX8664] SQL0007N The statement was not processed because a character that is not supported in SQL statements was included in the SQL statement. Invalid character: "". Text preceding the invalid character: "nt timezone) - days(". LINE NUMBER=7. SQLSTATE=42601

Will need to fill in your own HOSTNAME=host;DATABASE=name;PORT=number;UID=username;PWD=password thanks

from go_ibm_db.

@devanshah2

can you please create create_func.t file with the below content

opt echo on
opt callerror on
SqlAllocEnv 1
SqlAllocConnect 1 1
SqlDriverConnect 1 1 "DATABASE=go;HOSTNAME=localhost;PORT=50000;UID=uname;Pwd=***;PROTOCOL=TCPIP" -3 255 sql_driver_noprompt
sqlallocstmt 1 1
sqlprepare 1 "CREATE OR REPLACE FUNCTION utcmillis (in db2ts timestamp) returns bigint language sql deterministic no external action return ( bigint((days(db2ts - current timezone) - days(\'1970-01-01-00.00.00.000000\')) * 86400 + midnight_seconds(db2ts - current timezone))*1000 + microsecond(db2ts)/1000 );" -3
sqlexecute 1
SqlFreeStmt 1  Sql_close
SqlTransact 1 1 Sql_commit
Killenv 1

please add your connection details
How to run create_func.t file:-

1. open a command prompt
2. cd to the create_func.t file directory
3. run this commad in the commad prompt

   db2cli < create_func.t
   

from go_ibm_db.

db2cli < create_func.t

@akhilravuri1 Ran and following was the output:

IBM DATABASE 2 Interactive CLI Sample Program
(C) COPYRIGHT International Business Machines Corp. 1993,1996
All Rights Reserved
Licensed Materials - Property of IBM
US Government Users Restricted Rights - Use, duplication or
disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
> > opt callerror on
> SqlAllocEnv 1
SQLAllocEnv: rc = 0 (SQL_SUCCESS)
             CLI henv = 1, Test Driver henv = 1
> SqlAllocConnect 1 1
SQLAllocConnect: rc = 0 (SQL_SUCCESS)
             CLI hdbc = 1, Test Driver hdbc = 1
> SqlDriverConnect 1 1 "DATABASE=go;HOSTNAME=localhost;PORT=50000;UID=uname;Pwd=****!;PROTOCOL=TCPIP" -3 255 sql_driver_noprompt
SQLDriverConnect: rc = 0 (SQL_SUCCESS)
SQLDriverConnect: ConnStrOut:
 		UID=uname;PWD=******;DATABASE=go;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;
                  cbConnStrOut: 91
> sqlallocstmt 1 1
SQLAllocStmt: rc = 0 (SQL_SUCCESS)
              CLI hstmt = 1, Test Driver hstmt = 1
> sqlprepare 1 "CREATE OR REPLACE FUNCTION utcmillis (in db2ts timestamp) returns bigint language sql deterministic no external action return ( bigint((days(db2ts - current timezone) - days('1970-01-01-00.00.00.000000')) * 86400 + midnight_seconds(db2ts - current timezone))*1000 + microsecond(db2ts)/1000 );" -3
SQLPrepare: rc = 0 (SQL_SUCCESS)
> sqlexecute 1
SQLExecute: rc = -1 (SQL_ERROR)
SQLError: rc = 0 (SQL_SUCCESS)
SQLError: SQLState     : 42601
          fNativeError : -104
          szErrorMsg   : [IBM][CLI Driver][DB2/6000] SQL0104N  An unexpected token ";" was found following "second(db2ts)/1000 )".  Expected tokens may include:  "END-OF-STATEMENT".  LINE NUMBER=1.  SQLSTATE=42601

          cbErrorMsg   : 189
SQLError: rc = 100 (SQL_NO_DATA_FOUND)
> SqlFreeStmt 1  Sql_close
SQLFreeStmt: rc = 0 (SQL_SUCCESS)
> SqlTransact 1 1 Sql_commit
SQLTransact: rc = 0 (SQL_SUCCESS)
> Killenv 1
SQLDisconnect: rc = 0 (SQL_SUCCESS)
SQLFreeConnect: rc = 0 (SQL_SUCCESS)
SQLFreeEnv: rc = 0 (SQL_SUCCESS)
>

from go_ibm_db.

See you can clearly see that it is not accepting ';' at the end of the statement so remove it in the program.

But when it comes to .go program you are using fmt.Sprintf(``) and you added \ at the start and end of days('1970-01-01-00.00.00.000000') so you have to remove \ and ; in the go program.

make the changes and run the program.

If you face any issues let me know.

Thanks,
Akhil

from go_ibm_db.