Calling Stored procedure from E1 BSFN

[ramkitkv]

Has anyone created C BSFN to call a Stored procedure in E1. If so How to call stored procedure.

Thanks,

 

[Sebastian Sajaroff]

Hi,

And what is the database behind this?
MSSQL? Oracle? DB2/400? UDB?

 

[ramkitkv]

Currently we are in SQL Server - 2000.

Thanks,

 

[Larry_Jones]

Probably Access

 

[Gopal]

We can call stored procedures from E1 using C BSFN.





Use the below Syntax and create your C BSFN.





/* Allocate Environment Handle */

retCode =3D SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &myEnvnHndl);



/* Set the ODBC version */

if (retCode =3D=3D SQL_SUCCESS)

retCode =3D SQLSetEnvAttr(myEnvnHndl, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)
SQL_OV_ODBC3, SQL_IS_UINTEGER);



/* Allocate Connection Handle */

if (retCode =3D=3D SQL_SUCCESS)

retCode =3D SQLAllocHandle(SQL_HANDLE_DBC, myEnvnHndl, &myConnHndl);

if (retCode =3D=3D SQL_SUCCESS)

{

/* Connect to ODBC Database */

/* retCode =3D SQLConnectW(myConnHndl, dnsName, SQL_NTS, dnsUser,
SQL_NTS, dnsPswd, SQL_NTS); */

retCode =3D SQLDriverConnectW(myConnHndl,

NULL,

szDnsName,

SQL_MAX_OPTION_STRING_LENGTH,

NULL,

SQL_NTS,

0,

SQL_DRIVER_NOPROMPT);

/* Allocate SQL Statement Handle */

retCode =3D SQLAllocHandle(SQL_HANDLE_STMT, myConnHndl,
&myStmtHndl);

if (retCode =3D=3D SQL_SUCCESS)

{

/* SQL Query */

retCode =3D SQLPrepare(myStmtHndl, lpDS->szSQLQuery, SQL_NTS);

/* Specify address of variable to hold the number of rows fetched

retCode =3D SQLSetStmtAttr(myStmtHndl, SQL_ATTR_ROWS_FETCHED_PTR,

if (retCode =3D=3D SQL_SUCCESS)

{

/* Execute Query */

retCode =3D SQLExecute(myStmtHndl);

}

}

}



Assuming your database is SQL.



Gopal.

 

[tpw_nz]

You could create an E1 table that has an SQL (not E1) insert trigger. Given appropriate columns in the E1 table this will also allow you to use parameters in your stored procedure call.

 

[ramkitkv]

Thank you Gopal,

I will create a C BSFN to call the Store procedure.

I also have another question. Will this BSFN work for ORACLE as well. There are internal discussions going on to go for ORACLE as the database server in Future.

Thanks,
[email protected]

 

[peterbruce]

ramkitkv,

Here are a couple of posts with options that do not require writing c code:

Running Oracle SDPs From E811/TR8.96
Calling SQL from within an E1 App