Hi,

I want to save user settings from an application to a table.  The application only uses stored procedures - no queries.  As there are around 100 settings, I don't really want to call a stored procedure 100 times from the application via a remote mode connection.  

Does anyone know if there is a way I can pass multiple rows to a stored procedure for inserting?

Thanks,

Steve

Steve,

Slightly off topic.

I would have stored the user settings in a TStringList and saved the TStringList.Text in a Clob of a single row that has the User Id. I would create a class descended from TStringList that would have properties and functions to read and write individual settings. This is much simpler and faster than storing one row per setting.

Barry

Hi Barry,

<< Slightly off topic.

I would have stored the user settings in a TStringList and saved the TStringList.Text in a Clob of a single row that has the User Id. I would create a class descended from TStringList that would have properties and functions to read and write individual settings. This is much simpler and faster than storing one row per setting. >>

Thanks for the suggestion.  I did do something similar, storing XML in a CLOB.  As it's much easier to physically view individual rows when debugging problems, I want to try that out and see what the speed difference is.

-Steve

Steve

I write user settings in 1 record at a time

"SELECT Value FROM ConfigurationSettings WHERE xxxxx"

There are hundreds of settings in my application, but with a well formed index to maximize the speed of returning the data it is pretty fast, definitely less than 1 thousandth of a second per value. Inserting and updating new saved settings is also fast enough.

I played with the idea of "CLOBing" it, but I find it easier to work with multiple rows, especially if I want to globally update a Value for a particular setting. With them in individual rows it is easier to locate the ones which need to be updated.

If you return them all as a dataset it will be larger and therefore take longer to bring back, though not 100 times longer ...

Steve


I don't use Stored Procedures much at all but if you're using a new enough version of ElevateDB have a look at the topic 1.24 Row Value Constructors in the pdf manual

Row value constructors can be used in INSERT statements to insert multiple rows in a single statement
execution. In order to accomplish this, just separate each row value with a comma (,):
INSERT INTO Orders (OrderNo, ItemNo, QtyOrdered, UnitPrice)
VALUES (1200, 23478, 10, 30.00),
(1200, 15453, 4, 23.00),
(1200, 14545, 1, 89.00)
Note
You should be careful not to specify too many row values in a single INSERT statement. It is quite
possible to exceed the parsing and memory limitations of ElevateDB if you specify hundreds of
thousands of row values in a single INSERT statement.

If you're updating rather than inserting you may have to delete rows first then insert.


Roy Lambert

Adam

>There are hundreds of settings in my application, but with a well formed index to maximize the speed of returning the data it is pretty fast, definitely less than 1 thousandth of a second per value. Inserting and updating new saved settings is also fast enough.
>
>I played with the idea of "CLOBing" it, but I find it easier to work with multiple rows, especially if I want to globally update a Value for a particular setting. With them in individual rows it is easier to locate the ones which need to be updated.
>
>If you return them all as a dataset it will be larger and therefore take longer to bring back, though not 100 times longer ...


And if you want them all out together use LIST

Roy Lambert

Adam,

>I write user settings in 1 record at a time

"SELECT Value FROM ConfigurationSettings WHERE xxxxx"

There are hundreds of settings in my application, but with a well formed index to maximize the speed of returning the data it is pretty fast, definitely less than 1 thousandth of a second per value. Inserting and updating new saved settings is also fast enough.<

I'm not saying one method is better than the other, but I want to pick your brain a bit because it may save me some time later on.

I thought of storing application settings this way too, one setting per record.

Then I thought using a single record per user and a Clob column would be more efficient. I would load the Clob into a TStringList for a given UserId. Then I can then use the TStringList to search for a setting using value pairs "settingx=123", or "WindowPosition="1,2,3,4" without any additional network I/O.

The settings get loaded from one record (with a UserId  and Clob column) when the user logs in and he can modify them as much as he likes in the TStringList. The settings get saved when the user logs out. Loading and writing to a TStringList is a snap using the TStringList.Text property. Each setting can have any number of delimited values.

I'd be interested in hearing from you or others as to how they store program settings and user settings in the database. I think it would make for an interesting discussion.

"Roy, you got your ears on?" vbg

Barry

Hi Barry,

<< I'd be interested in hearing from you or others as to how they store program settings and user settings in the database. I think it would make for an interesting discussion.  >>

What I did (before thinking about having a separate row for each setting) is I wrote a class that mimics TRegIniFile - I called it TIniDatabase.  It has read and write methods, eg. ReadString, WriteBool, etc. that match the TRegIniFile ones, including using the same parameters.  

Inside the class, the values are stored as XML.  The settings are read from and written to the database all at once, rather than reading or writing each one individually, via a LoadSettings and SaveSettings method.  I didn't want hundreds of stored procedure calls going across the network when a bunch of users decided to login at the same time.

When I converted my applications from storing settings in the Windows registry to storing them in an ElevateDB database, I didn't have to change the hundreds of lines of code that read and write the settings.  I just replaced the TRegIniFile class with the TIniDatabase and it all worked.

- Steve

Barry

>"Roy, you got your ears on?" vbg

Nope - they've been sent for a service, but my eyballs are still here and operating albeit with mechanical assistance!

I do a mixed approach using a single table (Config) for for individual user modifiable defaults, application wide configurable settings and application wide fixed settings. This table has the following structure

CREATE TABLE "Config"
(
"_ID" VARCHAR(45) COLLATE "ANSI_CI",
"_Type" VARCHAR(1) COLLATE "ANSI_CI",
"_UserSettable" BOOLEAN DEFAULT FALSE,
"_ParamData" CLOB COLLATE "ANSI_CI" COMPRESSION 6,
"_Description" VARCHAR(65) COLLATE "ANSI_CI",
"_Group" VARCHAR(15) COLLATE "ANSI_CI",
"_SubGroup" VARCHAR(15) COLLATE "ANSI_CI",
"_Editor" VARCHAR(15) COLLATE "ANSI_CI",
CONSTRAINT "PK" PRIMARY KEY ("_ID")
)

As you can see the actual setting is held in a CLOB column. In a previous app I had one column for each type. This table is handled via a unit

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
unit ConfigProgs;

interface

uses
Dialogs, Classes, SysUtils, Controls, nlhTable, Variants;

const
cfg_NoticeBoard = 'NoticeBoard';
...
...
cfg_CutANSIEMailAt = 'CutANSIEMailAt';
cfg_UseSurnameForename = 'UseSurnameForename';

function FindOrMakeSpecialCfgKey(const cID: string): boolean;
function FindOrMakeCfgKey(const cID: string): boolean;
function GetCfgVar(const cID: string; const DefaultValue: variant): variant;
function CfgIsNull(const cID: string): boolean;
function GetCfgStr(const cID: string; const Default: string = ''): string;
function GetCfgPath(const cID: string; const Default: string = ''): string;
function GetCfgBool(const cID: string; Unset: boolean = True): boolean;
function GetCfgInt(const cID: string; Unset: integer = 0): integer;
function GetCfgFloat(const cID: string): real;
function GetCfgDate(const cID: string): TDate;
procedure PostCfGValue(const cID, Value: string);
function GetCfgFld1(const cID: string): string;

implementation

uses HHGlobals, nlhNotifier, LookupTables;

function GetCfgStr(const cID: string; const Default: string = ''): string;
begin
Result := HHCommons.Option(cID);
if Result = '' then begin
 if lu.Config.FindKey([cID]) then Result := lu.Config_ParamData.AsString else Result := '';
end;
if Result = '' then Result := Default;
end;

....
....

function GetCfgBool(const cID: string; Unset: boolean = True): boolean;
var
Interim: string;
begin
Interim := HHCommons.Option(cID);
if Interim = '' then begin
 if lu.Config.FindKey([cID]) then Interim := lu.Config_ParamData.AsString else Interim := '';
end;
if Interim <> '' then Result := LowerCase(Interim) = 'yes' else Result := Unset;
end;

end.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

plus there is a maintenance form (which I'll be kind an not post) which uses the info in Config to allow editing

Then in the Staff table (users) I have a CLOB column (_UserOptions) which contains key values pairs for the settings an individual user can override

The process goes
1. check for the user setting
2 if not there check for the application wide setting
3. if that's not there use the default passed
4 if that's not there self destruct!

As always if anyone wants the full code they're welcome to it.

Roy Lambert

Roy,

Thanks for the detailed response.
I'll take a look at it and I'm sure I'll find some useful insights.

I have yet to finalize my approach for saving program and user settings. I've been sitting on the fence for a while, procrastinating which direction to go. I like many others, have initially gone with the detail approach, one setting (or settings group) per record. The TStringList and XML approach also have merit.

The more info I have, the better confidence I have in finalizing on the right choice.

Thanks again for your post.

Barry