Database Manager
Table of Contents
Introduction
Init Database.pub.bbj
Dimension Record or Template
Query BBj
Query Vector
Update Vector
Read
Extract
Query
Write
Transactions
Insert
Delete
Update Query
ResultSet Query
Open Old Style Channels.
Installing Database.pub.bbj
Indroduction
The problem with BBj SQL is that it is slower than Read Record. This plus other problems has convinced me to stop using it. But I've been working on a program called DATABASE.PUB.BBJ. The idea is to put all file operations into a seperate program, and configure the calls to the program so that they will work on both SQL and BBj files.
After using it for awhile I've discovered other advantages. Coding file reads is much easier. Rather than trying to set up a read record loop, I just cut the call routines from this page and paste it into my program. Also, it's easier to make a call to Query_BBj and then work with the returned vector then to code a read record loop like I would have before.
I can also use this vector to write back to the file. Sure makes coding grids easier since I can just do one call to read in the grid, than another call that compares what's on the grid with the original vector and writes out the differences, with error messages if the records on file have been changed somewhere else.
A few notes on the use of "Failed_Flag" versus "Err_Code". I had a lot of trouble figuring out what to do with errors. It didn't help that sometimes an error in bbj isn't really an error, like an error 11. The idea was that I only used "Err_Code" if I might be getting back an error 11. Otherwise if an operation failed I assumed it was an error, with the error message in DATABASE.ERR_MESSAGE$.
To make things even more confusing, I eventually put an CatchError routine in DATABASE.PUB.BBJ, which exits with an error 200, thereby activating the guibuilder error routines.
I have just began using RecordSets and java.sql classes for mysql access. Frankly, if I had known how easy it was I would have used it from the beginning. The big advantage is that you don't have to use bbj templates to access data. There's a lot of modern data that doesn't fit very well into a string template. Also, the access seems cleaner.
Initialise Database.pub.bbj
In mysql I keep a list of databases I use in a table called dbases. It looks like this:
CREATE TABLE `dbases` (
`name` varchar(100) NOT NULL default '',
`AlwaysOpen` tinyint(4) NOT NULL default '1',
`ConnectString` varchar(100) NOT NULL default '',
PRIMARY KEY (`name`)
) TYPE=MyISAM;
Part of the initialisation process is opening these databases and
getting a list of tables
from them. The idea is that you should be able to move your tables from
one database to another without program changes. These two lines
normally initialise DATABASE.PUB.BBJ.
This is a standard initialisation. This will open all databases marked
"Always Open".
DATABASE!=null()
CALL "DATABASE.PUB.BBJ::INIT",DATABASE$,DATABASE!
However, some programs may need access to additional databases, perhaps databases that might not be up all the time, or might be on a VPN and so impose a speed penalty. For example, the MySQL database is marked "Always Open" in dbases. As a main repository for my data, if it's down my bbj programs are down. On the other hand, we have a document storage system, tho, that I only use in a few programs. For it, "Always Open" is marked false. I use the following to include it in the databases I open.
DATABASE!=BBjAPI().makeVector()
DATABASE!.add("docstar")
CALL "DATABASE.PUB.BBJ::INIT",DATABASE$,DATABASE!
Dimension Record or Template
In business basic we are used to opening all our files at the beginning of the program. SQL, of course, doesn't have this concept. So when you do a query or other operation in DATABASE.PUB.BBJ, I check to see if the file is open, then open it if needed. However, the routine Dim_Record, for getting a template for a file, also opens the file if it's bbj. Dim_Record also pads out the fields and set dates to -1.CALL DATABASE.NAME$+"::Dim_Record",DATABASE$,DATABASE!,"ASAC",ASAC$
Query_BBj
This mimics a SQL Query. It returns a vector holding all the records. While it's a bbj file the from and thru keys, and the possible values held in bvValue control the read. The Value.Type is used for the SQL where clause. Here's it's "J", for 7 digit numeric julian.FromKy$ should normally be shorter than the Key(ch), unless FromKy$ matches something in the file. ThruKy$, on the other hand, should be equal in length to LEN(Key(ch)), since bbj seems to see any shorter string as less than.
One limitation is that the Values can't be "OR". Each value has to be true to be returned.
Use DATABASE.KNUM to read along another key chain. It's
automatically setback to zero after each call.
This routine is designed for both bbj and sql files. However, since the
setup is so much
simpler, the routine Query_Vector can be used for new files that will
always be sql.
FromKy$="-1"+fill(5,chr(0)),ThruKy$=fill(7,"2")
Call DATABASE.NAME$+"::Dim_Value",Value$,bvValues!
Value.FieldName$="LD_DATE",Value.Type$="J",Value.Operator$=">",Value.Value$="-1"
bvValues!.add(VALUE$)
Value.FieldName$="LDINVOICE_DATE",Value.Type$="J",Value.Operator$="<",Value.Value$="1"
bvValues!.add(VALUE$)
DATABASE.KNUM=2
Call DATABASE.NAME$+"::Query_BBj",DATABASE$,DATABASE!,"ASLD",ASLD$,bvAll!,FromKy$,ThruKy$
:,bvValues!,SQLE$,Err_Code
Query_Vector
Query_Vector, on the other hand, is for SQL only queries. It returns the result set in a bbj vector. Use Query_Vector if the result set is going to be more than one. Use Query if only one record is to be returned. Query_BBj could be used instead of Query_Vector, even for SQL files, but the setup is much more complicated.
q$="Select towinvdt.* From towinvdt Left Join towinvhd on towinvhd.Transaction_No=towinvdt.Transaction_No
: Where towinvhd.Invoice_No="+InvoiceNo$+" and towinvhd.Invoice_Date="+fnSQLDate$(InvoiceDate)
Call DATABASE.NAME$+"::Query_Vector",DATABASE$,DATABASE!,"towinvdt",Q$,towinvdt$,bvtowinvdt!,
:SQLE$,Err_Code
Update_Vector
You can modify the results from either Query_BBj or Query_Vector. You can then place the modified results in a new vector, then call this routine to update the tables. This routine compares the two vectors, then makes changes as needed.You can add records to the vector, and it will insert them into sql. One problem is where you have autosequence primary keys. You can either run the insert seperately, or with mysql you can just make up a bogus autosequence number which is ignored on insert.
Bear in mind tho that DATABASE.PUB.OBJ puts the New! and the Old! vectors into treemaps with the primary key as reference. So every new record has to have a primary key.
Here's an example. Notice that tplPKey$ is only dimensioned as a template, it's not assiged. That's for SQL, since you have to know the primary key when doing an Update.
If there's an error, the original Query_BBj or Query_Vector is ran again. The "qEnhancements" in parantheses below could be bvValues for a bbj query. If you always redraw the screen with bvOn_File!, just like using the On_File$ in "Write", you can handle errors without addtional programming, as they'll get an error message in DATABASE.PUB.BBJ, and then see the problem when you redraw the screen.
This is easier than it looks. You must have the old FromKy$, ThruKy$, and bvValues from when you did the original DATABASE.PUB.BBJ call. You can then create a new bbj vector from scratch, load it with what you want to save, and then make this call.
Dim tplPKey$:"STOCKNO:C(8),ENHANCEMENT:C(15)"
Let bvNew!=BBjAPI().makeVector()
For i=0 to grdEnhancements!.getNumRows()-1
If grdEnhancements!.getCellListSelection(i,0)>0 then
Dim ENHANCEMENT$:FATTR(ENHANCEMENT$)
Let ENHANCEMENT.STOCKNO$=ASAA.STOCKNO$
Let ENHANCEMENT.ENHANCEMENT$=grdlsbEnhancement!.getItemAt(grdEnhancements!.getCellListSelection(i,0))
bvNew!.add(ENHANCEMENT$)
Fi
Next i
Call DATABASE.NAME$+"::Update_Vector",DATABASE$,DATABASE!,"enhancements",FromKy$,ThruKy$,
:(qEnhancements$),tplPKey$,ENHANCEMENT$,bvNew!,bvEnhancements!,bvIn_Table!,SQLE$,Err_Code
hmLine!.put("bvEnhancements",bvIn_Table!)
Read
Here's the setup for a simple read using DATABASE.PUB.BBJ. If it's a SQL file it uses the template to decode the where clause. The only trick is to always dimension the template with non-delimited fields, so the Read Record(ch,key=tplPKey$) will work.If the record does not exist, the primary key of RECORD$ is make equal to PKey$. This is primarily for new records. If you need the primary key to stay blank, just reverse RECORD$ and RECORD_OLD$. If the record is found they are always identical.
Read is a good example of how I intended DATABASE.PUB.BBJ. Notice
how it checks to see if
it's a bbj file, then uses SQL if it's not.
Read and Delete are the only places where a value has to be set for
PKey. In Read all we
know at this point are the key values. With Delete it just seemed safer
and easier.
Dim PKey$:"PrimaryKey:C(8)"Here's another example, but where another key chain is being used.
PKey.PrimaryKey$=FILE.ForeignKey$
Call DATABASE.NAME$+"::Read",DATABASE$,DATABASE!,"FILENAME",PKey$,RECORD$,RECORD_OLD$,ErrCode
If ErrCode=-1 then
Dim Pkey$:"MAKE:C(4)"
PKey.MAKE$=ASAB.MAKE$
DATABASE.KNUM=1
Call DATABASE.NAME$+"::Read",DATABASE$,DATABASE!,"ASMK",PKey$,ASMK$,ASMK_OLD$,ErrCode
Extract
Sometimes you need to extract a record. This routine doesn't work with SQL yet.
Dim PKey$:"TRANSACTION_NO:C(4),BANK_ACCOUNT_ID:C(6),DEPOSIT_NO:C(6)"
Let PKey.TRANSACTION_NO$="0000",PKey.BANK_ACCOUNT_ID$=ARCR.BANK_ACCOUNT_ID$
Let PKey.DEPOSIT_NO$=ARCR.DEPOSIT_NO$
Let DATABASE.Message_Flag=0
CALL DATABASE.NAME$+"::Extract",DATABASE$,DATABASE!,"ARCR",PKey$,ALTARCR$,ALTARCR_OLD$,ErrCode
If ErrCode=11 then
ALTARCR.PAYMENT_DATE=0,ALTARCR.AGING_PERIOD=0,ALTARCR.TRANSACTION_NO$="0000"
ALTARCR.BANK_ACCOUNT_ID$=ARCR.BANK_ACCOUNT_ID$,ALTARCR.DEPOSIT_NO$=ARCR.DEPOSIT_NO$
Else
if ErrCode>-1 then
Err_Msg$=DATABASE.Err_Message$
Call "ERROR.BBJ"
Fi
Fi
Query
This routine does a simple SQL Query on tables that are always SQL. To read tables that might be in BBj or SQL use "Read". If not found the Err_Code is 11, to match BBj.
Q$="Select County From zipc Where zip"+fnSQLChar$(aslc.zip$)
Call DATABASE.NAME$+"::Query",DATABASE$,DATABASE!,"zipc",Q$,record$,SQLE$,Err_Code
hmRecord!.add("County",Record.County$)
Write
One of the odd things about SQL from the Business Basic view is the seperation of Insert from Update. The Write command in bbj does either. At first I was going to try to force BBj files to work like SQL, but for various reasons I decided to use the same routine for insert, update, and delete old key then insert new record. The one thing this routine does not do is delete the old record if the new record is blank.This is the call statement for write. If the Failed_Flag comes back 1, then the write has failed, usually because of field change conflicts. You can then show them what's In_Table, so they can decide what to do.
Dim PKey$:"LDTRUCK:C(6),LD_DATE:N(7),LDLOAD:C(3),LDSTOCKNO:C(8),LDTYPE:C(1)"
Call DATABASE.NAME$+"::Write",DATABASE$,DATABASE!,"ASLD",PKEY$,ASLD$,ASLD_OLD$,In_Table$,
:Failed_Flag
This routine uses the PKey$ template to compare the keys in ASLD$ and ASLD_OLD$, and in bbj may delete the old key. It also compares the fields in ASLD, ASLD_OLD$, and what's presently in the table to see if the user is trying to change a field that's already been changed in the table.
Here's the same routine for a SQL table. Notice the query immediately before. If you're updating a record this routine expects to see the old record in COMPUTERS_OLD$. Also notice that if the record is not found, the primary key fields on the new record are updated. This way you just have to update the additional fields. If the primary key is an autosequence, this routine doesn't work for sql files. Use "Insert" instead.
When dimensioning PKey$, never specify a terminator. Instead of PKey$:"LCID:C(9*), use PKey$:LCID:C(9). I have to strip the CR's out of the keys.
Dim PKey$:"NAME:C(1*)"The Failed_Flag is from WriteRecord.Redo_Flag. It basically means that there was a field that was changed here and also changed on disk, so it has to be redone. This should be trapped somehow. However, if DATABASE.Message_Flag is set to 1, which is the default, WRITERECORD.BBJ will give a short error message..
PKey.NAME$=StatusBar!.getTextAt(2)
Call DATABASE.NAME$+"::Read",DATABASE$,DATABASE!,"computers",PKEY$,RECORD$,RECORD_OLD$,ErrCode
Record.Yard$=YardCode$
Call DATABASE.NAME$+"::Write",DATABASE$,DATABASE!,"computers",PKEY$,Record$,Record_OLD$,
:In_Table$,Failed_Flag
StatusBar!.setTextAt(3,record.yard$)
Transactions
If you have multiple tables to update you can use the transaction function. In this case I'm refreshing the entry screen with what's on file when there's a locking conflict.The question then becomes how to use the Failed_Flag. WRITERECORD.BBJ uses a bbj vector to store what records to write on a commit. If you commit it will write the good records and skip records where there's a conflict. So if the failure of one record should stop the writing of other records, you should catch it as below. It seems to be an odd way to implement a "commit", but it does give you more control over the results.
Call DATABASE.NAME$+"::Start_Transaction",DATABASE$,DATABASE!
Dim PKey$:"LDTRUCK:C(6),LD_DATE:N(7),LDLOAD:C(3),LDSTOCKNO:C(8),LDTYPE:C(1)"
Call DATABASE.NAME$+"::Write",DATABASE$,DATABASE!,"ASLD",PKEY$,ASLD$,ASLD_OLD$,ASLD_On_File$,Failed_Flag
Dim PKey$:"LCID:C(9)"
Call DATABASE.NAME$+"::Write",DATABASE$,DATABASE!,"ASLC",PKEY$,ASLC$,ASLC_OLD$,ASLC_On_File$,Failed_Flag
If Failed_Flag then
Let msg=MsgBox("Something was changed by another person. Try again.")
hmRecord!.put("ASLD",ASLD_On_File$)
hmRecord!.put("ASLC",ASLC_On_File$)
hmRecord!.put("bvASAT2",bvASAT2!)
Gosub DataCharges
Else
Call DATABASE.NAME$+"::Commit",DATABASE$,DATABASE!
hmRecord!.put("ASLD",ASLD$)
hmRecord!.put("ASLC",ASLC$)
hmRecord!.put("bvASAT2",bvASAT2!)
cdwCharges!.setVisible(0)
Fi
Insert
In some cases you want to add records to a bbj table, and you want to get an error if the record exists. Also, the Write function above needs a record_old$ to work, and on new records you're not going to have that.
This routine also works sql tables.
Dim PKey$:"ATSTOCKNO:C(8),ATNO:C(2)"
Call DATABASE.NAME$+"::Insert",DATABASE$,DATABASE!,"ASAT",PKey$,ASAT$,Failed_Flag
Delete
DeleteRecord
Use DeleteRecord as Delete is a reserved word."
This is mostly for REMOVEing records in bbj tables. The sql code is not
debugged, and I've
been using the Update Query for deletes in SQL tables.
Read and Delete are the only places where a value has to be set for
PKey. In Read all we
know at that point are the key values. With Delete it just seemed safer
and easier. The record to be deleted is in PKey$, since we don't
neccasarily have the record to delete If we do, we can set CheckRecord
to 1, and record could be compared to on file this is not presently
implemented.
Dim PKey$:"YARD:C(1),ZONE:C(17),COMPANY:C(6)"
PKey.YARD$=ASTZ.YARD$,PKey.ZONE$=ASTZ.ZONE$,PKey.COMPANY$=ASTZ.COMPANY$
MaxDelete=1,MinDelete=1,CheckRecord=1
Call DATABASE.NAME$+"::Delete",DATABASE$,DATABASE!,"ASTZ",PKey$,ASTZ$,CheckRecord,MaxDelete,MinDelete,NumDeleted,ErrCode
Update Query
With SQL files you often just want to update a field or two. It's also used for special SQL updates, inserts, and deletes that don't quite fit in with the read/write or vector routines This is especially for transaction and tracking files.
If Err_Code<>-1 then the actual rows updated is different than ToUpdate. If ToUpdate is 0 it assumes you actually meant 1. A -1 for ToUpdate means it doesn't do this check.
Q$="Update towinvhd Set Paid='N' where Invoice_No="+grdPYPayments!.getCellText(PaymentRow,0)
ToUpdate=-1
Call DATABASE.NAME$+"::Update_Query",DATABASE$,DATABASE!,"towinvhd",Q$,ToUpdate,Updated,SQLE$,Err_Code
ResultSet Query
Well, it turns out that using the java.sql classes are very easy. A big
advantage with using java ResultSets to access a sql database is that
you avoid the limitations of the bbj templates. I will probably be
using resultsets much more in my programs.
Be sure to close objects with "con!.close()", "stmtNotes!.close()"
and "rsNotes!.close()" when finished.
Here's the setup.
Q$="Select * from notes where Category="+fnSQLChar$(Category$)+" and Subject="+fnSQLChar$(Subject$)
Call DATABASE.NAME$+"::ResultSetQuery",DATABASE$,DATABASE!,"notes",Q$,con!,stmtNotes!,rsNotes!,Err_Code
And this is how you would access the ResultSet in your program. You can find all the ResultSet methods at http://java.sun.com/j2se/1.4.2/docs/api/. Select java.sql in the upper left hand box.
While rs!.next()
ts!=rs!.getObject("TimeEntered")
If rs!.getInt("Public") then
NewNote$="P-"
Else
NewNote$=""
Fi
NewNote$=NewNote$+rs!.getString("Author")+" "+new java.text.SimpleDateFormat().format(ts!)
NewNote$=NewNote$+fill(10,"*")+$0a$+rs!.getString("Note")
If Len(NOTE$)>0 and NOTE$(LEN(NOTE$),1)=$0A$ then
NOTE$=$0A$+NOTE$+NewNote$
Else
NOTE$=NOTE$+$0A$+NewNote$
Fi
Wend
rs!.close()
stmt!.close()
con!.close()
Open old style channels.
Well, sometimes you just can't avoid having a few READ RECORDS or WRITE RECORDS in your code. Also, as you update a system you might have little common programs that need files open on the old channels to work.
Dim FILE[10]
Call DATABASE.NAME$+"::OpenOldFILEArray",DATABASE$,DATABASE!,FILE[ALL],"ASTB",ASTB$,10,Err_Code
Installing Database.pub.bbj
First, you need to install mysql, which you can obtain from www.mysql.com. Then you need to dowmload the JDBC driver from there, which is currently called mysql-connector-java-3.0.10-stable-bin.jar. Go into Enterprise Manager for your bbj server and add that jar to the classpath. Then make these entries to sql.ini. Either make a database called ASP in mysql, or change asp below to your new database name in mysql.[JDBC Drivers]You then need to add this line to config.bbx
com.mysql.jdbc.Driver
[BASIS Data Sources]
mysql
[mysql]
url=jdbc:mysql://localhost:3306/asp
SQL=c:/asp/asp5/configs/sql.iniUnzip and add these files to the "data" directory on your mysql machine.
Because of the slowness in speed to get file definitions out of BBj, I run tplMySQL.BBj to grab the templates from bbj and put them into mysql. Any bbj files have to be setup with ddbuilder or enterprise manager, then tplMySQL ran to transfer these templates over to mySQL. The two main programs are DATABASE.PUB.BBJ and WRITERECORD.BBJ. All three programs are here.
