Source language: Translate to:

Stored Procedure

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Stored Procedure

Postby antonio45 » Mon Jun 18, 2007 9:13 am

How can I use the Stored Procedure which is being returned a table in neobookDBPro. For example ;


CREATE PROCEDURE [dbo].[SP_ReturnTable]
(
@Curr int

)
--##--
AS
SELECT
CODE,
DATE_,
TRANNO,
DETAIL,
DOCODE,
LINEEXP,
TRCURR,
ISNULL(CURCODE,'YTL') KURADI,
dbo.__MIS2_fn_CORE_Debit(TRNET, SIGN) ID_Debit,
dbo.__MIS2_fn_CORE_Credit(TRNET, SIGN) ID_Credit,
dbo.__MIS2_fn_CORE_Debit(dbo.[__MIS2_fn_GetRealAmount](@Curr,DATE_,AMOUNT,TRRATE, TRNET,TRCURR,0),SIGN)-
dbo.__MIS2_fn_CORE_Credit(dbo.[__MIS2_fn_GetRealAmount](@Curr,DATE_,AMOUNT,TRRATE, TRNET,TRCURR,0),SIGN) Amount
FROM __MIS2_vw_CCariEkstre CC
LEFT JOIN __MIS2_vw_IslemTurleri IT
ON CC.MODULENR = IT.MODULENR AND CC.TRCODE = IT.TRCODE
LEFT JOIN L_CURRENCYLIST LC
ON FIRMNR=1 AND CURTYPE=TRCURR


This Stored procedure is being returned a Table. Can I use this stored procedure in NeobookDbPro plugin? If answer is Yes, how can I assigned this stored procedure to grid ?


Second question is, Can I use a formula column ?

Best regards,

Mehmet BAKITKAL






:?: [/b]
antonio45
 
Posts: 42
Joined: Fri Mar 31, 2006 11:37 am

Postby Neosoft Support » Mon Jun 18, 2007 11:09 am

You can use the dbpExecSQL action to create a stored procedure if the database you're using supports it. Not all database types support stored procedures.

It's currently not possible to display the results of a stored procedure in a separate grid unless the results are stored in a table. You can create a special table used just for the results if needed.

I'm not sure about creating a formula column. You may have to create it manually using the dbpExecSQL action. Otherwise, if the fields to be combined are all within the same record, you can use NeoBook's Math action. For example:

Math "[DB1.Sales.Total]*[DB1.Sales.Commission]" "2" "[Result]"
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5593
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Stored Procedure

Postby antonio45 » Mon Jun 18, 2007 1:27 pm

Thank you for your quick reply. I want to use formula column in a grid. So I need an extra column for the formula column. So If I use a view I can add a formula column in view, but if I use data table I can't use MATH function in a grid. I hope you can add this property this plugin. (I use MSSQL)

Thank you for your helps again.

Mehmet BAKITKAL
antonio45
 
Posts: 42
Joined: Fri Mar 31, 2006 11:37 am

Stored Procedure

Postby antonio45 » Tue Jun 19, 2007 5:52 am

Hello,
I found a solution for formula column and I want to share this information with you. If you use dbpExecSQL like following, you can add a formula column

dbpOpenDatabase "MyDatabase" "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MYDATABASE;Data Source=."
SetVar "[tablo]" "ReturnTable"
dbpExecSQL "MYDATABASE" "SELECT| MODULENR,| TRNET,| TRNET*10 AS FORMULA_COLUMN |FROM [tablo]"
dbpOpenTable "MYDATABASE" "ReturnTable" ""
dbpShowGrid "MYDATABASE" "ReturnTable" "Rectangle1"


Mehmet BAKITKAL[/b]
antonio45
 
Posts: 42
Joined: Fri Mar 31, 2006 11:37 am

Postby Neosoft Support » Tue Jun 19, 2007 10:01 am

Thank you for posting your solution.

Apparently, some types of databases (NOT MS Access or MySQL) allow you to add a formula column/field when creating the table. For example:

dbpExecSQL "Test" "CREATE TABLE mytable ( low int, high int, myavg int AS (low + high)/2 )"
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5593
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest

cron