PE / SQL USER MANUAL
Your ISP server MUST support Microsoft’s .NET Framework.
The PE /SQL Translator software is available as pesql-deploy.zip that contains the client package and the server package. If you plan to use the Srvice package, running you onw PE / SQL engine, you also need the Client package. To install both packages requires running 2 setups.
To use StarMan's internet service, you just need the Client package. However you need a User Code before you can access your database. (see Service Requirements under Service link).
Client Package, webform1.aspx
All that you need to do to access PE/SQL is to provide the following code in your ASPX page. A sample page that can be modified for your application is provided, in C#, and its script block is shown below. You can write the equivalent script in VB.
// The following script is required in your web page. It should reflect the event, usually a button click,
// named in the onClick method. TextBox1 and TextBox2 can should also be chnaged to reflect your web compnents.
// See the button tag below.
// Please insert your user code as mypath, replacing "dalis"
// While we are using C#, you can use VB instead and code appropriately, following the C# equivalents
// ***** Do not change the following statement **********
pesql_server.pesql_Service1 proxy = new pesql_server.pesql_Service1();
void Button1_Click(object sender, System.EventArgs e) {
String myenglish, mySQL, mpath;
mpath = "dalis"; // Server StarMan test DCT
myenglish = TextBox1.Text;
mySQL = proxy.GetSQL(mpath, myenglish);
// **** Do not change the above statement **********
// The next line should be replaced with the following redirect command which invokes
// your output display page.
TextBox2.Text = mySQL;
// Response.Redirect("http://starman.com/vbbb/listacc.aspx?DSQL=" + mySQL);
// Substitute the above line, using your appropriate web page before the "?".The remainder remains as is.
// It uses the GET method of passing the SQL statement to your web page. Your web page needs the following script statement:
// var = Resquest.QueryString("DSQL")
}
After modification, this file must be loaded to your web server "client" folder for access by you and your clients.
Server Package, pesql.asmx
In order to access PE /SQL translator server on your ISP, you will need to copy all those files found in the pesql-S.zip, pesql-server folder, to an appropriate folder on your ISP.
PE /SQL Dictionary requirements
The PE-GEN.MDB, found in the Client package, and related files are designed to remain on your local site for preparation of the dictionary file that needs to also be loaded to the BIN directory of your application on the ISP. The output file must be renamed with your User Code name, as "user.dct", with your code replacing the word "user". (See further instructions in this manual). This file must be loaded on your web server application/bin directory.
II. Using PE / SQL
A. What is PE / SQL?
PE / SQL stands for Plain English SQL queries. Rather than attempting to train users or customers on the complexities of the ever changing SQL syntax definitions, this approach makes use of commonly used business English, or CUBE. The following is a more complex example of a simple search instruction, or query. The user enters 83 characters and the software generates the complex SQL-92 syntax of 168 characters.
PE /SQL saves time and money by allowing for a more efficient search language – less stress, less frustration and better results. Your users don’t need to know anything about the table or column names, just simple business English, that’s all. The exception is in the more advanced searches using a secondary database, when a CUBE table name must be provided. See the above user statement where the table names follow the word “IN”.
While version 5.5 does not attempt to handle every aspect of SQL syntax, it does allow for a linked second table search and will generate an INNER JOIN. See C, Technical Specs for more information.
Currently, version 5.5 of the software is available as an Internet ASPX .NET Framework web page with embedded C# code. Version 5.5 comes with a MS Access 2000 database that facilitates the generation of the CUBE dictionary. This dictionary contains all words acceptable to PE /SQL and their “meanings”. The database, PE-GEN.mdb, is easy to use for editing and tailoring the software uniquely to each database. It comes with a Basic, generic dictionary and a sample dictionary as a tutorial.
B. Using PE / SQL
The package
comes with an ASPX page for testing and tutorial purposes,
which should be replaced by your appropriate application pages.
The page is independent of any database access code, and simply
passes off the SQL to another page for accessing the database.
While
we tried to make the software as compatible to English as
possible, there is always the need for some structure – just
can’t get away with it. Some items to keep in mind:
1.
Use the specs in section C, Technical Specs as a guide;
2.
Review the sample queries shown in the included ASPX page;
3.
The default or target table is given in the DCT file and will be
used where no table names are provided, elimination in most cases
any mention of a specific table;
4.
Each column or data name has a default table associated with it,
which will be used to generate the appropriate SQL syntax;
5.
ERRORs will usually occur in regard to typing errors, or the use
of invalid data/column names in the search statement;
sometimes it will be due to not following the specified search
English;
6.
Error notifications will occur from the Internet as a result of
incorrect links to the database or as a result of an error in SQL
syntax; try revising your statement to shift condition statements
and see if the error persists, if so, please notify us via email.
C. Generating the Dictionary file -- .DCT
The
package comes with PE-GEN.MDB, a MS Access 2000 database to help
generate and tailor the dictionary to your database, quickly and
easily. This program executes macros, queries and Visual
Basic code to extract all the data/column names from the target
database as well as from any secondary/relational databases.
The
first menu entry, Generate DCT file, consists of a simple
dialogue and does all the hard work. While several
secondary files may be added, PE / SQL only allows 1 secondary
database in any search statement. It causes the following dialog
box to pop up. Select “target’ for the first or a new
generation for a DCT, deleting the old version, and “Add”
to add the secondary files to the existing DCT file. The
complete path must be provided.
After
generating and adding any secondary files to the DCT file, you
need to add the Basic DCT data to your application DCT.
EDITING
Next,
your DCT needs to be tailored to the common English being used in
regard to your application. For example, you ordinarily would
refer to “sales”, but really mean the “product.sales”
column and data within your Product database. However, the
generator program stores the complete, technical column value as
the PE search word. This is workable, but defeats the purpose of
PE/SQL. You would need to select “View/Edit DCT”
from the menu and revise any appropriate PE search words. They
are found in the first column under Field1. Filed10 shows
the technical data name as will be generated in the SQL query. Only
the words found in Field1 are acceptable.
All
data names/columns are identified by a FIELD5 entry of “FIELD”
and all table words are identified by a FIELD5 entry of “TBL”.
SQL/table words:
Certain
words are “combo” words that are used in the PE search,
but need to refer to the default database. These are “all”,
“*”, and “every” and can be found in Field1.
They simply refer to all the data/column names in a table. They
are part of the Basic DCT table and are not generated by step 1
above.
You
need to edit Field6 by replacing the existing table name, found
in front of the “dot” with the default table name. For
example, using a table called “product”, the entry
would now look like “product.*” in Field6 for each of
these words.
Same words:
You
may use any name or word that you feel is appropriate and it may
refer to more than one table, but to only one column within
the table. Look at the Sample DCT file and you will notice
that Field6, for certain words, contains an entry like (see
“broker”):
SOLD.BROKER:NAMES.CONTACT
This
entry indicates that the word “broker” in Field1 can
refer to 2 different tables, each with its own column names. In
the SOLD table “broker” means “Broker”, while
in the Names table it means “Contact”. The colon is
used as a separator symbol.
Linked tables:
What
is a linked table? These are the relational tables
that make the database a relational database. The way
it works is very simple. In order for 2 tables to be linked
there must be a data name/column in each table that represents
the same data. For example, in the above instance, the SOLD
table can be linked to the NAMES table by the column names Broker
and Contact, respectfully. The linked columns are specific to
each particular relationship: the SOLD table can also be linked
to the ACTIV table by their respective “category”
fields.
A
slightly more involved editing must be performed if you plan to
use secondary databases/tables. In the Sample DCT table,
look at the word “NAMES” and you will see an entry in
Field5 as “TBL”. Field6 has the following entry:
NAMES:
sold.cat/cat:activ.contact/agt
Follow
closely, now. This means that the “NAMES” table
is linked to the “SOLD” table by its “cat”
column to the other table’s “cat” column. It
is also linked to the “ACTIV” table by means of its
“agt” column with the activ table’s “contact”
column. These relationships are obviously reciprocal.
SOLD: names.cat/cat:activ.contact/contact
^
^
|
|
NAMES: sold.cat/cat:activ.contact/agt
|
__________|
|
|
|
v
|
ACTIV
: NAMES.AGT/CONTACT:SOLD.CONTACT/CONTACT
Study
the above carefully. As an aide, you can look at the
involved PE words contained in the column names above. Separate
the code above by the “/”. The left-side represents the
technical data name of the linked database and the right side is
the name used in linking database.
Each
of these column names must have a FIELD6 entry showing that it is
also “defined” in these tables. It’s logical
that tables cannot be linked if the PE words are not to be
found in the other table, as indicated by the following FIELD6
entries for these words:
AGT:
NAMES.AGT:VBBBMEM.CONTACT:ACTIV.CONTACT
CAT:
ACTIV.CATEGORY:SOLD.CAT:NAMES:CAT
CONTACT:
ACTIV.CONTACT:SOLD.CONTACT:NAMES.AGT
Again, these
entries are only necessary if you plan to use secondary tables in
your search statements.
CONVERSION:
After you are satisfied with the above, you can then click on the “Produce DCT” box on the menu and you are all done generating your dictionary file for this application. You need to change the File Name in the Produce DCT macro to your User Code. You can only have 1 DCT in a folder, the BIN sub-folder. To run a second PE /SQL application on some other TARGET database, it must be run form some other folder.