ADO RecordSet Fetch() ?

[avista]

Hi,
I use ODBC in my programs to connect to database and want to change it and use ADO
Is there a method Fetch() In ADODB.Recordset
I want to execute sql but fetching records manyaly one by one.

AND PLEASE ...
Where i can find manual about all methods and DATA variables for ADODB.Recordset and ADODB.Connection

Best regards,

[Rick Lipkin]

Avista

Here is the Wiki on using ADO and Fivewin that many of our friends have contributed ..

http://wiki.fivetechsoft.com/doku.php?i ... ted_stuffs

To Answer your question .. Fetch() is not a method ( as far as I know ) .. Here are the most common methods and their xBase counter parts ..

Code: Select all  Expand view

Append --> oRecordSet:AddNew()
Close --> oRecordSet:Close()
Commit --> oRecordSet:Update()
Delete --> oRecordSet:Delete()
Deleted() --> oRecordSet:Status == adRecDeleted
EOF() --> oRecordSet:EOF or oRecordSet:AbsolutePosition == -3
Field() --> oRecordSet:Fields( nField - 1 ):Name, :Value, :Type
FCount() --> oRecordSet:Fields:Count
GoTop --> oRecordSet:MoveFirst()
GoBottom --> oRecordSet:MoveLast()
Locate --> oRecordSet:Find( cFor, If( lContinue, 1, 0 ) )
Open --> oRecordSet:Open( cQuery, hConnection )
OrdListClear() --> oRecordSet:Index := ""
RecCount(), LastRec() --> oRecordSet:RecordCount
RecNo() --> oRecordSet:AbsolutePosition
Skip --> oRecordSet:MoveNext()

 

Here is the official Microsoft page on Ado methods..

http://msdn.microsoft.com/en-us/library ... 85%29.aspx

Rick Lipkin

[cnavarro]

thanks
regards

[Armando]

Avista:

An other one

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

Best regards

[Adolfo]

Avista ..
Please send me your Mail.

[avista]

Adolfo,
This is my e-mail

sunrised@t-home.mk

Regards,

[avista]

This is what i am doing usung tOdbc and tDbOdbcd classes

Code: Select all  Expand view

oOdbc := TOdbc():New( sConnectionString )
cSql  := "SELECT * FROM MyTable"
oDbf  := TDbOdbcDirect():New( cSql, oOdbc )

IF lAutoComplete // Fill all records
   oDbf:Complete()
 ELSE
   DO WHILE .t.
      IF oDbf:Fill() // Fill one record
         nRecords := nRecords + 1
         oSayRecords:SetText(ALLTRIM(STR(nRecords)))
       ELSE
         EXIT
      ENDIF

      IF lBreak
         MsgInfo("Break by user")
         EXIT
      ENDIF
   ENDDO
ENDIF
 

HOW TO DO THIS using ADO

Rick: Fetch() is method from tOdbc class which using SqlFetch() fill one records from executed SQL
I need something similar using ADO

Best regards,

[avista]

PLEASE i need some sugestions about this ...

Why i need this
For example if i use sql "SELECT * FROM MyTable" and MyTable have 15.000.000 records
I want to have a view while collecting (importing) data and chance to break it

Best regards,

[Rick Lipkin]

Avista

Why i need this
For example if i use sql "SELECT * FROM MyTable" and MyTable have 15.000.000 records
I want to have a view while collecting (importing) data and chance to break it

You are correct .. it becomes 'expensive' to open a Sql table with "Select * From Table" .. Generally, you open a table looking for a set of records that pertain to a certain criteria such as between two dates

dDate1 := ctod("01/10/2013")
dDate2 := Date()

cSql := "Select * from [Table] where [DateField] >= '"+dtoc(dDate1)+"'"
cSql += " and [DateField] <= "'"+dtoc(dDate2)+"'"

or

If you just want to open a blank recordset knowing you are going to Add records to it you can do it like this:

cSql := "Select * from [Table] where 1 = 2" // will never be true so it opens an empty recordset

Lots of possibilities!
Rick Lipkin

[avista]

Rick, Thanks for reply

in this case
dDate1 >= ctod("01/01/2013")
user can by mistake write "01/01/2003" so all record will be selected that will be going too long and user dont know if program is working or is blocked
becouse of that i need solution liike in my source sample in reply before ... if using odbc using Fetch() and collecting records one by one
second reason is that i want to have a view while collecting and have chance to break it for example

Code: Select all  Expand view

DO WHILE .t.
      IF oDbf:Fill() // Fill one record
         nRecords := nRecords + 1
         oSayRecords:SetText(ALLTRIM(STR(nRecords)))
       ELSE
         EXIT
      ENDIF

      IF lBreak
         MsgInfo("Break by user")
         EXIT
      ENDIF
   ENDDO

IF i use
cSql := "Select * from [Table] where 1 = 2"

How i can adding records one by one
PLEASE some sample if it is possible
(if it is possible probably that is what i need)

Thanks a lot

Regards,

[nageswaragunupudi]

There is no way to read and add records one by one to a recordset in ADO. We open RecordSet once and all records matching the Query are read into the recordset.

If we expect that the Query may result in too many records, then specify oRs:MaxRecords := <yourlimit> and then open the recordset.
If oRs:RecordCount() < oRs:MaxRecords then we know all records are read and if not there are more rows meeting the criteria that are not read. In such a case we may narrow down the where clause or take any other appropriate action.

Example:
oRs := TOleAuto():New( "ADODB.RecordSet" )
oRs:MaxRecords := 1000
<other clauses>
oRs:Open( .... )

if oRs:RecordCount() < oRs:MaxRecords
// oRs contains all records
else
// oRs contains only part and there are more records meeting the criteria
endif

This is equivalent to "SELECT TOP <n> ...." query, but the SQL query syntax is different for different DBMS.

[avista]

Rao Thanks for reply

One more question

Is it possible to define a TIMER and every second read value from
oRs:RecordCount()
before collecting is finished and dysplay it on the screen
and if nned to execute
oRs:Close()
before collecting is finished

If it is possible will be a good tip

Best regards,

[nageswaragunupudi]

Yes, atleast theoritically.

When we open a RecordSet, we can specify adAsyncFetch ( value is 32 ) by or'ing it with nOption parameter.

Then after reading first batch of records ( set by us in oRs:CatcheSize ), rest of the records are read and added to the recordset Asynchronously. We can also examine the events generated to asertain the number of records read so far and if the entire recordset is fetched.

It should be easy to do it with VB, VC# etc.

I said "theoritically", because I tried long ago but could not make it work with Harbour code.

I too am interested to learn if any other friends have done it with (x)Harbour.

Through this posting I request our friends to try and educate us if they succeed.

For further information please refer
http://www.w3schools.com/ado/ado_ref_recordset.asp

[avista]

I have try to define TIMER
But not success
Nothing is working until oRs:Open() finish.
That is big problem becouse user dont know is program working or it is blocked.
Probably adAsyncFetch combined with oRs:CatcheSize can give results but i have no idea what to do.
I have readed many forums about this but found nothing useful.
I think Rick and Rao have most expperiance with ADO and i please too Antonio, Enrico ... and all others to help

Best regards,

[nageswaragunupudi]

We can forget about adAsyncFetch. This does not work with C. This and ADO event handling is possible only with VB etc and not with normal C.

Let us address your issue.
Normally opening a client side recordset should not take unduly long time as long as we judiciously select the size of the the data to be read.

Still if you want to mimic the ODBC fetch() record by record and then copy into your own memory, I suggest the following way:

Open serverside recordset with adOpenForwardOnly and adLockReadOnly. Keep reading the recordset as and when you need and copy to your memory ( array or whatever you were using with ODBC ). This is the fastest cursor possible.

Example:
I assume you have already opened the connection.

Code: Select all  Expand view

oRs   := TOleAuto():New( "ADODB.RecordSet" )

WITH OBJECT oRs
   :Source           := <your sql statement>
   :ActiveConnection := oCn
   :CursorLocation   := 2  // adUseServer
   :LockType         := 1  // adLockReadOnly
   :CursorType       := 0  // adOpenForwardOnly
   //
   :Open()
END
 

Whenever you want to fetch a record, execute code something like this:

if oRs:Eof()
// Already fetched all records. No more records
else
for n := 1 to oRs:Fields:Count()
aRow[ n ] := oRs:Fields( n - 1 ):Value
next n
AAdd( aData, aRow )
oRs:MoveNext()
endif

If you like, I give you here a shortcut code for the above:

Code: Select all  Expand view

if ! oRs:Eof()
   AAdd( aData, oRs:GetRows( 1, 0 )[ 1 ] )
endif
 

With this you can do what you were doing using ODBC fetch() method.

My personal opinion is that you should stop thinking the ODBC way and start thinking the ADO way, same way as we should stop thinking the DBF way and start thinking the RDBMS way when we move to Relational Database systems.