VB6 and large database (memory out)

[campy]

i got a small piece of soft that connects to a huge .gdb database (500000 lines) through the network.
using .movelast will give me memory out. any good advice to access in a fast way the last record?

Dim cn As New ADODB.Connection
sConnStr = "Provider=ZStyle IBOLE Provider;Password=masterkey;User ID=SYSDBA;SQL Dialect=3; Logging Level=0;Silent mode=True;Character Set=WIN1251;Server=192.168.0.78;Data Source =192.168.0.78:/DATABASE/TIME_DBS/TC_" & lblTime.Caption
cn.ConnectionString = sConnStr
cn.Open
rs.Open "Select * from RT_TIME", cn, adOpenDynamic, adLockOptimistic
rs.MoveLast --->>> 'here's when i get "out of memory" error

[debasisdas]

Try using specific column name in the select query instead of using Select *

[monarchmk]

select top 1 * from rt_time order by ID DESC

You can change ID in whatever column which is criteria for last record (eg. date, ordernumber...), and DESC means order from last to first, and top 1 means return only first record (which is last record in database with desc order).This passes job on server not on you software.
Also try put some indexes on lager tables, it will work faster

[monarchmk]

Normaly i did not read whole syntax and wrote SQL for MS databases...
Correct syntax for Firebird is
SELECT FIRST 1 from rt_time ORDER BY <orderfield> DESC

this should solve issue (i hope:) )

[campy]

thanks mate, that was very helpful, is working indeed. I can get the last value easily now, but what if i want to get a value from a row let's say "1 hr ago", as the way i'm using it gives me "Either BOF or EOF is true or the current record has been deleted". Have in mind my database is writing a line every 5 seconds. Here's the code to do that:

rs.Open "Select FIRST 10 * from RT_TIME order by RTTI_DAT_TIME DESC", cn, adOpenDynamic, adLockOptimistic
Do Until i.Caption = 720
i.Caption = Val(i.Caption) + 1
lblTD1.Caption = Format(rs("RTTI_" & txtCD.Text).Value, "0.00")
lblTAP1.Caption = Format(rs("RTTI_" & txtCTAP.Text).Value, "0.00")
rs.MoveNext 'i'm thinking my last line will be the first one now as i'm using DESC option to read the last line - it has to do somehow with that "FIRST 10"?
Loop

[campy]

nevermind, figured out :
Select * from RT_TIME order by RTTI_DAT_TIME DESC - that's the right syntax

thanks all, consider the topic closed

[AndreRet]

Nice to see the effort from your side, thank you.:)

Please mark this as solved, found at the bottom of this page, thanks.

[monarchmk]

Sorry to reopen but solution is wrong... it will again point to out of memory issue...

in select at end you must add WHERE clause.. like this

Select * from RT_TIME WHERE RTTI_DAT_TIME < 'date_time' order by RTTI_DAT_TIME DESC

where 'date_time' is litteral string that depends on your settings and you can see this link for firebird date literals...
http://www.firebirdsql.org/doc/contrib/FirebirdDateLiterals.html
here is a short list od date literals for date 25th of Jun 2004 =

'25.6.2004' '06/25/2004' 'June 25, 2004' '25.jun.2004' '6,25,2004' '25,jun,2004' '25jun2004' '6-25-04' 'Jun 25 04' '25 jun 2004' '2004 June 25' '20040625' '25-jun-2004' '2004-jun-25' '20040625' '25 JUN 04' '2004-06-25' '2004,25,06'

Avoid using * and avoid using select without WHERE, because retrieving a whole DB for only one record is time and resource consuming, with where is faster.

[AndreRet]

Any hair left on your head?;)

[campy]

i'll try that as well, thanks for ur effort monarchmk, i owe u a beer,
cheers

[AndreRet]

Now! beer we can accept, money means nothing. The NEW world payment commodity.;)