Page 1 of 2
ADO RecordSet Fetch() ?
Posted: Tue Jul 23, 2013 12:54 pm
by 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,
Re: ADO RecordSet Fetch() ?
Posted: Tue Jul 23, 2013 1:11 pm
by 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_stuffsTo 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
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.aspxRick Lipkin
Re: ADO RecordSet Fetch() ?
Posted: Tue Jul 23, 2013 1:37 pm
by cnavarro
thanks
regards
Re: ADO RecordSet Fetch() ?
Posted: Tue Jul 23, 2013 3:42 pm
by Armando
Avista:
An other one
http://www.w3schools.com/ado/Best regards
Re: ADO RecordSet Fetch() ?
Posted: Wed Jul 24, 2013 12:10 am
by Adolfo
Avista ..
Please send me your Mail.
Re: ADO RecordSet Fetch() ?
Posted: Wed Jul 24, 2013 6:58 am
by avista
Adolfo,
This is my e-mail
sunrised@t-home.mkRegards,
Re: ADO RecordSet Fetch() ?
Posted: Wed Jul 24, 2013 7:12 am
by avista
This is what i am doing usung tOdbc and tDbOdbcd classes
Code: Select all | Expand
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,
Re: ADO RecordSet Fetch() ?
Posted: Thu Jul 25, 2013 9:17 pm
by 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,
Re: ADO RecordSet Fetch() ?
Posted: Thu Jul 25, 2013 9:47 pm
by 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
Re: ADO RecordSet Fetch() ?
Posted: Thu Jul 25, 2013 11:44 pm
by 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
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,
Re: ADO RecordSet Fetch() ?
Posted: Fri Jul 26, 2013 3:21 am
by 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.
Re: ADO RecordSet Fetch() ?
Posted: Fri Jul 26, 2013 7:08 am
by 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,
Re: ADO RecordSet Fetch() ?
Posted: Fri Jul 26, 2013 9:48 am
by 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
Re: ADO RecordSet Fetch() ?
Posted: Sun Jul 28, 2013 4:32 pm
by 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,
Re: ADO RecordSet Fetch() ?
Posted: Sun Jul 28, 2013 8:53 pm
by 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
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
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.