Question about SQL

[Manuel Valdenebro]

.. do you have a sample connection string you are using for your Oracle ADO recordsets ?? msadora or are you using oraoledb ??

Rick,

I know you are a master. I have learned with you. I reply you thinking in all people (every level).

Although I can using msadora (MS-Ole) or oraoledb (Oracle native provider) I am using last one, because it is advises in Oracle Web and for compatibility with PL/SQL Oracle language. For check connection, you can use FDATALINK function.

Following Nages advices, at the beginning, I have a connection to Database (unique connection). I have a function in my personal library, for recordset. I call this function when I need and return recordset object. After I can use that recordset-object for "browse" or another thing.

I show you an example of my connection code and recordset:

STATIC oWnd
static oCon // conexion
STATIC oRs // recordset
////////////////////////////////////////////////////////////////////
/// cHost = name of database in the tnsname.ora file
/// cUser standar Oracle user, you can change it
/// cPass standar password Oracle, you can change it
FUNCTION MAIN ()
Local cProv := "OraOLEDB.Oracle.1" ;
cHost := "XE" ;
cUser := "hr" ;
cPass := "hr"

// create object connection
oCon := TOLEAuto():New('ADODB.Connection')

oCon:ConnectionString := "Provider=" + cProv + ";" + ;
"Data Source= " + cHost + ";" + ;
"User ID=" + cUser + ";" + ;
"Password=" + cPass + ";"

// Connection Database / conexión con la Base de Datos
TRY
oCon:Open()
CATCH oError
AdoError(oCon, oError) // personal function for show errors.
QUIT
END

////////////////////////////////////////
/// RF original code
/// from spanish forum FWH
/// Crea recordset de una orden Sql
/// Create recordset from Sql order
/// lVacio (lEmpty) if .t. error
/// lVacio := .f. for new table
///////////////////////////////////////
FUNCTION F_RECORDSET (oCon, oRs, cSql, lVacio)
local oError
DEFAULT lVacio := .T.
oRs := TOleAuto():New("adodb.recordset")
oRs:CursorLocation := 3 // adUseClient (no funciona con servidor)
oRs:LockType := 3 // adLockOptimistic (solo bloque en update)
oRs:CursorType := 1 // adOpenKeyset
oRs:Source := cSql
oRs:ActiveConnection(oCon)

TRY
oRs:Open( )
CATCH oError
AdoError(oCon, oError) // personal function for show errors.
RETURN nil
END
IF lVacio
IF oRs:EOF .and. oRs:BOF // Jose Luis Capel spanish blogs
msgstop ("Tabla vacia") // Empty Table
oRs:CLOSE()
oRs:=Nil
RETURN nil
ENDI
oRs:MoveFirst()
ENDI

RETURN oRs


////////////////////////////////////////////////////
/// From Biel Code (spanish blogs)
/// Comprobar conexión a la base de datos
/// Check connection to Database
////////////////////////////////////////////////////
FUNCTION FDATALINK ( )
LOCAL oDataLink := TOleAuto():New("Datalinks"),;
oConn := oDataLink:PromptNew()
valert (oConn:ConnectionString)

RETURN nil


Regards

[nageswaragunupudi]

Master Nageswaragunupudi,

I am including "ado.ch", but I have seen you use for 'CreateParameter', adDate, adParamReturnValue, adInteger and adParamInput. Is there another "include" for this?
Where can I get more information for CreateParameter?

Please see this
http://www.w3schools.com/ado/default.asp
we get all information here
For more information refer to msdn

[nageswaragunupudi]

Here is the include file for all the constants.
http://rapidshare.com/files/83948081/adodef.zip.html

[nageswaragunupudi]

I thought this website will be of interest to those who look for connection strings for different databases

http://www.connectionstrings.com/

[Manuel Valdenebro]

Please see this
http://www.w3schools.com/ado/default.asp
we get all information here
For more information refer to msdn

Thanks again Mr Nageswaragunupudi.

Regards

[Manuel Valdenebro]

Mr NageswaraRao

I was using "CreateParameter" for get the return of a PL/SQL function. But this morning, I am surprised, because I can get same result from a simple SELECT .... FROM DUAL using RecordSet. This is easier because avoid to do one xHarbour function for each PL/SQL function.

I would like your comments.

Regards

[nageswaragunupudi]

Yes.

What I gave you was a simple example for knowing how to use functions and stored procedures from ADO.

The use of serverside procedures and functions are justified under two circumstances:

First reason is simple. Where the result depends on complex data processing, functions / procedures are desirable. If we read individual rows of data all the way from server to client for further processing, it takes more time. Instead the collection and processing the data on the server itself is fast and after processing, the function can provide the processed restult(s).

Second and most important reason why server side procedures are preferred is to maintain safety and integrity of data, uniform business logic and to make the business logic as well as data design ndependent of the front-end. Elaboration of this topic is more involved and we can discuss about it, if you are really interested.

[Manuel Valdenebro]

What I gave you was a simple example for knowing how to use functions and stored procedures from ADO.

Mr. Nagesrarao,

Perhaps I didn't explained very well. I like PS/SQL function and I am using it. I am satisfied their utilities and I understand their value.

You know we have 2 steps for use PL/SQL function/procedure:

1) Creating PL/SQL function/procedure with SQL+, SqlDeveloper, PL/SQL Developer, or other aplication.

2) Our xHarbour/FWH aplication must get the return value of that function/procedure.
In this second step, I used 'ADODB.Command' and createparameters, following your example in 'FUNCTION TestOraFunc( nAddDays )'. But after that, I have discovered I can get the return value of a PL/SQL function/procedure with a general 'ADODB.RECORDSET' function join a "select". It is the same method that used Oracle program I have mentioned above (Sql+, SqlDeveloper, etc), for executes a PL/SQL function. This method is easier and more simple and saving to do, which each PL/SQL function, an individual function in xHarbour, with several parameters (complicated).

I hope you can understand now.

Regards.