My final addition on this (promise)

I always feel that doing extra work to bundle fields into XML and then unbundle them while it feels "neat" and ends up with fewer DB rows and fewer transactions has too many downsides,

i.e.
* your storage gets clogged up with the XML wrappings,
* it becomes much harder to to locate saved settings (perhaps to reset them) once they are bundled, sometimes it actually becomes impossible.
* Sometimes you end up having to retrieve long lists of XML just to change / upload a few of the fields.
* To update just 1 field you have to rewrite the whole lot.
* EDB is damn fast anyway ...

If you are always reading the whole set of config data and writing the whole set and if your component already has a nice XML-based storage system built in, then I use XML (i.e. Dev Ex components, TChart). Otherwise I tend to do row-by-value.

In all these situations Roy's point about posting multiple rows with parenthasized-INSERT statements is useful.

Adam / Barry

BEGIN RANT:


XML is (a bit like SQL) touted as the answer to everything. If you have some data that is only ever going to be used in your application, and it has a simple format (eg key value pair) then why carry the overhead of XML, other times it just shouldn't be the answer.

I've just read a post over in the embarcadero newsgroups where someone was asking advice as to how to format his XML and would it cope with this data (it was sort of books & comments stuff) and he didn't want to use a database. So one consequence is every time he opens his app he has to load and parse the entire database! Very clever.

XML is very useful for some things. I've moved from using csv to xml for my ElevateDB export/import and found it a bit faster to my surprise (and its easier to hack .

Generally I much prefer something like

FullScreenOnStartup=Yes

to

<Setting>
<Key>
FullScreenOnStartUp
</Key>
<Value>
Yes
</Value>
</Setting>


Or as I have in one place

YYNY

to

<PopulatePageAtStartup>
<Page>
LTC
</Page>
<Visible>
Yes
</Visible>
</Page>
</PopulatePageAtStartup>
<PopulatePageAtStartup>
<Page>
Project
</Page>
<Visible>
Yes
</Visible>
</Page>
</PopulatePageAtStartup>
<PopulatePageAtStartup>
<Page>
Companies
</Page>
<Visible>
No
</Visible>
</Page>
</PopulatePageAtStartup>
<PopulatePageAtStartup>
<Page>
Contacts
</Page>
<Visible>
Yes
</Visible>
</Page>
<PopulatePageAtStartup>



END RANT

Roy Lambert

Hi Roy,

<< 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 >>

Sorry, I missed this earlier post.  Thanks, I'll check it out.

- Steve

FWIW, I wrote a TIniFile compatible class that uses a DBISAM database
table. Worked really well. IIRC I could add an extra "user" field too,
but it made the code really easy. It solved a problem with moving a
system that used to use TIniFile to a shared database.

--

Matthew Jones

Hi Adam,

<< I always feel that doing extra work to bundle fields into XML and then unbundle them while it feels "neat" and ends up with fewer DB rows and fewer transactions has too many downsides, .....

* it becomes much harder to to locate saved settings (perhaps to reset them) once they are bundled, sometimes it actually becomes impossible. .... >>

I agree.  I have stored procedures which need to read some settings and it's proving difficult when the settings are stored in a CLOB as XML.

- Steve

Hi Roy,

<<
INSERT INTO Orders (OrderNo, ItemNo, QtyOrdered, UnitPrice)
VALUES (1200, 23478, 10, 30.00),
(1200, 15453, 4, 23.00),
(1200, 14545, 1, 89.00)

>>

This is a nice feature and I can see how I could build the SQL in code and run it as a query.  However, I can't see how I could do this in a stored procedure and pass in the values for multiple rows using parameters.  Am I missing something?

- Steve

Steve


>This is a nice feature and I can see how I could build the SQL in code and run it as a query. However, I can't see how I could do this in a stored procedure and pass in the values for multiple rows using parameters. Am I missing something?

Maybe I'm missing something - how do you want to pass the multiple rows? Can you give me an example of what you think it should look like?

Roy Lambert

Hi Roy,

<< Maybe I'm missing something - how do you want to pass the multiple rows? Can you give me an example of what you think it should look like? >>

Well, that's the thing.  I don't know what it would look like.  Here's an example of a stored procedure that inserts one row using parameters:

CREATE PROCEDURE "AddHistory" (IN "PReferenceID" INTEGER, IN "PUserID" INTEGER, IN "PDetails" VARCHAR(100) COLLATE "UNI")
BEGIN    
  DECLARE Result CURSOR FOR SQLStatement;     
  DECLARE HistoryDateTime TIMESTAMP;

  SET HistoryDateTime = CURRENT_TIMESTAMP;

  PREPARE SQLStatement FROM
     'INSERT INTO History
      (HistoryDateTime, ReferenceID, UserID, Details)
      VALUES (?, ?, ?, ?)';

  EXECUTE SQLStatement USING HistoryDateTime, PReferenceID, PUserID, PDetails;
END

What I would like to do is to be able to insert multiple rows.  But I don't think this is possible based on what I have seen so far.

- Steve

Steve

><< Maybe I'm missing something - how do you want to pass the multiple rows? Can you give me an example of what you think it should look like? >>
>
>Well, that's the thing. I don't know what it would look like. Here's an example of a stored procedure that inserts one row using parameters:

That's fine, and could be used as a sort of pattern.

>What I would like to do is to be able to insert multiple rows. But I don't think this is possible based on what I have seen so far.

If you want to wave a magic wand then no, if you're prepared to do a bit (possibly a lot) of work then you have all the tools you need.

At some point you have to marshal the parameters to get them into the SP. Given that I'm not sure why you reject the multi-row insert mechanism Tim has built in. The only reason I can see is a desire to use parameters rather than build teh SQL for efficiency reasons.

If you think in terms of how you would proceed using Delphi (assuming that's what you're using) you would either use array types as the parameters, or specify a number of parameters to the maximum you could ever need then cut short at the first one that was null. You can do roughly the same in the SP. I discovered the nice little feature that if you don't supply a parameter its treated as null so you could do that.

Alternatively you could pass all of the parameters as strings (remember a varchar can be enormous) and use string slicing / casting to get what you want. Since you have to marshal the parameters you could build the value list for each row (eg 12,67,87,'fred') delimit each set with # (or some other symbol) and in the SP loop round getting each new value chunk, build and execute the statement for that row. Not as efficient as just being able to sling a load of data in and have it shoved into the right place but still doable.

Roy Lambert

Hi Roy,

<< If you want to wave a magic wand then no, if you're prepared to do a bit (possibly a lot) of work then you have all the tools you need. >>

All I really wanted to know is if it was possible to pass in an array of values for each parameter.  It seems the answer is 'no'.  I have already done a lot of work - I'm just trying to simplify existing code and make it more efficient.

<< Given that I'm not sure why you reject the multi-row insert mechanism Tim has built in.>

I haven't rejected anything. The multi-row insert mechanism, as far as I can tell, only works with hard-coded values, not parameters.  If at all possible, I want to avoid building the SQL, as well as executing queries, from the client app.

<< The only reason I can see is a desire to use parameters rather than build teh SQL for efficiency reasons. >>

It's for efficiency and performance reasons.  I don't use ElevateDB in Local Mode, only Remote Mode, so by using stored procedures (with parameters) the server (not the client) does all of the grunt work.  The client app just passes the data to the server and lets the server worry about what to do with it.  A lot of the business rules are centralised on the server. This works well, especially where there are a number of steps to perform.  Instead of the client app executing a number of queries in sequence (like it did when the app used a DBISAM database), it calls one stored procedure and then is free to do other stuff.  This stored procedure then calls other stored procedures, as required, to perform a number of tasks.  

Believe it or not, I have worked on some very large systems with thousands of users (albeit using MS SQL Server and Oracle) and this is the model they have all used (the client apps had no SQL whatsoever in them - it was all on the server).

<< If you think in terms of how you would proceed using Delphi (assuming that's what you're using) you would either use array types as the parameters, or specify a number of parameters to the maximum you could ever need then cut short at the first one that was null. You can do roughly the same in the SP. I discovered the nice little feature that if you don't supply a parameter its treated as null so you could do that.  >>

It doesn't appear that stored procedures can accept array parameters.

<< Alternatively you could pass all of the parameters as strings (remember a varchar can be enormous) and use string slicing / casting to get what you want. Since you have to marshal the parameters you could build the value list for each row (eg 12,67,87,'fred') delimit each set with # (or some other symbol) and in the SP loop round getting each new value chunk, build and execute the statement for that row. Not as efficient as just being able to sling a load of data in and have it shoved into the right place but still doable. >>

Yes, I have done that with some stored procedures.  It works but it's kind of clunky.  I was looking for a simpler and more efficient way of doing it.  I strive to make code clean, simple and easy-to-maintain.  Unfortunately it's not always possible.

Thank you for your suggestions.

- Steve