On Monday 14 July 2014 10:06:27 Martin Schreiber wrote:
> Instead to use "tmsesqlquery" one could use "tsqlresult" for readonly
> queries which is even faster. :-)
>
See attachment.
Martin
object mainfo: tmainfo
bounds_x = 69
bounds_y = 139
bounds_cx = 441
bounds_cy = 336
container.bounds = (
0
0
441
336
)
font.height = 11
font.name = 'stf_roman'
font.xscale = 1
font.dummy = 0
options = [fo_main, fo_terminateonclose, fo_screencentered, fo_autoreadstat,
fo_autowritestat, fo_savepos, fo_savezorder, fo_savestate]
oncreated = frm_on_created
ondestroy = frm_on_destroy
moduleclassname = 'tmainform'
object grid: twidgetgrid
frame.levelo = 0
frame.framewidth = 1
frame.localprops = [frl_levelo, frl_framewidth]
bounds_x = 0
bounds_y = 0
bounds_cx = 441
bounds_cy = 298
anchors = [an_left, an_top, an_right, an_bottom]
optionsgrid = [og_rowheight, og_colchangeontabkey, og_wrapcol,
og_autopopup, og_mousescrollcol]
optionsgrid1 = [og1_noresetselect]
fixrows.count = 1
fixrows.items = <
item
height = 15
captions.count = 3
captions.items = <
item
caption = #8470' '#1087'/'#1087
end
item
caption = #1048#1089#1087#1086#1083#1085#1080#1090#1077#1083#1100
end
item
caption = #1055#1077#1089#1085#1103
end>
end>
datacols.count = 3
datacols.colorselect = -1610612731
datacols.coloractive = -1610612732
datacols.options = [co_readonly, co_disabled, co_rowselect, co_savevalue,
co_savestate, co_mousescrollrow]
datacols.options1 = [co1_rowfont, co1_rowcolor, co1_zebracolor,
co1_rowcoloractive, co1_rowcolorfocused, co1_rowreadonly, co1_autorowheight]
datacols.items = <
item[tstringedit1]
colorselect = -1610612731
coloractive = -1610612732
width = 74
options = [co_readonly, co_disabled, co_rowselect, co_savevalue,
co_savestate, co_mousescrollrow]
options1 = [co1_rowfont, co1_rowcolor, co1_zebracolor,
co1_rowcoloractive, co1_rowcolorfocused, co1_rowreadonly, co1_autorowheight]
widgetname = 'tstringedit1'
dataclass = tgridmsestringdatalist
end
item[tstringedit2]
colorselect = -1610612731
coloractive = -1610612732
width = 170
options = [co_readonly, co_disabled, co_rowselect, co_savevalue,
co_savestate, co_mousescrollrow]
options1 = [co1_rowfont, co1_rowcolor, co1_zebracolor,
co1_rowcoloractive, co1_rowcolorfocused, co1_rowreadonly, co1_autorowheight]
widgetname = 'tstringedit2'
dataclass = tgridmsestringdatalist
end
item[tstringedit3]
colorselect = -1610612731
coloractive = -1610612732
width = 192
options = [co_readonly, co_disabled, co_rowselect, co_fill,
co_savevalue, co_savestate, co_mousescrollrow]
options1 = [co1_rowfont, co1_rowcolor, co1_zebracolor,
co1_rowcoloractive, co1_rowcolorfocused, co1_rowreadonly, co1_autorowheight]
widgetname = 'tstringedit3'
dataclass = tgridmsestringdatalist
end>
datarowlinewidth = 0
datarowheight = 16
reffontheight = 13
object tstringedit1: tstringedit
optionswidget1 = [ow1_fontglyphheight]
optionsskin = [osk_framebuttononly]
frame.levelo = 0
frame.localprops = [frl_levelo]
frame.dummy = 0
taborder = 1
visible = False
bounds_x = 0
bounds_y = 0
bounds_cx = 74
bounds_cy = 16
optionsedit = [oe_readonly, oe_undoonesc, oe_closequery,
oe_checkmrcancel, oe_shiftreturn, oe_eatreturn, oe_resetselectonexit,
oe_exitoncursor, oe_endonenter, oe_autoselect, oe_autoselectonfirstclick,
oe_focusrectonreadonly, oe_savevalue, oe_savestate, oe_checkvaluepaststatread]
reffontheight = 13
end
object tstringedit2: tstringedit
optionswidget1 = [ow1_fontglyphheight]
optionsskin = [osk_framebuttononly]
frame.levelo = 0
frame.localprops = [frl_levelo]
frame.dummy = 0
taborder = 2
visible = False
bounds_x = 75
bounds_y = 0
bounds_cx = 170
bounds_cy = 16
optionsedit = [oe_readonly, oe_undoonesc, oe_closequery,
oe_checkmrcancel, oe_shiftreturn, oe_eatreturn, oe_resetselectonexit,
oe_exitoncursor, oe_endonenter, oe_autoselect, oe_autoselectonfirstclick,
oe_savevalue, oe_savestate, oe_checkvaluepaststatread]
textflags = [tf_ycentered, tf_wordbreak, tf_noselect]
reffontheight = 13
end
object tstringedit3: tstringedit
optionswidget1 = [ow1_fontglyphheight]
optionsskin = [osk_framebuttononly]
frame.levelo = 0
frame.localprops = [frl_levelo]
frame.dummy = 0
taborder = 3
visible = False
bounds_x = 246
bounds_y = 0
bounds_cx = 192
bounds_cy = 16
optionsedit = [oe_readonly, oe_undoonesc, oe_closequery,
oe_checkmrcancel, oe_shiftreturn, oe_eatreturn, oe_resetselectonexit,
oe_exitoncursor, oe_endonenter, oe_autoselect, oe_autoselectonfirstclick,
oe_savevalue, oe_savestate, oe_checkvaluepaststatread]
textflags = [tf_ycentered, tf_wordbreak, tf_noselect]
reffontheight = 13
end
end
object btn_prev: trichstockglyphbutton
color = -2147483645
frame.framewidth = 1
frame.optionsskin = [fso_flat]
frame.localprops = [frl_framewidth, frl_optionsskin]
frame.dummy = 0
taborder = 1
bounds_x = 4
bounds_y = 303
bounds_cx = 50
bounds_cy = 28
anchors = [an_left, an_bottom]
state = [as_localimagelist, as_localimagenr, as_localonexecute]
glyph = stg_arrowleft
onexecute = on_execute
end
object btn_next: trichstockglyphbutton
Tag = 1
color = -2147483645
frame.framewidth = 1
frame.optionsskin = [fso_flat]
frame.localprops = [frl_framewidth, frl_optionsskin]
frame.dummy = 0
taborder = 2
bounds_x = 61
bounds_y = 303
bounds_cx = 50
bounds_cy = 28
anchors = [an_left, an_bottom]
state = [as_localimagelist, as_localimagenr, as_localonexecute]
glyph = stg_arrowright
onexecute = on_execute
end
object tsqlite3connection1: tsqlite3connection
waitcursor = True
DatabaseName = 'bin/karaoke.s3'
KeepConnection = True
controller.options = [dbo_utf8]
options = [slo_transactions]
busytimeoutms = 500
Transaction = tmsesqltransaction1
left = 32
top = 72
end
object tmsesqltransaction1: tmsesqltransaction
Database = tsqlite3connection1
left = 32
top = 112
end
object sqlresult1: tsqlresult
fielddefs = <>
params = <>
database = tsqlite3connection1
transaction = tmsesqltransaction1
options = [sso_utf8]
left = 32
top = 152
end
end
unit main;
{$ifdef FPC}{$mode objfpc}{$h+}{$endif}
interface
uses
msetypes,mseglob,mseguiglob,mseguiintf,mseapplication,msestat,msemenus,msegui,
msegraphics,msegraphutils,mseevent,mseclasses,msewidgets,mseforms,msedataedits,
mseedit,msegrids,mseificomp,mseificompglob,mseifiglob,msestrings,msewidgetgrid,
msesimplewidgets,msedatabase,msesqlite3conn,sysutils,mdb,msebufdataset,msedb,
msesqldb, msesqlresult, songs;
type
TDirQuery = (dqPrev, dqCurr, dqNext);
type
tmainfo = class(tmainform)
grid: twidgetgrid;
tstringedit1: tstringedit;
tstringedit2: tstringedit;
tstringedit3: tstringedit;
btn_prev: trichstockglyphbutton;
btn_next: trichstockglyphbutton;
tsqlite3connection1: tsqlite3connection;
tmsesqltransaction1: tmsesqltransaction;
sqlresult1: tsqlresult;
procedure frm_on_destroy(const sender: TObject);
procedure frm_on_created(const sender: TObject);
procedure on_execute(const sender: TObject);
private
{ private declarations }
FSongs: TSongs;
FAllRecords: Integer;
procedure GetCurrentPage(Dir: TDirQuery);
end;
var
mainfo: tmainfo;
implementation
uses
main_mfm;
Const
cPageSize = 100;
procedure tmainfo.frm_on_destroy(const sender: TObject);
begin
FSongs.Free;
end;
procedure tmainfo.GetCurrentPage(Dir: TDirQuery);
var
i: Integer;
f0,f1,f2: tdbcol;
begin
//В зависимости от параметра процедуры определяем с какого номера записи проводить выборку
Case Dir of
dqPrev: If sqlresult1.Params[0].AsInteger>=cPageSize Then
sqlresult1.Params[0].AsInteger:=sqlresult1.Params[0].AsInteger-cPageSize;
dqNext: If sqlresult1.Params[0].AsInteger<=FAllRecords-cPageSize Then
sqlresult1.Params[0].AsInteger:=sqlresult1.Params[0].AsInteger+cPageSize;
End;
sqlresult1.refresh();
if sqlresult1.Params[0].AsInteger > FAllRecords - cPageSize then
btn_next.enabled := False
else
btn_next.enabled := True;
if sqlresult1.Params[0].AsInteger < cPageSize + 1 then
btn_prev.enabled := False
else
btn_prev.enabled := True;
//Заполняем коллекцию
f0:= sqlresult1.cols[0];
f1:= sqlresult1.cols[1];
f2:= sqlresult1.cols[2];
FSongs.Clear;
while not sqlresult1.eof do begin
With FSongs.Add Do
Begin
Id := f0.asmsestring;
Solist := f1.asmsestring;
SongName := f2.asmsestring;
End;
sqlresult1.next();
end;
//Заполняем полученой порцией данных компонент отображения
grid.beginupdate;
if grid.rowcount > 0 then
grid.clear;
With grid do begin
for i:=0 To FSongs.Count-1 do begin
grid.rowcount := i + 1;
tstringedit1[i] :=FSongs[i].Id;
tstringedit2[i] := FSongs[i].Solist;
tstringedit3[i] := FSongs[i].SongName;
end;
end;
grid.endupdate;
end;
procedure tmainfo.frm_on_created(const sender: TObject);
begin
//Создаем коллекцию
FSongs:=TSongs.Create(TSong);
tsqlite3connection1.Connected := True;
//Определяем общее количество записей
FAllRecords:= getsqlresultvar(tmsesqltransaction1,'SELECT COUNT(*) FROM rus',[]);
//Основной запрос
sqlresult1.SQL.Clear;
sqlresult1.SQL.Add('SELECT rus.id, sol.Aname, rus.name');
sqlresult1.SQL.Add('FROM rus INNER JOIN sol');
sqlresult1.SQL.Add('ON rus.sol_id=sol.id');
sqlresult1.SQL.Add('ORDER BY sol.Aname, rus.name');
sqlresult1.SQL.Add('LIMIT '+IntToStr(cPageSize)+' OFFSET :n');
sqlresult1.Params[0].AsInteger:=0;
GetCurrentPage(dqCurr);
end;
procedure tmainfo.on_execute(const sender: TObject);
begin
case (sender as trichstockglyphbutton).Tag of
0 : GetCurrentPage(dqPrev);
1 : GetCurrentPage(dqNext);
end;
end;
end.
------------------------------------------------------------------------------
Want fast and easy access to all the code in your enterprise? Index and
search up to 200,000 lines of code with a free copy of Black Duck®
Code Sight™ - the same software that powers the world's largest code
search on Ohloh, the Black Duck Open Hub! Try it now.
http://p.sf.net/sfu/bds
_______________________________________________
mseide-msegui-talk mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/mseide-msegui-talk
