The best website database!

[AM...@aol.com]

Hi All

I currently use MS ACCESS as my backend to my website and other developers in 
the forum have advised this is not a good choice and I am aware of this but I 
was forced to use it as all data comes to me in MS ACCESS from database admin 
that supply the data for the website. I want to know what is the best 
database to use for a website? 

features I am looking for are:

1- An excellent GUI (Very important) front like MS ACCESS where I can quickly 
design queries, tables and so on. I normally design all the queries in MS 
ACCESS and then I just write the single line command "SELECT QUERY1" rather 
than writing the full query in a javabean or jsp page by hand as this saves 
me alot of time.

2- Good drivers (I know the MYSQL comes with Mark Mathews free drivers which 
are excellent - so i am preferabally looking for a cheap/free driver aswell).

3- Be able to export MS ACCESS databases (I would like to do this but not 
extremely important in long run as I might be able to convince my data 
suppliers to change databases)

4- Must be quite fast at processing alot of queries.

I have done some reading on ORACLE, MYSQL (used it in university but was only 
text based!!! I know there are some GUI available but how good are they?), 
and SQL Server.

Input welcomed from all

Thanks in advance
Kind Regards
Amran

--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>

RE: The best website database!

[Andrew Conrad <an...@msn.com>]

Actually Desktop SQL is SQL Server, it's just a change in configurations
(smaller default mem usage) and I believe a limited number of concurrent
connections.  It's also meant to work on Windows 95/98/ME.  I also
believe if it is run on 9x it doesn't support Named Pipes.


As far as a good database for web development (specifically Java
development), you can always look on the JDBC Driver support page.  

http://industry.java.sun.com/products/jdbc/drivers


A Level 4 Driver is recommended because it theoretically allows for PURE
Java development and portability.  You may or may not want your product
to be J2EE Compliant etc....  check it out.  You will find just about
everything, except Access I believe.  You can also find out if it
supports specific items such as connection pooling etc...


As far as GUI interface development, there are plenty of tools out
there; just they aren't included with the RDBMS most of the time.  Try
looking into Computer Associates ER Win or even (gasp) Visio Architect.



And as far as your problem with your data coming in Access, you can
always just write yourself a simple VB app that automates the moving
your data from one format to another.  The question I have is why are
you moving the data.  If the data was entered into a real RDBMS in the
first place, you wouldn't be moving it, just accessing it differently.
Maybe you need to find out why they are using Access and design a system
that stops this manual Data massaging.


Of course if you are looking for a reason to use Access, you have named
them all.  Easy development, Integrated GUI (sorta), low cost, and the
data is already in Access.


-Andrew



-----Original Message-----
From: Will Hartung [mailto:willh@msoft.com] 
Sent: Friday, June 28, 2002 9:10 PM
To: Tomcat Users List
Subject: Re: The best website database!

----- Original Message -----
From: <AM...@aol.com>
> Hi All
>
> I currently use MS ACCESS as my backend to my website and other
developers
in
> the forum have advised this is not a good choice and I am aware of
this
but I
> was forced to use it as all data comes to me in MS ACCESS from
database
admin
> that supply the data for the website. I want to know what is the best
> database to use for a website?

"It Depends"

Now that that is out of the way...

> features I am looking for are:
>
> 1- An excellent GUI (Very important) front like MS ACCESS where I can
quickly
> design queries, tables and so on. I normally design all the queries in
MS
> ACCESS and then I just write the single line command "SELECT QUERY1"
rather
> than writing the full query in a javabean or jsp page by hand as this
saves
> me alot of time.

Ideally, you will, in time, wean yourself of this reliance, as most
databases do not have a GUI. In the long term it's better to have a more
intimate relathionship with SQL, minimally for performance reasons. But,
it
seems pretty clear you haven't reached that threshold yet.

However, all is not completely lost.

One of the Neat Things(tm) about MS ACCESS is not so much that it has a
built-in database based on Microsofts Jet Engine, but that it can act as
a
central access point for, technically, any number of ODBC compliant
databases. So, you can theoretically still use ACCESS as your primary
database, but have all of the tables be linked from the true database
host.

This will certainly cost you performance, and the queries you devise may
not
work quite like they do in native ACCESS, but it will be close.
Particularly
if you are sticking close to the most basic of SQL functionality, and
not
relying on a lot of the higher level functions provided within ACCESS.

If you're using mostly "Pure SQL", then ACCESS is simply a data broker,
and
yet another layer between your app and your DB.

As long as the contention and locking facilities are being used in the
native backend, versus within ACCESS, you should gain quite a bit of
reliablity over pure ACCESS as well. I'm not totally sure if this is the
case, but it probably is, again for basic SQL statements.

The goal is to use ACCESS simply as an interface into your new database,
and
have it broker your SQL statements to and from the database, with
hopefully
as little intervention as possible. Your "SELECT QUERY1" will still
work,
however, but beware that ACCESS will potentially happily suck in all of
the
data from the new DB, churn on it, and then spit it back out to you.
Depending on the query, this can be expensive and isn't what you want.
If
you like the simplicity of "SELECT QUERY1" from ACCESS, I would suggest
that
you perhaps use ACCESS to develop your basic queries, and then use the
actual SQL generated to turn them into VIEWS on the host DB, so the SQL
in
your Java becomes "SELECT * FROM VIEW1".

Again, it's almost always better to use the literal SQL rather than
VIEWs,
etc. ESPECIALLY if you're joining them together. "SELECT * FROM VIEW1,
VIEW2
WHERE ..." CAN be very expensive. It all depends.

But, when you data or activity gets big enough to actually notice the
performance dogging, this kind of stuff tends to float to the top pretty
quick, so when you need to fix it, you'll find it. These kinds of things
work great with 10 rows in the tables, and die horribly with 10000 rows.

You WILL have issue with BLOBs of any kind, however.

Of course, if you continue to receive data from your admin in ACCESS
format,
you will need a way to convert that data into the new back end. Ideally,
this to can be automated from ACCESS as well: INSERT INTO
NewBackEndTable
SELECT * FROM OriginalACCESSTable.

So, the point being, that even if you go to a new database, you do not
necessarily have to leave the capabilites of ACCESS behind. Eventually,
particularly as your site gets more loaded, you will want to yank ACCESS
out
of the loop and go direct, but it wouldn't surprise me if that's farther
away than you may think. It depends on your site.

As to which backend, Oracle and SQL Server are more than capable. Oracle
has
very good direct JDBC support, whereas SQL Server does not, the JDBC
drivers
come from third parties. However you're probably using the JDBC<->ODBC
bridge. I've never used it in anger, so can't say anything to its
productiion worthiness, however I've never heard glowing things about
it.

But if you continue to talk to ACCESS, you're pretty much stuck with it
anyhow.

Both of these DBs are quite expensive.

mysql's biggest point against it is that it does not conform as well to
the
standard SQL syntax and behaviors. It has other issues with it, but many
folks are satisfied and work around them. I don't know how well its ODBC
drivers may work.

PostgreSQL has more conventional SQL DB capabilities, and its own hoards
of
raving fans, and decent Java support, so it should be considered as
well. I
don't know how good its ODBC driver is either, however.

These two DB's are, of course, free.

Has anyone used the new "desktop" MS SQL database engine? Isn't that the
backend of the Latest/Greatest ACCESS?

You may also want to consider something like SQL Anywhere from Sybase,
or
even Interbase/Firebird from WhateverBorlandIsCalledToday. They're
cheap,
and not slouches for basic DB stuff. Sure, you can't have clusters of
them
running on Mega SMP machines. But I really doubt that's the issue today.

When the modern "desktop" is a 1.5GHz machine, with 512MB of RAM,
there's a
LOT of capability in a "workgroup" database server today.

Let's all raise our hands if you've supported over a hundred users on a
33Mhz 68030 with 64MB of RAM? App and DB...Come on..up with the
hands....Anyone?

Anyway, hope that helps.

As said in the beginning, it depends.

Best Regards,

Will Hartung
(willh@msoft.com)





--
To unsubscribe, e-mail:
<ma...@jakarta.apache.org>
For additional commands, e-mail:
<ma...@jakarta.apache.org>


--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>

Re: The best website database!

[Will Hartung <wi...@msoft.com>]

----- Original Message -----
From: <AM...@aol.com>
> Hi All
>
> I currently use MS ACCESS as my backend to my website and other developers
in
> the forum have advised this is not a good choice and I am aware of this
but I
> was forced to use it as all data comes to me in MS ACCESS from database
admin
> that supply the data for the website. I want to know what is the best
> database to use for a website?

"It Depends"

Now that that is out of the way...

> features I am looking for are:
>
> 1- An excellent GUI (Very important) front like MS ACCESS where I can
quickly
> design queries, tables and so on. I normally design all the queries in MS
> ACCESS and then I just write the single line command "SELECT QUERY1"
rather
> than writing the full query in a javabean or jsp page by hand as this
saves
> me alot of time.

Ideally, you will, in time, wean yourself of this reliance, as most
databases do not have a GUI. In the long term it's better to have a more
intimate relathionship with SQL, minimally for performance reasons. But, it
seems pretty clear you haven't reached that threshold yet.

However, all is not completely lost.

One of the Neat Things(tm) about MS ACCESS is not so much that it has a
built-in database based on Microsofts Jet Engine, but that it can act as a
central access point for, technically, any number of ODBC compliant
databases. So, you can theoretically still use ACCESS as your primary
database, but have all of the tables be linked from the true database host.

This will certainly cost you performance, and the queries you devise may not
work quite like they do in native ACCESS, but it will be close. Particularly
if you are sticking close to the most basic of SQL functionality, and not
relying on a lot of the higher level functions provided within ACCESS.

If you're using mostly "Pure SQL", then ACCESS is simply a data broker, and
yet another layer between your app and your DB.

As long as the contention and locking facilities are being used in the
native backend, versus within ACCESS, you should gain quite a bit of
reliablity over pure ACCESS as well. I'm not totally sure if this is the
case, but it probably is, again for basic SQL statements.

The goal is to use ACCESS simply as an interface into your new database, and
have it broker your SQL statements to and from the database, with hopefully
as little intervention as possible. Your "SELECT QUERY1" will still work,
however, but beware that ACCESS will potentially happily suck in all of the
data from the new DB, churn on it, and then spit it back out to you.
Depending on the query, this can be expensive and isn't what you want. If
you like the simplicity of "SELECT QUERY1" from ACCESS, I would suggest that
you perhaps use ACCESS to develop your basic queries, and then use the
actual SQL generated to turn them into VIEWS on the host DB, so the SQL in
your Java becomes "SELECT * FROM VIEW1".

Again, it's almost always better to use the literal SQL rather than VIEWs,
etc. ESPECIALLY if you're joining them together. "SELECT * FROM VIEW1, VIEW2
WHERE ..." CAN be very expensive. It all depends.

But, when you data or activity gets big enough to actually notice the
performance dogging, this kind of stuff tends to float to the top pretty
quick, so when you need to fix it, you'll find it. These kinds of things
work great with 10 rows in the tables, and die horribly with 10000 rows.

You WILL have issue with BLOBs of any kind, however.

Of course, if you continue to receive data from your admin in ACCESS format,
you will need a way to convert that data into the new back end. Ideally,
this to can be automated from ACCESS as well: INSERT INTO NewBackEndTable
SELECT * FROM OriginalACCESSTable.

So, the point being, that even if you go to a new database, you do not
necessarily have to leave the capabilites of ACCESS behind. Eventually,
particularly as your site gets more loaded, you will want to yank ACCESS out
of the loop and go direct, but it wouldn't surprise me if that's farther
away than you may think. It depends on your site.

As to which backend, Oracle and SQL Server are more than capable. Oracle has
very good direct JDBC support, whereas SQL Server does not, the JDBC drivers
come from third parties. However you're probably using the JDBC<->ODBC
bridge. I've never used it in anger, so can't say anything to its
productiion worthiness, however I've never heard glowing things about it.

But if you continue to talk to ACCESS, you're pretty much stuck with it
anyhow.

Both of these DBs are quite expensive.

mysql's biggest point against it is that it does not conform as well to the
standard SQL syntax and behaviors. It has other issues with it, but many
folks are satisfied and work around them. I don't know how well its ODBC
drivers may work.

PostgreSQL has more conventional SQL DB capabilities, and its own hoards of
raving fans, and decent Java support, so it should be considered as well. I
don't know how good its ODBC driver is either, however.

These two DB's are, of course, free.

Has anyone used the new "desktop" MS SQL database engine? Isn't that the
backend of the Latest/Greatest ACCESS?

You may also want to consider something like SQL Anywhere from Sybase, or
even Interbase/Firebird from WhateverBorlandIsCalledToday. They're cheap,
and not slouches for basic DB stuff. Sure, you can't have clusters of them
running on Mega SMP machines. But I really doubt that's the issue today.

When the modern "desktop" is a 1.5GHz machine, with 512MB of RAM, there's a
LOT of capability in a "workgroup" database server today.

Let's all raise our hands if you've supported over a hundred users on a
33Mhz 68030 with 64MB of RAM? App and DB...Come on..up with the
hands....Anyone?

Anyway, hope that helps.

As said in the beginning, it depends.

Best Regards,

Will Hartung
(willh@msoft.com)





--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>

RE: The best website database!

[Markus Gieppner <vi...@mgfint.com>]

Hi Amram,

Take a look at SAPDB (www.sapdb.org). It's free even for commercial
applications, very scalable & powerful, has plenty of functions, stored
procedures, transactions, replication etc., a nice user interface in
Windows. JDBC, ODBC, Perl and Python interfaces, a C/C++ precompiler and
lots of documentation is available.

For a simple website that doesn't require transactions this might actually
be almost an overkill, and a "lightweight" db be more appropriate (like
MySQL).  SAPDB is closer to the big ones like Oracle.

I personally like it a lot, unlike PostGreSQL it runs well on Windows.

Markus





-----Original Message-----
From: AMRAN121@aol.com [mailto:AMRAN121@aol.com]
Sent: Friday, June 28, 2002 6:05 PM
To: tomcat-user@jakarta.apache.org
Subject: The best website database!


Hi All

I currently use MS ACCESS as my backend to my website and other developers
in
the forum have advised this is not a good choice and I am aware of this but
I
was forced to use it as all data comes to me in MS ACCESS from database
admin
that supply the data for the website. I want to know what is the best
database to use for a website?

features I am looking for are:

1- An excellent GUI (Very important) front like MS ACCESS where I can
quickly
design queries, tables and so on. I normally design all the queries in MS
ACCESS and then I just write the single line command "SELECT QUERY1" rather
than writing the full query in a javabean or jsp page by hand as this saves
me alot of time.

2- Good drivers (I know the MYSQL comes with Mark Mathews free drivers which
are excellent - so i am preferabally looking for a cheap/free driver
aswell).

3- Be able to export MS ACCESS databases (I would like to do this but not
extremely important in long run as I might be able to convince my data
suppliers to change databases)

4- Must be quite fast at processing alot of queries.

I have done some reading on ORACLE, MYSQL (used it in university but was
only
text based!!! I know there are some GUI available but how good are they?),
and SQL Server.

Input welcomed from all

Thanks in advance
Kind Regards
Amran

--
To unsubscribe, e-mail:
<ma...@jakarta.apache.org>
For additional commands, e-mail:
<ma...@jakarta.apache.org>


---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.372 / Virus Database: 207 - Release Date: 6/20/2002

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.372 / Virus Database: 207 - Release Date: 6/20/2002


--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>