Calling SqlImmediate inside the function

forum.advanced.programming (1998-2005)
Sooraj

Calling SqlImmediate inside the function

Post by Sooraj » 06 Jan 2005, 07:03

 Posted by:  Sooraj 

Hai Friends,

If we connect the internal handle to a database by calling SqlImmediate and
it remains connected until the application terminates or you explicitly
disconnect it with SqlClearImmediate.So I replaced all SqlImmediate() with "
fnSqlImmediate( )".Which is a user defined function declared in the form
level. Function contains a SqlPrepareAndExecute & and a SqlFetchRow.Which
works similar to SqlImmediate( ). The only difference is that it uses an
external SQL handle.If I use " fnSqlImmediate( )" inside the function it
will generate an error if it contains an function variable inside the INTO
part, because the function executes in the form level.How can I eliminate
this problem with out using a SqlPrepareAndExecute & and a SqlFetchRow at
each level where we use SqlImmediate( ).

Thanks in Advance,
Sooraj

Igor Ivanovic
Site Admin
Site Admin
Croatia
Posts: 1202
Joined: 05 Mar 2017, 12:37
Location: Zagreb, Croatia

Calling SqlImmediate inside the function

Post by Igor Ivanovic » 06 Jan 2005, 09:23

 Posted by:  Igor Ivanovic 

Sooraj,

Use SqlVarSetup before calling your fnSqlImmediate function. It saves
the current execution context for the next SQL functions.
See: Developing with SQLWindows, page 12-45.

Or, you can extend you fnSqlImmediate to receive the current context
as a parameter and then use SqlVarSetup inside your function.
Assuming that the handle (p_hSql) and context (p_sContext) are your
function parameters you can achieve it by the following code:

!!CB!! 73
Set bOk = SqlPrepare( p_hSql, p_sStatement )
Set sExpr = "SqlVarSetup( SalNumberToWindowHandle( " ||
SalNumberToStrX( SalWindowHandleToNumber( p_hSql ), 0 ) || " ))"
If bOk
Set nType = SalCompileAndEvaluate( sExpr, nError, nErrorPos,
nReturn, strReturn, dtReturn, hWndReturn, bInhibitErrors, p_sContext )
If nError
Set bOk = FALSE
Set bOk = bOk and SqlExecute( p_hSql )

We have completely replaced the Sql functions with our own, so we have
full control over timeouts and the user can choose to wait or cancel
the execution.

Igor

Sooraj

Calling SqlImmediate inside the function

Post by Sooraj » 06 Jan 2005, 11:43

 Posted by:  Sooraj 

Hello Igor ,
Thanks for the response.Can you please explain this in detail.When I tried
the code it quits the application without generating an error.

I want to execute the below code using global fnSqlImmediate( ) function.

!!CB!! 70
fnSelectNationality( )
Call fnSqlImmediate( "
SELECT nationality INTO :strNationality_F
FROM Employee
WHERE empid = 1000001 " )

Local Variable
Number : strNationality_F

Can you please explain...

Sooraj

FRBhote
India
Posts: 2208
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Calling SqlImmediate inside the function

Post by FRBhote » 06 Jan 2005, 12:08

 Posted by:  F R Bhote 

Function: fnExists
Description:
Returns
Boolean:
Parameters
Sql Handle: hSql
Static Variables
Local variables
Number: nFetch
Actions
Call SqlContextSet( hSql )
Call SqlExecute( hSql )
Call SqlFetchNext( hSql, nFetch )
Call SqlContextClear( hSql )
If nFetch = FETCH_Ok
Return TRUE
Else
Return FALSE

Dmitry Parkhomenko

Calling SqlImmediate inside the function

Post by Dmitry Parkhomenko » 08 Jan 2005, 15:05

 Posted by:  Dmitry Parkhomenko 

You can use 'full-path' variable notification in INTO clause instead of
simple variable. For example
SELECT ....
INTO :myVal
use
SELECT ....
INTO :hWnd.MyFrm.myVal

Sooraj

Calling SqlImmediate inside the function

Post by Sooraj » 18 Jan 2005, 06:20

 Posted by:  Sooraj 

Hello...

If "INTO variable" is a form level variable it will work.But if it is a
function variable it will show error.But in the centura books online it is
defined that this will work in the case of internal fiunction also.Because
in my case it is not that much easy to change all function variable to form
level.How can I solve this problem? Is it is possible?

Function: fnGetNationality
Returns
Boolean:
Parameters
Static Variables
Local variables
Receive String: strNationality_F
Actions
Call fnSqlImmediate( "
SELECT nationality INTO :strNationality_F
FROM Employee
WHERE empid = " )

Function: fnSqlImmediate ! Global Function
Returns Boolean:
Parameters
String: sSql
Static Variables
Local variables
Number: nInd
Boolean : bOK
Actions
Call SqlContextSet ( hSqlImmediate )
If SqlPrepare( hSqlImmediate, sSql)
If SqlExecute( hSqlImmediate )
IOf SqlFetchNext( hSqlImmediate, nInd )
Set bOK = TRUE
Else
Set bOK = FALSE
Call SqlContextClear ( hSqlImmediate )

hSqlImmediate Global Handle

The above function returns an error because strNationality_F is Function
local variable .
If strNationality_F is Form level variable it works.

Sooraj

Martin Knopp

Calling SqlImmediate inside the function

Post by Martin Knopp » 18 Jan 2005, 08:04

 Posted by:  Martin Knopp 

The problem is that the execution context of the SQL statement is in
fnSqlImmediate and not in fnGetNationality - SqlVarSetup is available
exactly to define a different execution context for a SQL statement but it
needs to be called after the SqlPrepare is done. So you probably would need
to split up your fnSqlImmediate. A possible solution might be to pass in a
SqlContextCurrent() as a parameter to fnSqlImmediate and do a
SalCompileAndEvaluate with "SqlVarSetup( hSqlImmediate )" within
fnSqlImmediate after the SqlPrepare using this passed in context - I didn't
try it but it could work.

BTW: SqlContextSet is depricated and had been replaced with SqlVarSetup and
calling it within fnSqlImmediate doesn't seem to make sense at all to me.

--
Martin Knopp, Dipl. Ing.

fecher GmbH
good people - good software

D'Orsay-Gasse 4/1
A-1090 Wien

Phone +43-1-3199124-0
Fax: +43-1-3199124-80
mailto:Martin.Knopp@fecher.at
http://www.fecher.at
------------------------------------------

We would be happy to inform you regarding the following topics

-- Cross platform development with SQLBase and Team Developer for Windows
and Linux

-- buildingBLOCKS XP: Web User Interfaces with ASP.NET and Team Developer

-- classCOMPILER.NET: Compiling non-visual SAL code to MSIL (.NET) more at
http://www.fecher.at/classcompiler

Return to “advanced.programming”

Who is online

Users browsing this forum: [Ccbot] and 1 guest