Hi All,

Thanks in advance for the help.

We are considering a new Project. This project is going to be totally
web based.

Currently my company is using Pervasive SQL 2000i for there ERP needs.
Will the existing Pervasive SQL 2000i itself serve the purpose OR
should I consider moving to MS SQL Server.

Do assist me in terms of development also i.e. Is it easier to code
with Pervasive SQL 2000i or with MS SQL Server.

Regards
Samson

Hi Samson,

If the new project is intended to use data from the existing PSQL ERP
database, then I would strongly recommend staying with Pervasive.SQL.

I would recommend upgrading to version 9, primarily to gain a higher
level of SQL compliance, but also to gain access to better tools and
greater SQL performance than you get with v2000i. Here's a link to a
summary of why you might want to upgrade:
http://www.analyticabiz.com/psqlv9sp2partner.php

You haven't mentioned what your development platform is going to be
(Linux/Apache/PHP, .NET, Java) but it doesn't matter really.
Pervasive.SQL is cross-platform (Windows, Linux, Netware) and is
accessible by all access methods, including ADO, ADOdb and JDBC, just
to name a few.

If you are intending to expose the data to the internet rather than
just to an intranet, you'll need to purchase an internet license. It
doesn't sound like you intend to do that, though.

In short, PSQL will serve your needs perfectly well, and will save you
the conversion costs of going to MSSQL.

Having said all that, if the project is not intended to use the
existing database, your options are more open. While PSQL would still
serve the new project's needs just fine, the advantages of using it
specifically for this project diminish, along these lines: If the new
database is going to reside on the current server, then it would be
better to stay with PSQL; if the new database will reside on a new,
dedicated server, then PSQL, MSSQL, MySQL or PostgreSQL would do
probably equally well, depending, again, on your platform. All of the
above would work on Linux, for example, except for MSSQL.

HTH.

Wayne
Analytica Business Systems, Inc.
A Premier Pervasive Partner

To subscribe to our Pervasive news feed:
www.analyticabiz.com/rss20.php

Samson wrote:

Hi Wayne,

Thanks a lot for spending your time to help me out. Your technical
comments have been really helpful to me in shortlisting my queries.

For the new project, our developmental platform will be .NET. In this
project we will be accessing few data (about 10%) from the ERP Project.
We do intend to expose this project to the Internet.

Is Pervasive SQL secure enough in the Internet? Does it support
ADO.NET. Does it support all the SQL Statements?

In one website, I read that Pervasive SQL executes SQL Statements
faster only if it has the correct indexes specified in the Table, if
not it does a complete table scan thereby effecting it's performance.
Is this disability overridden now?

In general is it's performance at par with MS SQL.

Your comments on the above would be greatly appreciated.

Thank you,

Regards,
Samson



Wayne wrote:

Hi Samson,

My responses inline:

Samson wrote:

In that case, I definitely recommend NOT converting your ERP system
away from PSQL.

Yes. It offers full security management through standard SQL security
grammar, and integrates with the server-based security to the extent
you wish.

Yes.

That's a difficult question to answer. "All" is a lot! There are so
many extensions. All I can do is quote the PSQL v9 SQL documentation:

"The ODBC v2.5 specification provides three levels of SQL grammar
conformance: Minimum, Core, and Extended. Each higher level provides
more fully-implemented data definition and data manipulation language
support. The Pervasive ODBC Engine Interface fully supports the minimum
SQL grammar, as well as many core and extended grammar statements. The
Pervasive ODBC Engine Interface support for SQL grammar is summarized
in the following table."

Here is a link to the page this quote comes from, which includes a
table:
http://www.pervasive.com/library/doc...lref-03-5.html

This is true. This is always true. This is true for every DBMS under
the sun, except for those thankfully-obsolete flat-file databases that
did not have indexing capability and had to search sequentially for
everything. Unoptimized queries will always be slower than optimized
queries, in all databases. The real questioin is how much of a penalty
do you pay for issuing an unoptimized query. There are several factors
that might make that penalty more severe in PSQL than in others, some
of which are architectural (historically the architecture of PSQL is
more index-centric) and others are more implementation-dependent (most
commercial apps that use PSQL use the transactional engine, therefore
access is 100% index-based and support for relational access is an
afterthought).

Personally, I cannot imagine intentionally writing an application that
contains queries that do not use an index. To me, that's simply bad
programming practice. Ad hoc queries built by end users, yes, they can
be excused, but the developers, no. If a query is going to be used
often enough to actually code it into a program, and it does not use an
index, then a proper index needs to be created to accommodate it.

Stepping down from my soap box, this penalty is less severe for the
later versions of PSQL and becomes even less so with every release,
primarily due to more efficient usage of internal temp tables.

In general, yes. Pervasive cites independently-researched benchmarks
that show that PSQL is faster than MSSQL. While they're real and
accurate, they are easy to refute, especially by benchmarking a query
that does not use an index, which is everybody's favorite.

Hope this helps.

Wayne

Hi Samson,

As Wayne said in his response, this is a general issue with any DB. But
for versions from PSQL 2000 to PSQL 8, Pervasive could be brain dead
as far as automatically picking the correct (or most useful) index.

We had trouble with it picking the wrong index, and doing a table scan
anyway, where if it had picked the "correct" index, it wouldn't have.
We have had to resort to oddball SQL constructs to make it pick the
right index. E.g., we might have to say "... in (someval, made up
list)" instead of just saying "column = someval"

But in PSQL 9 +, I've read that Pervasive has added the ability to
specify the index. I haven't had an opportunity to use that feature
yet, so I can't report on how it works.

Paul

Hi Paul,

Thanks for passing your observation of Indexes.

I presume that it would be advisable for me to move to PSQL 9+. But I
Hope pervasive SQL 9+ picks up the correct index automatically based on
our query and we don't have to explicity specify the index name which
the query needs to use.

Most of my developers are from MS SQL server background, I only hope
that there migration to P.SQL will be smooth and there learning curve
will be less.

Regards,
Samson

Paul Rodden wrote:

know squat.

The security went tits up on one of our databases in version of v9.5
for Novell and pervasive control centre just kept throwing up errors as
if some sort of configuration file had got corrupted (the Java log had
loads of null pointer exceptions in it, so I assume something is
failing to get set on loading the data).

With absolutely no information about the workings of PSQL we are having
trouble tracking down the source of the fault.

It's not as if we can go and get a book and read up on pervasive
either, there are none, and I've failed to locate any consultants in
the UK.

So why go with Pervasive when you have the choice of switching to
something that has tons and tons of good documentation, like MySQL or
infact anything other than PSQL?

If anyone has any direction for us (sorry to take the focus of the
problem in hand here) let me know.

Wayne wrote:

Hi Paul,

I have not had your experience. Granted, PSQL's index-detection
algorithms were not as good back in v7 and v2000i as they are now, or
rather, they've improved over the years, but I've never needed to go to
the lengths you've had to in order to ensure that it use an index.

The general rule, at least since v2000i, has been that if you include
in a WHERE clause or an ORDER BY clause any column that is part of an
index, the index-derivation algorithm will discover some index to use,
and will do its best to discover the most optimal index to use. It's
the "will do its best" part that has been improving over the years.

I can't recall a time when I've followed this rule that PSQL has failed
to use some index.

Clearly, that would apply to all tables in a join, for instance, and if
you specify an "index column" in the parent table and a "non-index
column" in the child, PSQL will need to search all (or most) of the
child table to satisfy the join. Presumably, the database schema would
have already taken that into account and the tables would have been
properly normalized, thereby minimizing such occurances. On the other
hand, it might be perfectly acceptable to not use an index on very
small lookup tables, like what might appear in a drop-down list box.

For more complex queries, I'd recommend always using the Query Plan
Builder, and to use stored procedures as often as possible in place of
in-line coded queries. That way, the queries can be optimized ahead of
time and reused. But I'm probably not telling you anything you don't
already know.

Wayne
Analytica Business Systems
A Pervasive Software Premier Partner
www.analyticabiz.com

Paul Rodden wrote:

You're absolutely right. I looked up "went tits up" in the PSQL Status
Code list and even in the Pervasive Knowledge Base and couldn't find
any references to it anywhere.

At any rate, I think you're looking at two different issues. If you'd
describe exactly what your security symptoms are, I or someone else
here might be able to help out. Really.

As for the PCC errors, it sounds to me as if the JRE might be corrupt
or the PCC needs to be reinstalled. Thankfully, only the PCC uses
Java, not the Pervasive Microkernel Engine or the Relational Engine, so
the PCC errors are probably a red herring.

What errors is PCC throwing up?

As for why use Pervasive.SQL? Well, from 2000 to 2004 I was the PSQL
"DBA" for a retail chain with 350+ stores, each of which had a PSQL
v2000i server managing a 1.5 gb store database with 3 - 15 users.
Administering them all took probably .1% (that's point 1 percent) of my
time. If that. Try that with MSSQL. Oh, and I've been gone two years
now and they're still running all of that and they haven't had to
replace me.

Wayne
Analytica Business Systems
A Pervasive Software Premier Partner
www.analyticabiz.com

seetickets wrote:

Hi Wayne,

Post titled Pervasive SQL 9.5 Security Problems, the original post is
below. I'll take note about the Java errors probably being misleading.

Thanks
Glyn


---------------
Hi people,

We are currently in the process of setting up pervasive sql v9.5 on
Netware 5.1, we tried to set up security in the form of a Master user &
password using Pervasive control centre, we then however disabled it.

Now when we try to access some of our databases we need to login as
Master and for some others we do not.

When we try to go to the properties for one of the databases in the
Control centre, and go to security there is nothing displayed, ant
theres a java log saying that some of the widgets could not be created
(I guess because the configuration file it is trying to read is
corrupt?). If we then try to save anything we get the following error:

"Could not accept changes - the currently displayed page contains
invalid values"


Any ideas on what my be causing this?

Thanks in advance

Wayne wrote: