Access Denied

Pondering the difficulties of database access and the limitations of Microsoft Access.

Other consultants get to go to Hawaii for vacations, but not me. I went to Sweden to teach SQL classes. Regular readers will remember the extent of my Portuguese in Brazil was the ability to order a light bulb. If I had even attempted Swedish, I am sure that I would have ordered a Saab or a Volvo, those being the only words I know. For me, Swedish is strong cryptography.

I did not even bother getting a guide book or phrase book for the trip. I learned everything I needed to know about Sweden from Web sites, which have a lot of English. I found out that the weather was not going to be as bad as I first thought. This was very useful information to a man whose only heavy coat is a surplus military-issue field jacket.

SmartCards and Web Databases

I can glimpse into a future where Web databases are the standard way that people inside and outside a company get corporate information. While I would hardly classify Sweden as a business, having separate pages in English and Swedish was one way to control access to data on Web sites that locked me out of certain sections. My solution to the problem of Web access has been strong cryptography in SmartCards because it works so well in the Nordic countries for email. I am rethinking this position as a result of an incident that occurred recently, but I do not yet have another solution.

NationsBank ATM machines at two Kroger supermarkets ate three of my bank cards. The bankýs attitude was that because I wasnýt a customer, it was my problem and I should call my bank. Well, I had done that, and my bank, which Consumer Union rates as the best bank in America, assured me that there was no problem on my end. I also talked to a friend who banks with NationsBank, and she had lost five cards in one year due to ATM errors.

I replaced those ATM cards in two weeks, but what would I have done if the ATM machine had scrambled the data on a SmartCard and locked me out of every service I accessed with the card? It reminds me of an old science fiction story written as a series of letters between a man and the billing computer at a book club. He attempts to return an unwanted copy of Robert Louis Stevensonýs Kidnapped and winds up charged with the kidnapping and murder of Stevenson.

Trademarks Online

Of course, the best way to deny people access to databases on the Internet is simply never to put them onto the Internet, even when it is something to which they should have access, such as trademarks.

One U.S. official who does not lie about the ability to put trademark information on the Internet is Philip G. Hampton II, Assistant Commissioner of Trademarks. He spoke on October 3, 1997, to the Patent and Trademark section of the Oregon State Bar. When asked why trademark data was not made available on the Internet, he responded candidly that to do so would be "politically incorrect." He said that businesses benefit financially from the limited availability of the information. If this information was freely available, these companies would go squealing to their representatives in Congress, who would give hell to the responsible official.

The federal trademark data is only 2GB and could be placed on the Internet in a week or less. You might want to fax a letter to Mr. Hampton at 703-308-7220, or even better, give him a call at 703-308-8900 and remind him that he works for the entire country, not just trademark data vendors.

The European Patent Office will have full databases on the Internet this year. The Canadian Patent Office already has a copyright and patent database on the Internet. And the U.S. Copyright Office has both a full database on the Internet and accepts copyright applications electronically.

Not only are all Japanese patents available online, but you will also be able to file them electronically later this year. One leading Japanese word processor company tells you how to do this using its word processor (in Japanese at www.justsystem.co.jp/ichitaro/patent). If you can do this in Japanese, why canýt you do it in English?

Junk Email Law

While I am still annoyed with legal problems on networks, I was pleased to learn that there was the possibility of some relief from junk email at the end of 1997. Representative Chris Smith (R-N.J.) sponsored legislation that extends the prohibition on junk fax-mail to junk email. You can get a copy of H.R.1748 from thomas.loc.gov. It is called "Netizens Protection Act of 1997," and it amends the Communications Act of 1934, banning the transmission of unsolicited advertisements by electronic mail. It requires that sender identification information be included with electronic mail messages (in much the same way that a fax must carry identification under current law).

SQL Wants You!

If you are willing to look up some useful information on your own instead of waiting for email to show up, you might want to visit the Whitemarsh Information Systems Corp. Web site at www.wiscorp.com, where Michael Gorman, secretary of NCITS H2 Database Standards committee for a million years, has posted information.

Problems with Oracle Lite

Rae MacLeman is a student going for a Masters in Information Technology who sought to obtain an evaluation copy of Oracle that would run under Windows 95. MacLeman visited the Oracle Web site at www.oracle.com/clientdb/olite and noted that some of the evaluation products, including Oracle Lite for Windows 95, are available on CD-ROM for $15 shipping. She called the Oracle office in Cincinnati and was given an 800 number in California. When she called, she was told that Oracle Lite was not available on CD. When she pressed the point and mentioned that the product was advertised on the Oracle Web site and in the Oracle store, she was told that the Web page was incorrect.

When she asked why Oracle was knowingly putting misleading data on its Web site, she was told by a very candid employee that it might just be to generate sales that otherwise would not occur.

Can anyone tell me if this is just a scam or whether or not these products are available on CD-ROM? Downloading more than 50MB from the Web just is not a practical option unless you have a ýbig pipe,ý or a great deal of time and luck.

[Editorýs note: We spoke with Mark Gaydos, Oracle Lite product manager, who assured us that the CD-ROM was available for a 60-day trial period; a $15 shipping and handling fee is required.]

XDB and Micro Focus

I was not the only person changing plans at Christmas time. On December 23, 1997, Micro Focus Group Ltd. (www.microfocus.com) announced it has entered into a definitive agreement to acquire privately held XDB Systems Inc.

Micro Focus makes a desktop Cobol, and XDB Systems makes a desktop version of DB2, so they are a logical match. In fact, several years ago George Shussel of DCI used to tell a story about a client who moved hundreds of thousands of lines of IBM mainframe Cobol and DB2 code to products from Micro Focus and XDB with only a dozen changes in the source code. Micro Focus and XDB have already had a working relationship for the past eight years. IBM estimates that DB2 is installed in 80 percent of the Fortune 500 companies and that these installations are supporting 30 million users. Industry analysts predict the amount of data stored in DB2 databases will double over the next two years. This looks like a good deal all the way around, and I am glad to see that XDB has been acquired by a stronger company that has no interest in gutting it or subverting its technology.

Porting Access to Other Platforms

At the start of 1997, I heard people at a vendor show talking about developing databases in Microsoft Access and then porting them to a larger system with more data. I know this does not work; I make money consulting for people who have tried it. However, someone in this situation who does not want me to use his name expressed his concerns to me in an email: "Yup, we are killing ourselves using Access for data of this size. I knew it when they talked about this system a year ago (ýOh, donýt worry! Weýre only doing our development in Access ý weýll use the upsizing tool to migrate as soon as we hit production!ý), and we certainly know it now (nine months into production)! I have spent the last three weeks scrubbing bad data in Access and adding rules/constraints/uniqueness, etc. to assure that it does not happen again. Our experience with the upsizing tool to SQL Server is that it will preserve all of these constraints."

If you use Visual Basicýs RDO feature to talk to Access during development and then to SQL Server during deployment, it will fail dramatically on your machine, even after days of fiddling. Apparently it works for some, but thereýs a steady stream of people on CompuServe and elsewhere having problems. How many different versions of odbcjt32.DLL are out there?

One reader ended up with a 12MB Access database that he had to move to SQL Server to make further progress. Well, you just use the File/Export, right? Wrong. That command lost the PRIMARY KEY constraints, lost the indexes, and left all columns NULL-able, so he could not manually define a PRIMERY KEY or declarative referential integrity.

He then discovered an "upsizing Wizard" from Microsoft for just this purpose, which is not on the office disk; he downloaded it free from the Web site. Of a dozen tables, 10 moved without error, PRIMARY KEY preserved, and all their data moved. Great! The two tables that did not move were his largest tables, one with 11KB and the other with 75KB.

The first table gave him a "ran-out-of-locks" error because he was running the default SQL Server configuration of 5KB for locks, and the Wizard tried to move all the rows in one transaction in a way it apparently needs one lock per row. He used the Wizard to upside the schema and did a loop of INSERT INTO statements to move the SQL table attached to Access in 5KB chunks.

The INSERT INTO statements still failed, complaining that one column could not contain a NULL. Well, the SQL Enterprise Manager table widget said the column was NULL-able. My reader figured it was an ODBC glitch and tried pass-through queries and other things until he lucked into looking at the trigger declarations. No rules or stored procedures were involved and none was defined for the database. He is new to SQL Server and did not realize triggers are stored elsewhere! The Wizard configured a column in the table as NULL-able, wrote a trigger to prevent NULLS from being inserted, and gave "error #44444" when it was attempted. When he removed those lines from the triggers, all was well.

Puzzle:

This problem came up on the Oracle forum on CompuServe, but as far as I know it cannot be solved in Oracle in one statement. You have a table with sequential primary key column and some other columns. We need to add a working table with data to the first table. For example, the two tables might look like this:

CREATE TABLE TestLogBook 
(seq INTEGER NOT NULL PRIMARY KEY,
 test_name CHAR(5) NOT NULL, 
 answer CHAR(8) NOT NULL);
INSERT INTO TestLogBook VALUES (1, ýtestaý, ýyesý); 
INSERT INTO TestLogBook VALUES (2, ýtestbý, ýnoý); 
INSERT INTO TestLogBook VALUES (3, ýtestcý, ýmaybeý);

CREATE TABLE WorkingTable
(test_name CHAR(10) NOT NULL,
 answer CHAR(10) NOT NULL);
INSERT INTO WorkingTable VALUES (ýtestdý, ýyesý); 
INSERT INTO WorkingTable VALUES (ýtesteý, ýnoý); 
INSERT INTO WorkingTable VALUES (ýtesteý, ýnoý);
 

Oh, there is a little problem with the working table. Sometimes the data entry clerks put in the same data twice, so you can have redundant, duplicate rows. That is, the extra row with ('teste', 'no') in the sample data is an error that you need to ignore.

Can you do this in one INSERT INTO statement and avoid writing a procedure with a cursor?

INSERT INTO TestLogBook (seq, test_name, answer) 
SELECT DISTINCT (SELECT COUNT(DISTINCT T2.test_name) 
                                        FROM WorkingTable AS T1, WorkingTable AS T2
                                    WHERE T1.test_name >= T2.test_name
                                        AND T1.test_name = T3.test_name) 
                                 + (SELECT MAX(seq) FROM TestLogBook),
            T3.test_name,
            T3.answer
FROM WorkingTable AS T3;