Has anyone managed to get EWB to work properly with SQLiteODBC?  After much effort I have managed to view a simple SQLite table in an EWB app, both in the IDE server, and in EWBSRVR using a browser, but there is no sign that EWB is going to be able to update the table.  Procmon shows the file is never written to, though Commit shows no error.  

We need to be able to create web applications to view AND update about a dozen simple tables, for installation at (unskilled-) customer sites.  So installing MS-SQL and similar major databases is a non-starter.  We cannot use  DBISAM and ElevateDB because a few of our customers will want to access the data themselves, so a proprietary database is no use.  SQLite, or even plain XML files, would be ideal.

After a brief evaluation and some recommendations I had high hopes that EWB was going to be the answer for rapid development of this type of application.  But though the UI is easy, finding a suitable back end is a struggle. All the examples I have found are fine for viewing data, but Elevate seem to assume that everyone who wants to do more than viewing the data is going to buy their database products, because there are NO examples of updating databases or inserting records with any other back end. The DataSetClient and even the CDCollector examples are both read-only.

If nobody has any suggestions for getting SQLite working with EWB, are there any suggestions for a mechanism to update really simple data files, as well as viewing the data, from an EWB application?  I don't really want to re-invent the wheel and twiddle the JSON strings myself:  I want something that is as simple and quick to implement as the EWB front-end.

If you can run EWB Web Server then look into the custom dataset module
capability as it lets you handle the request with less coding than the
next option.

I know you want it out of the box but EWB has a very straightforward
JSON dataset model so you would need to write something more custom to
handle the JSON yourself:
http://www.elevatesoft.com/manual?action=viewtopic&id=ewb1&topic=JSON_Reference

The reason this does not exist in the box is simply time - there are
always more features requested than time to implement them.

If the tables are really very simple then you can always also implement
a custom server function that just handles basic URL params or post data
to do the update. Would require some coding but might be lot less than
for JSON side.

Finally i would reconsider the use of ElevateDB - it's proprietary only
in terms of storage format (and you can buy source code license if
you're really concerned over that) and is royalty free so only you would
need to purchase a license, not your customers.

Raul






On 8/4/2013 2:47 PM, Tim Frost wrote:
> If nobody has any suggestions for getting SQLite working with EWB, are there any suggestions for a mechanism to update really simple data files, as well as viewing the data, from an EWB application?  I don't really want to re-invent the wheel and twiddle the JSON strings myself:  I want something that is as simple and quick to implement as the EWB front-end.

Hmm, if you want an easy database, why not MySQL? There are REST interfaces
available for that which you could deploy on your server. That said, a quick Google
shows that there is a REST interface for SqlLite too.

I suggest that rather than using the default "easy" database facilities, you are
better off using a standard REST interface so that you can be as flexible as you
wish in future. It isn't hard.

/Matthew Jones/

(Matthew Jones) wrote:
Hmm, if you want an easy database, why not MySQL?

Because the intended application is going to monitor and control just a small part of a complex application: and MySQL involves a download twice the size of our own and a complex installation.  Thanks for the suggestion (I had forgotten about MySQL) but it's way overkill.

And the the reason we cannot use ElevateDB, as Raul suggested (thanks also), is that it does not allow a user to access the data from their own applications without buying their own development licence.

I suppoese the real problem is that I want to use EWB without a 'proper' database, and without using a database there is no simple way to do this.  I understand why there are no examples of either working directly with the JSON or writing a 'module' that does not use a Tdataset: I am not frightened of undertaking either task; it's just that I don't have the time to start from scratch and experiment until I get it working.

Getting SQLite working in update mode would at least get me up and running quickly, and give me time to explore other solutions; and installing a couple of small DLLs is a no-brainer.  So I would still like to know if anyone has done that.

If you look into the EWB Modules then there is a sample project there
for XML based database. It's based on delphi biolife sample table but
should be fairly easy to modify for any other XML table(s).

The resulting dll can be plugged into the EWB web server and already has
support or both read and edit (for posting back changes). The components
supplied take care of all json parsing so code underneath is quite
straightforward.

i think this might be the quickest way to get basic XML back-end
database going for EWB.

Raul



On 8/5/2013 9:01 AM, Tim Frost wrote:
> I suppoese the real problem is that I want to use EWB without a 'proper' database, and without using a database there is no simple way to do this.  I understand why there are no examples of either working directly with the JSON or writing a 'module' that does not use a Tdataset: I am not frightened of undertaking either task; it's just that I don't have the time to start from scratch and experiment until I get it working.

Following on from Raul's comment re an XML sample, this might give you
something more sophisticated
http://www.cromis.net/blog/downloads/simplestorage/

Cheers, Bob


On 05/08/2013 14:01, Tim Frost wrote:
> (Matthew Jones) wrote:
> Hmm, if you want an easy database, why not MySQL?
>
> Because the intended application is going to monitor and control just a small part of a complex application: and MySQL involves a download twice the size of our own and a complex installation.  Thanks for the suggestion (I had forgotten about MySQL) but it's way overkill.
>
> And the the reason we cannot use ElevateDB, as Raul suggested (thanks also), is that it does not allow a user to access the data from their own applications without buying their own development licence.
>
> I suppoese the real problem is that I want to use EWB without a 'proper' database, and without using a database there is no simple way to do this.  I understand why there are no examples of either working directly with the JSON or writing a 'module' that does not use a Tdataset: I am not frightened of undertaking either task; it's just that I don't have the time to start from scratch and experiment until I get it working.
>
> Getting SQLite working in update mode would at least get me up and running quickly, and give me time to explore other solutions; and installing a couple of small DLLs is a no-brainer.  So I would still like to know if anyone has done that.
>

If you look into the EWB Modules then there is a sample project there
for XML based database. It's based on delphi biolife sample table but
should be fairly easy to modify for any other XML table(s).

That's exactly where I started, and it is indeed very easy to modify, and should have produced exactly what I hoped for.  When I first evaluated EWB I looked at this and assumed it would do precisely what I needed.  I sketched out the design of the XML files, mocked up my first EWB screens, and was ready to go.  Last week I started working with the module code, and the biolife database, but nothing I could do would cause the XML file to be updated on commit.  I even tried dropping in a biolife.SavetoFile in various places, which merely produced unchanged copies of the original XML.

After spending most of a day on this, I finally found a thread here (search for 'datasetmodule' and it is the only thread found) in which Tim says to someone who had the same issue, "That's on purpose - it's using a TClientDataSet, so it doesn't actually update anything in the source .XML file."  Finding that this solution was a chocolate teapot is what led me to look for SQLite and other possible ideas.  Although I have used Delphi very extensively since D1, I have never needed to use a TClientDataSet, so I assumed Tim's statement was obvious to everyone except me, and abandonded this approach.   I would love it to be proved wrong so that I could go back to using simple XML files.  I will definitely take a look at OmniXML and Simple Storage also, but if the datasetmodule truly cannot update anything through a TClientDataSet, I am not sure if that will help.

Sorry for the probable misdirection caused by my earlier messages.  New question: can the datasetmodule example be modified somehow to save committed changes to an XML file?

On 8/6/2013 5:46 AM, Tim Frost wrote:
> That's exactly where I started, and it is indeed very easy to modify, and should have produced exactly what I hoped for.  When I first evaluated EWB I looked at this and assumed it would do precisely what I needed.  I sketched out the design of the XML files, mocked up my first EWB screens, and was ready to go.  Last week I started working with the module code, and the biolife database, but nothing I could do would cause the XML file to be updated on commit.  I even tried dropping in a biolife.SavetoFile in various places, which merely produced unchanged copies of the original XML.

Ok, i took a look and yes you need to add a SaveToFile but after that it
works fine - i tried changing a column value as well as delete a row and
both were correctly reflected back in the xml.

Concurrent use by multiple users would still be an issue in case of XML
file but otherwise this should work fine for single user or
non-concurrent multiple user scenario.

easy way to save the changes is to just add the savetofile into destroy
function:

procedure TExampleDataSetModule.EWBModuleDestroy(Sender: TObject);
begin
BioLife.SaveToFile(IncludeTrailingBackslash(ExtractFilePath(OSModuleFileName))+'data\biolife.xml',dfXML);
  Biolife.Close;
end;


One more note is that do not use the XML file size as indicator - even
after deleting data the file remains same size. either reload from web
interface to see changes or run a binary comperison to be sure.

Raul

Tim,

<< Has anyone managed to get EWB to work properly with SQLiteODBC?  After
much effort I have managed to view a simple SQLite table in an EWB app, both
in the IDE server, and in EWBSRVR using a browser, but there is no sign that
EWB is going to be able to update the table.  Procmon shows the file is
never written to, though Commit shows no error. >>

EWB isn't updating the table - ADO and the SQLiteODBC driver are doing so.
EWB is using the same exact same ADO client code for SQLite that it uses for
every other ODBC driver, so the issue is most likely on the ODBC side.  This
is all *very* basic stuff.

<< We need to be able to create web applications to view AND update about a
dozen simple tables, for installation at (unskilled-) customer sites.  So
installing MS-SQL and similar major databases is a non-starter.  We cannot
use  DBISAM and ElevateDB because a few of our customers will want to access
the data themselves, so a proprietary database is no use.  SQLite, or even
plain XML files, would be ideal. >>

Both DBISAM and ElevateDB come with ODBC drivers and royalty-free
distribution.  If you want to give your customers access to the data, you
can do so without any additional cost to them, and very little up-front cost
for you (less than $300).  Plus, they can always use our free utilities to
access/update the data, even if they don't have access to an ODBC driver.

<< After a brief evaluation and some recommendations I had high hopes that
EWB was going to be the answer for rapid development of this type of
application.  But though the UI is easy, finding a suitable back end is a
struggle. All the examples I have found are fine for viewing data, but
Elevate seem to assume that everyone who wants to do more than viewing the
data is going to buy their database products, because there are NO examples
of updating databases or inserting records with any other back end. The
DataSetClient and even the CDCollector examples are both read-only. >>

We don't provide examples for other databases because we don't own those
database engines and can't easily re-distribute their engines in some form.
All of these other engines require re-distribution and
installation/configuration, all of which would detract from the main purpose
of the examples - to show the customer a working example as quickly as
possible.

<< If nobody has any suggestions for getting SQLite working with EWB, are
there any suggestions for a mechanism to update really simple data files, as
well as viewing the data, from an EWB application?  I don't really want to
re-invent the wheel and twiddle the JSON strings myself:  I want something
that is as simple and quick to implement as the EWB front-end. >>

Email me (timyoung@elevatesoft.com) your database along with the SQLite ODBC
driver installation that you're using.  I'll take a look and tell you where
the problem is.

Tim Young
Elevate Software
www.elevatesoft.com

Raul wrote:

Ok, i took a look and yes you need to add a SaveToFile but after that it
works fine - i tried changing a column value as well as delete a row and
both were correctly reflected back in the xml.

Thanks very much: this nearly works.  I thought I had done the same earlier, but before, I saw no change to the file timestamp, and now I do.

I can change a string or value in the biolife table, hit return to fix it, click commit, and see the XML file timestamp update.  And usually, the data in the XML file is updated to match.  But sadly not always. After a while the data in the browser is updated, and the file is rewritten, but with no change to the data in the file. If I decide to persist with the XML solution, I will do some more experimentation with different browsers and run the module code in an elevated delphi debugger to try to work out what is going on.  It may be some sort of browser issue.  And I will start a separate thread if I still have a problem.

On the other hand, Tim may have persuaded me to purchase ElevateDB, and certainly to try it!  And if not, I may take him up on his offer to look at what might be wrong with using SQLite and ODBC.  I accept that it is more than likely to be an issue not connected with EWB.

Thanks again for all the help!