Question about SQL

[Manuel Valdenebro]

oRs:Requery() is a useful tool as well as oRs:ReSync(1,2) .. do not leave the resync parameters out .. and you MUST have a 'primary key' set on your table for the Resync method to work.
ReSync is a must in a networked environment especially for workstation update visability... if you use local recordsets ( which I recommend )

Rick Lipkin

Rick thanks. I unknowledge resync command. I understand now, REQUERY only actualize local SELECT versus RESYNC that incluiding all net-records that can be insert while you are working.

I read RESYNC has relation with CATALOG. I think CATALOG is DB-name but really I dont know exactly its utilities.

Thanks for all your advices.

[Enrico Maria Giordano]

Do you mean ADOX.Catalog? It is for dealing with the database structure.

EMG

[Manuel Valdenebro]

Do you mean ADOX.Catalog? It is for dealing with the database structure.

EMG

Thanks Enrico. Now I know from Microsoft support that Adox.Catalog is for MS-Jet motor specially.

Regards

[Enrico Maria Giordano]

This is not true. It also works at least with MS SQL Server.

EMG

[Rick Lipkin]

Manuel

Here is an example of using resync() .. Lets say you have two workstations accesing the same table using the same SQL code .. so basically both workstations have identical copies of their local recordset.

Workstation 1 changes row 2 .. workstation 2 clicks on row 2 and they have 'stale' information because there was a change that was made behind them... signature gets incremented++

In a multi-user environment I use a 'signature' field called Updated, N, 5.

I initialize all my variables before I display the information. I use resync() to go freshen up the local recordset and to make sure the table and the recordset are identical... that is why you need a 'primary key' on the table. SQL looks to that 'primary key' to go find that row and update the record.

As I go thru my edit routine I increment Updated++( signature field ) . If workstation 2 clicks on the same row .. my pre-edit routine does a quick ( new ) table recordset ( call ) just on "updated" If the initial "updated" value is the same as the table .. I allow the edit to be written .. if the two values are different .. I know someone has slipped behind workstation 2 and written changes to the same row in the same table .. and I stop the edit on workstation 2 from over-writing what workstation 1 just changed.

Hope that makes sense.

Rick

[Manuel Valdenebro]

My involvement with Oracle is not limited to just using ADO, but includes PL/SQL, making Oracle packages and interfacing them with ADO.

Nages,

I would like to use Oracle PL/SQL with FWH. Can you showme an example of function or procedure with PL/SQL?

Regards

[nageswaragunupudi]

ADOX works with Oracle too. But ADOX is slower than using native functions of Oracle.

PLSQL is the language used for writing procedures and functions to be stored on Oracle Server. They are like our procedures and functions, but written in PLSQL language understood by Oracle server. They are directly stored on the server as objects. Oracle can directly read them and execute them. Like our program modules, we can group several procedures and functions into Packages.

From the client we can execute those functions or procedures through ADO's execute method of connection object and read the return values.

MSSQL's language is T-SQL. ( Transact SQL). For MSSQL servers we write the procedures and functions in TSQL language. But there is no concept of packages here.

ADS server uses Streamline SQL.

Every RDMS provides for storing serverside procedures and functions on the server and their respective languages. Almost all these languages are similar with slighly different flavours.

Advantages and need for such serverside procedures are a bit beyond the scope of this post.

[Rick Lipkin]

Manuel

Tell me what client you used for your workstations .. I am running on Vista and could not get the Ora9i disks to load properly because of incompatability ..

Rick Lipkin

[nageswaragunupudi]

Fortunately for me our clients are still using XP professional only. But could you make the client work on xp ?

[Manuel Valdenebro]

Tell me what client you used for your workstations .. I am running on Vista and could not get the Ora9i disks to load properly because of incompatability

Rick,

I am using Oracle 10-g (server and clients) successfully, but with XP. I am very confortable with xHarbour + FWH + Oracle.

Regards

[Manuel Valdenebro]

From the client we can execute those functions or procedures through ADO's execute method of connection object and read the return values.

Nages,

I am beginning with Oracle PL/SQL. I am creating several procedures with "Oracle SQL Developer" and run perfectly. But when I try to run from my FWH-aplication, with ADO method EXECUTE, I dont receive any answer.

Can you please, show me an example, how do you executes a PL/SQL procedure in xHarbour/FWH?

For instance, this procedure:

create or replace PROCEDURE S10_1 AS
sFecha Varchar2(40);
BEGIN
select to_char(sysdate,'dd/mm/yyyy hh24:mm:ss')
into sFecha from dual ;
dbms_output.put_line('Hoy es: ' || sFecha);
END S10_1;

Regards y thanks for your help.

[Rick Lipkin]

Rick,

I am using Oracle 10-g (server and clients) successfully, but with XP. I am very confortable with xHarbour + FWH + Oracle.

Regards
_________________
Un saludo

Manuel

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

Rick Lipkin

[nageswaragunupudi]

Mr Manuel

I am giving a test oracle function and xharbour code to use the return value. This function is of no practical use, but for demonstration only.

Create the function on Oracle Server:

Code: Select all

CREATE OR REPLACE FUNCTION sysdateplus (nadd IN NUMBER)
   RETURN DATE
AS
   dret   DATE;
BEGIN
   dret := SYSDATE () + nadd;
   RETURN dret;
END;
/

You can create this function from SQLPlus.
Now here is the xharbour program to use the function

Code: Select all

STATIC oCon
//----------------
FUNCTION Main()

LOCAL dOraRet

	// First connect to your oracle server and store
	// the connection object in oCon static variable

	dOraRet	:= TestOraFunc( 10 )
	msginfo( dOraRet )
	IF VALTYPE( dOraRet ) == 'D'
		msginfo( TTOC( dOraRet ) )
	ENDIF

RETURN NIL
//--------------
STATIC FUNCTION TestOraFunc( nAddDays )

STATIC oCmd

LOCAL oParam, dRetVal
LOCAL oCon

	IF oCmd == NIL
	   
	   // we have oCmd in a static variable
	   // once created can be used through out the life of the program
	   // saves time for creation in subsequent calls
	   
		oCmd	:= TOLEAuto():New( 'ADODB.Command' )
		WITH OBJECT oCmd
			:ActiveConnection	:= oCon				// your connection object
			:CommandText		:= "SYSDATEPLUS"  // function name
			:CommandType		:= adCmdStoredProc // 4
			:Prepared			:= .T.

			// Create Return parameter
			oParam 	:= :CreateParameter( 'RetValue', adDate, adParamReturnValue )  // 7, 4
			:Parameters:Append( oParam )
			// Create input paramter
			oParam	:= :CreateParameter( 'InputNumber', adInteger, adParamInput ) // 3, 1
			:Parameters:Append( oParam )
		END
	ENDIF

	oCmd:Parameters(1):Value	:= nAddDays
        // we can also write oCmd:Parameters('InputNumber'):Value
        // this is more clear but using a number saves a few micro seconds of execution time
	TRY
	   oCmd:Execute()
	CATCH
	   // your stadard fuction to show error
	   RETURN NIL
	END
   dRetVal  := oCmd:Parameters(0):Value


RETURN dRetVal
//--------------------

At first it seems like a lot of code to write. But once we start using oracle or any other rdbms extensively, we naturally write our own library of utilities to create command objects and many others.

[nageswaragunupudi]

Sorry
Please remove the line "LOCAL oCon" from the TestOraFunc
Regret the mistake

[Manuel Valdenebro]

Master Nageswaragunupudi,

Thank you very much. Your example is very good. Thanks again for your knowledges and helpful.

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?

Regards