New AdoRDD (free)

[Antonio Linares]

Rick,

Then, this should work:

HB_AdoRddGetRecordSet():Index = "userid"

which it is the equivalent for:

SET INDEX TO userid

[Rick Lipkin]

Antonio

I inserted the index code and got a run-time error :


TRY

SELECT 1
USE (AllTrim(cDbf)) VIA "ADORDD" TABLE AllTrim(cTable) SQL ;
FROM AllTrim(cFrom) USER AllTrim(cUser) PASSWORD AllTrim(cPsw)

* SET INDEX to USERID

CATCH oERR
MsgInfo( "Error in Opening USERINFO table" )
oDlg:End()
RETURN(.F.)
END TRY

*MsgInfo( HB_AdoRddGetCatalog():Tables( cTable ):Indexes:Count )
*MsgInfo( HB_AdoRddGetCatalog():Tables( cTable ):Indexes( 0 ):Name )

HB_AdoRddGetRecordSet():Index = "userid"

browse()





//------------------
Application
===========
Path and name: O:\Ricks programming Backup\FOX\DRUGSQL-adordd\DcSql32.Exe (32 bits)
Size: 1,480,192 bytes
Time from start: 0 hours 0 mins 0 secs
Error occurred at: 05/13/2007, 18:04:32
Error description: Error ADODB.Recordset/16389 E_FAIL: _INDEX
Args:
[ 1] = C userid

Stack Calls
===========
Called from: win32ole.prg => TOLEAUTO:_INDEX(0)
Called from: LOGIN.PRG => _LOGIN(45)
Called from: main.prg => (b)MAIN(204)
Called from: DIALOG.PRG => (b)TDIALOG:TDIALOG(0)
Called from: DIALOG.PRG => TDIALOG:DISPLAY(0)
Called from: DIALOG.PRG => TDIALOG:HANDLEEVENT(0)
Called from: => DIALOGBOXINDIRECT(0)
Called from: DIALOG.PRG => TDIALOG:ACTIVATE(0)
Called from: main.prg => MAIN(206)

System
======
CPU type: Intel Pentium III 733 Mhz
Hardware memory: 512 megs

Free System resources: 90 %
GDI resources: 90 %
User resources: 90 %

Compiler version: xHarbour build 0.99.70 Intl. (SimpLex)
Windows version: 5.0, Build 2195 Service Pack 4

Windows total applications running: 18
1
2 Fax Monitor
3 SysFader
4 NetDDE Agent
5 Logging In ...... Please be patient
6 DRUGSQL-adordd
7 ICA Administrator Toolbar
8 ccApp
9 NetWareProviderIcons
10 Power Meter
11 Connections Tray
12 MS_WebcheckMonitor
13 DDE Server Window
14 WFShell
15 ICA Seamless Host Agent
16 ICA Control Channel Support
17 WFShellWindow
18 Program Manager

Variables in use
================
Procedure Type Value
==========================
TOLEAUTO:_INDEX
Param 1: C "userid"
Local 1: U
Local 2: N 0
_LOGIN
Param 1: C "Y"
Param 2: O Class: TDIALOG
Local 1: U
Local 2: U
Local 3: L .F.
Local 4: C "drugcontroluser"
Local 5: C "WEBDB02"
Local 6: C "r2x6j3q4"
Local 7: C "DRUGCONTROLtest"
Local 8: C "USERINFO"
Local 9: U
Local 10: U
(b)MAIN
Param 1: U
(b)TDIALOG:TDIALOG
Param 1: O Class: TDIALOG
TDIALOG:DISPLAY
TDIALOG:HANDLEEVENT
Param 1: N 15
Param 2: N 0
Param 3: N 0
Local 1: O Class: TDIALOG
DIALOGBOXINDIRECT
Param 1: N 4194304
Param 2: C " Ā %
' L o g g i n g I n . . . . . . P l e a s e b e p a t i e n t P N \ f S T A T I C V a l i d a t i n g U s e r "
Param 3: N 0
Param 4: O Class: TDIALOG
TDIALOG:ACTIVATE
Param 1: U
Param 2: U
Param 3: U
Param 4: L .T.
Param 5: U
Param 6: L .T.
Param 7: U
Param 8: U
Param 9: U
Param 10: L .F.
Local 1: O Class: TDIALOG
Local 2: N 0
Local 3: U
Local 4: U
Local 5: U
Local 6: O Class: TDIALOG
MAIN
Local 1: C "O:\Ricks programming Backup\FOX\DRUGSQL-adordd"
Local 2: U
Local 3: C "O:\Ricks programming Backup\FOX\DRUGSQL-adordd\DcSql32.Exe"
Local 4: U
Local 5: U
Local 6: N 47
Local 7: U
Local 8: U
Local 9: U
Local 10: U
Local 11: U
Local 12: U
Local 13: U
Local 14: U
Local 15: D 05/13/2007
Local 16: N 1977
Local 17: N 1024
Local 18: N 768
Local 19: U
Local 20: U
Local 21: C "Y"
Local 22: C "Y"
Local 23: O Class: TDIALOG
Local 24: L .F.

Linked RDDs
===========
DBF
DBFFPT
DBFNTX
DBFBLOB
ADORDD

DataBases in use
================

1: => DRUGCONTROLTEST RddName: ADORDD
==============================
RecNo RecCount BOF EOF
1 21 .F. .F.

Indexes in use TagName

Relations in use

Classes in use:
===============
1 HBCLASS
2 HBOBJECT
3 TWINDOW
4 TDIALOG
5 TBRUSH
6 TCONTROL
7 TSAY
8 TFONT
9 TOLEAUTO
10 ERROR

Memory Analysis
===============
148 Static variables

Dynamic memory consume:
Actual Value: 0 bytes
Highest Value: 0 bytes

[Antonio Linares]

Rick,

http://www.w3schools.com/ado/prop_rs_index.asp

"This property is used together with the Seek method to create an indexed display of records and to use the Seek method to search these indexed records, but few providers supports this property and method. Use the Supports method to determine whether the provider supports seek and indexes."

Please try this:

#define adIndex 0x100000

MsgInfo( HB_AdoRddGetRecordset():Supports( adIndex ) )

[Rick Lipkin]

Antonio

Returned .f. ... ... kinda thought that. From everything I have read .. MS SQL server uses the indexes in the execution plan if it is a faster way to get at the data .. but not truly to be used or manipulated by a program. In Oracle .. there is a 'hint' clause that tells the query to use the index ..

This is not a 'deal buster' for me .. perhaps you can add an optional <order by> to be used by the SQL query like .. "Select * from userinfo order by userid" .. will need some way to tap into the oRs:Find() method from your code.... here is what I was doing in ADO .. this is the "find" program .. where the cert table is ordered by reg_no .. so that is the sort of the table :

//---------------------------------
DO CASE
CASE cITEM = "Reg Number "

cFIND := ALLTRIM(SUBSTR(cFIND,1,2)+SUBSTR(cFIND,4,5))
oRs:Find("reg_no = '"+cFIND+"'" )

IF oRs:eof
oRs:MoveFirst()

xFIND := "reg_no like '"+cFIND+"%'"
oRs:Find( xFIND )

IF oRs:eof()
Msginfo( "Reg Number "+cFind+" can not be found" )
oRs:MoveFirst()
ENDIF
ENDIF

RETURN(.T.)

CASE cITEM = "Name "

oRs:Filter := "name like '"+cFIND+"%'"

IF oRs:eof
oRs:MoveFirst()

xFIND := "name like '"+cFIND+"%'"
oRs:Find( xFIND )

IF oRs:eof()
Msginfo( "Name "+cFind+" can not be found" )
oRs:MoveFirst()
ENDIF
ENDIF

RETURN(.T.)
ENDCASE


//---------

Since I would also have to locate on a name field .. there was no way to re-order the table unless closed and re-creaded the recordset ordered by name .. to at least make the program usable .. I used the 'filter' method to give the user the opportunity to locate by a name out of the origional recordset .. and have a 'clear filter' option to bring back all the records again ..

A bit clumsy but it works ..

Was hoping there was a 'magic wand' you could wave to use ADO see and manipulate the MS SQL indexes .. it appears ADO can see the index and know it by name .. just not use it to find records or order or re-order a recordset.. as in SET INDEX to ..

Looks like the best thing you can do is add an additional <order by> option for those databases that do not support indexing... and let our developers know that is just a limitation they will have to deal with ..

Just my thoughts.

Rick Lipkin

[Rick Lipkin]

Antonio

I just sent a message to our MS SQL DBA and asked him to review your link ( sent him the last two messages from this thread ) and asked him if the ( interactive ) use of indexes on our database is default or a configurable parameter .. We are using a SQL 2000 database .. also asked the DBA if SQL 2005 would give us the flexability to 'actively' use indexing.

Rick Lipkin

[Enrico Maria Giordano]

Enrico,

How do you use a trusted connection with standard ADO ?

With the connection string I already reported:

Code: Select all

oRs:Open( "SELECT * FROM Contatti", "Provider=SQLOLEDB;Integrated Security=SSPI;Data Source=EMAG\Emag;Initial Catalog=Ecmp", adOpenForwardOnly, adLockOptimistic )

EMG

[Enrico Maria Giordano]

Enrico

//---
oRs:Open( "SELECT * FROM Contatti", "Provider=SQLOLEDB;Integrated Security=SSPI;Data Source=EMAG\Emag;Initial Catalog=Ecmp", adOpenForwardOnly, adLockOptimistic )
//---

Source is the IP or DNS name of the server

cFROM := "EMAG\Emag" // not a servername .. looks like a folder

No, it is a server name. Please note that the above connection string works just fine with ADO.

( Security=SSPI .. need a user and password )

No, trusted connection doesn't need user and password.

EMG

[Antonio Linares]

Rick,

Yes, we also considered the use of ORDER BY if the Index property is not supported. We could issue an oAdoConnection:Execute() and get a new recorset, and replace the previous used one.

Anyhow, lets see what your DBA says. ADO should provide all that functionality. Probably SQL 2005 is the way to go

[Antonio Linares]

Enrico,

Then you should modify adordd.prg for your needs and use the right connection string for you

[Enrico Maria Giordano]

Ok, no problem. I was only doing some tests to try ADORDD.


EMG

[Antonio Linares]

Enrico,

We are sure that your tests with ADORDD will greatly improve it, as you have a great ADO experience and surely you can contribute to enhance it

[Rick Lipkin]

Antonio

I loaded up a MS SQL 2005 box and I played around with the same tables as in my 2000 relm. A couple of noteworthy things ..

1) There are no ( structural ) changes in indexing from 2000 to 2005
2) If you set the indexing to Unique and clustered .. the table opens in index order without issueing an 'order by' clause ( both in 2000 and 2005 ) .. however

#define adIndex 0x100000
MsgInfo( HB_AdoRddGetRecordset():Supports( adIndex ) )

still returns false in 2005

Just thought I would send you the feedback.

Rick Lipkin

[Antonio Linares]

Rick,

Thanks for the feedback. We may need to issue an oAdoConnection:Execute() using a ORDER BY statement and replace the resulting recorset into the ADORDD workarea

[Rick Lipkin]

Antonio

Don't forget .. since you can not 'seek' on an index .. you will need some way of #translating the "locate" or "seek" command to something like :

cSQL := "reg_no like '"+cFIND+"%'"

oRs:Find( cSQL ) ..

This seems to work universally with all SQL engines and ADO .. If you decide to go down this path .. RDD EOF() and not found() will need to be #translated to oRs:eof

Set scope can possibly be #translated like : oRs:Filter := "name like '"+cFIND+"%'" .. and set scope to oRs:Filter := "" to clear

Also .. you may have to re-consider a 're-fetch' for a recordset if the user needs to re-query on a different "order by"

Rick Lipkin

[Milan Mehta]

Dear All,

I am quite excited about the possibility of Using Access and MySql in my program. I have always resisted using the same as I needed to alter my programming style and it also required some other program to be installed on client machine. Hence now I can comfortable go ahead with using Access in my program.

I will highly appreciate, if somebody can show me, how can I ?
- Create a Table (in a Database)
- Open the Table
- Index the Table
- Seek a particularly Value
- Set the filter

Any sample for the same will be highly appreciated.

TIA

Milan.