Using the Windows Clipboard in FoxPro
By · CommentsThe Windows Clipboard in easily accessed in FoxPro and Visual FoxPro (VFP). There are currently two commands that directly relate to it. These are _CLIPTEXT to copy and paste text and DataToClip() to copy values from a table. These are not highly sophisticated commands, but they are quick and straightforward and are often the simplest way to transfer data between applications.
Let us explore these two VFP commands further and provide some examples:
_CLIPTEXT System Variable
The _CLIPTEXT System Variable contains the content of the Windows Clipboard and can be engaged to write text to and read text from the Clipboard. This feature has been available since the earliest versions of FoxPro for Windows. The syntax is very simple:
_CLIPTEXT = cExpression && writes cExpression to the Clipboard cExpression = _CLIPTEXT && stores the Clipboard content to cExpression
One of the most common uses of _CLIPTEXT is during program development. Long SQL statements, commands and other strings generated by programs at runtime are often difficult to debug using VFP’s standard tools. In the following code sample an SQL statement is copied to the Windows Clipboard so the developer can paste it into a wordprocessor or other application for additional analysis:
cSQL = cFields + cSource +cFilter + cOrder + cOutput _CLIPTEXT = cSQL SET STEP ON && opens the Trace window and suspends the program
DataToClip Method
DataToClip() copies a set of records to the Windows Clipboard. It is a method of the Application Object or the _VFP System Variable. The field names appear as the first line of the text copied to the Clipboard followed by a separate line for each record. This method has been available since VFP 5 and is only slightly more complicated:
Syntax
ApplicationObject.DataToClip([nWorkArea | cTableAlias] [, nRecords] [, nClipFormat])
Arguments
- nWorkArea
- Specifies the work area number of the table for which records are copied to the Clipboard. If you omit cTableAlias and nWorkArea, records are copied to the Clipboard for the table open in the current work area.
- cTableAlias
- Specifies the alias of the table for which the records are copied to the Clipboard.
- nRecords
- Specifies the number of records copied to the Clipboard. If nRecords is greater than the number of remaining records in the table, all the remaining records are copied to the Clipboard. If nRecords and nClipFormat are omitted, the current record and all remaining records are copied to the Clipboard.
- nClipFormat
- Specifies how fields are delimited. The settings for nClipFormat are:
-
nClipFormat Description 1 (Default) Fields delimited with spaces 3 Fields delimited with tabs - If nClipFormat is omitted, fields are delimited with spaces.
This following copies the current work area from the current record to the end in tab-delimited format:
_VFP.DataToClip(,,3)
This following copies the current work area from the current record to the end in space-delimited format:
_VFP.DataToClip()
There are some important limitations with the DataToClip Method. Memo field content and General fields are not copied, there is no way to apply a filter or select which fields will be copied, and bugs have been found in the method’s implementation in VFP 5 and VFP 6. Also note executing DataToClip() does not move the record pointer.
4th of July Sale
By · Comments
Our 12-day 4th of July Sale begins today at www.PeacockData2.com . . . 25% discount on all database products and developer licenses!
See you there!
What is Encryption and Why Use It?
By · CommentsEncryption is the process of transforming information using an algorithm (called a cipher) to make it unreadable to anyone except those possessing a special key to unlock the information. The unlocking process reverses the encryption and is know as decryption.
Encryption has long been used by militaries, diplomats and governments to facilitate secret communication, and it is now commonly used to protect information within many kinds of civilian systems. According to a 2007 report by the Computer Security Institute, 71% of the companies surveyed utilize encryption for some of their data in transit while 53% utilized encryption for some of their data in storage.
In recent years the use of encryption has increased substantially as more and more information has become accessible through the internet both while at rest on servers and while in transmission from one network to another. The internet is a wonderful resource that allows us to communicate instantly with anyone, virtually anywhere—but it has also allowed access to cyber criminals and electronic hackers who can steal millions of dollars in seconds.
While it is still safest to keep sensitive information on removable storage media such as portable external hard drives and flash memory drives, these can be lost, and this is not a practical security solution in many situations.
Encryption is particularly important for protecting credit card numbers, Social Security numbers, passwords, personal identification numbers (PINs), client lists, business plans and corporate intelligence. However, for many it is also important for concealing political dissent as well as opposition in the workplace.
The best known encryption is Pretty Good Privacy (PGP) developed by Philip R. Zimmermann, Jr. Originally designed as a human rights tool, PGP was published for free on the Internet in 1991. It is now owed by PGP Corporation (a part of Symantec).
There is no known method which will allow someone to break PGP encryption, and it has been described by computer security specialist Bruce Schneier as “the closest you’re likely to get to military-grade encryption”. Nevertheless, early versions of PGP have been found to have theoretical vulnerabilities, so new versions have been developed or recommended.
PGP and similar products follow the OpenPGP standard for encrypting and decrypting data. Defined by the OpenPGP Working Group of the Internet Engineering Task Force (IETF) in Proposed Standard RFC 4880, OpenPGP is today the most widely used eMail encryption standard in the world.
In most cases the impact of unwanted disclosure of information is negligible—but NOT ALWAYS! Do not discount encryption as too difficult, time consuming or expensive. It is not and in some cases is required by law. There is a good chance one day you will be glad you utilize it.
No Saturday Mail Delivery Inches Closer
By · CommentsMany of our clients are extensively involved in mailing, and we would like to keep you up-to-date on an issue about which you have expressed considerable concern.
The U.S. Postal Service (USPS) has now taken its case for eliminating Saturday mail delivery to the Postal Regulatory Commission (PRC). This is the first step in the review process required by law any time a nationwide change in postal service is proposed.
According to a report recently issued by the USPS, they are facing unprecedented volume declines and a projected $238 billion shortfall during the next decade. It cites five-day delivery as one of the fundamental changes that will help the USPS better respond to changing customer needs and reduce its deficit.
Technological and social changes have altered the way Americans communicate, and electronic media have replaced the letter as the primary means of business and social communication. Revenue from First-Class Mail—the Postal Service’s longtime bread-and-butter product—continues to decline as the use of electronic message delivery increases.
Last year mail volume plummeted by 25.6 billion pieces—a nearly 13 percent fall—resulting in a USPS revenue drop of nearly $7 billion. The trends contributing to these declines are expected to continue.
Postmaster General John E. Potter says the change will help rebalance postal operations with the needs of today’s customers and will save more than $3 billion a year, including reductions in carbon emissions and energy use.
Potter emphasizes that the proposal dealt only with Saturday street delivery and that postal offices will be open on Saturdays, access to PO Boxes would continue, Express Mail would be delivered seven days a week and incoming mail would continue to be processed.
PRC Chairperson Ruth Y. Goldway is inviting public comment on the proposed change. You can share your views at the Commission website by clicking the “Contact PRC” tab at the top. You can also (somewhat ironically) write them at: Postal Regulatory Commission, ATTN: Office of Public Affairs and Government Relations, 901 New York Ave NW, Ste 200, Washington, DC 20268-0001.
The USPS plans to implement five-day delivery in fiscal year 2011, however, even if the PRC goes along with the change, the U.S. Congress could enact legislation requiring continuation of six-day mail delivery.
Using the pdNickname RELFLAG Field
By · CommentspdNickname is a unique nearly 50,000 record database designed to facilitate comparing sets of first name data based on nicknames, diminutives, pet names, variations and given names. One of the most important fields in the database product is RELFLAG, which stands for “Relationship Flag”.
The RELFLAG field contains one of two possible values:
-
Close relationship between the name and variation (common variants):
Includes closely associated nicknames, diminutives and pet names as well as first name variations that are considered closely related.
-
More distant relationship between the name and variation (less common variants):
Includes alternate forms of the names, often deriving from another culture, as well as nicknames, diminutives and pet names that are relatively uncommon.
PDNICKNAME VARIATIONS FOR THE GIVEN NAME “SAMUAL”
The RELFLAG field indicates if the name and variation have a (1) close or (2) more distant relationship.
The RELFLAG field is useful for controlling what is to be considered an acceptable match. As more distant relationships are included in matches, the error rate naturally rises. The error rate increase is usually not substantial, but it is measurable in hundredths and tenths of a percent.
RECOMMENDATIONS
RESIDENTIAL LISTS: While additional accuracy can be achieved if only close relationships are considered, with residential lists, the margin of error rate increase is almost always very small even when the more distant relationships are included—rarely more than 0.02% in our testing. Therefore, under best practices, it is fully acceptable to use all RELFLAG relationships when matching residential lists. With the exception of the George Foreman family, most errors that might occur result from different given name that share the same nickname or other variation.
BUSINESS & ORGANIZATION LISTS: On the other hand, with business and organization lists, when the more distant relationships are included the margin of error rate increase is typically higher, compared to residential lists. However, our testing normally shows an increase that is still less than 0.1%, but we have seen it as high as 0.3% with some large lists. Under best practices, it is recommended that only close relationships be considered when processing business and organization lists.
Keep Database Content Consistent
By · CommentsDatabases and spreadsheets are great for organizing information, but to get the most out of them, content must be entered in a consistent manner. Too often data entry results in a mishmash of styles and formatting. For example, one record may have the name entered first name first while the next has last name first. Or some telephone numbers may be entered with parenthesis delimiting the area code while others employ a hyphen or slash. Etc.
It is important for database managers, usually in conjunction with interested associates, to develop rules for entering information into their tables. Inconsistencies make it difficult filter, sort, query, merge and process the database records and can even lead to data corruption and security vulnerabilities. Data entry rules can take the form of a written style guide, preprogrammed validation routines or a combination of both.
STYLE GUIDE
A style guide (sometimes called a style manual) is a written set of standards for entering content. It is meant to foster uniformity in style and formatting by providing rules indicating how each data element is to be handled. These elements can include names, addresses, telephone numbers, dates of birth and any other fields and field sets that would benefit from constancy. Below is an example of part of a style guide:
| Data Element | Style Requirements | Example |
|---|---|---|
| Customer name | Include. Enter first name first. Exclude title. Punctuate initials and suffix abbreviations. | George E. Jones, Jr. |
| Home telephone | Include. Format: (###) ###-####. | (800) 609-9231 |
| Fax |
Include, if applicable. Format: (###) ###-####. |
(818) 480-4391 |
| Web address | Include with http://. | http://www.peacockdata.com |
A style guide does not have to be exhaustive, only comprehensive enough to advance sufficient standardization. Depending on the situation, one or two pages is often enough, while other circumstances may require a book-sized document. In any case, style and formatting rules must be clear and readable to be effective, and the inclusion of examples has proven to be extremely helpful for end users.
VALIDATION ROUTINES
In addition to or instead of written guides, style and formatting rules are often enforced with validation routines built directly into the interfaces used for data entry. These make sure information is valid, reasonable and secure while at the same time further reduces the opportunity for user error.
Some validation routines are automated so input is restricted at the time of data entry. The most common of these is an input mask which sets a template or pattern for a field or field set that automatically formats entered content. Input masks are particularly suitable for telephone numbers, ZIP and postal codes, times and dates.
Other validation routines are programs set to run when an end user tries to exit a field or save a record. These are similar to (or often exactly like) scripts that check content entered in web forms. Some routines are simple and may only guard against invalid characters and missing information, while others may be very complex and even check spelling and grammar.
• • Data entry rules and preprogrammed validation routines are an important part of data management, and taking time to tackle this part of the development process will inevitably reap generous rewards.
Meet the ISO
By · CommentsOur pdCountry database product includes information derived from ISO – International Organization for Standardization – standards and publications. We though you would like to know more about the organization, so meet the ISO:
Founded February 23, 1947, the ISO is a network of the national standards institutes of 159 countries, with one member per country. Its Central Secretariat, which coordinates the system, is headquartered in Geneva, Switzerland, and English and French serve as the official languages.
ISO is not an acronym in either of the organization’s official languages and the letters are not delimited with periods like initials. The short-form name is based on the Greek word ἴσος (isos in English), meaning equal. Fitting, since their mission is to equalize and standardize across nations and cultures.
The ISO is the world’s largest developer and publisher of international standards. While not a government organization, it has strong links to governments, and the ISO’s stature gives it the ability to set standards that often become law, either through treaties or national standards.
The organization’s work helps improve international collaboration and communication and promotes steady and equitable growth of international trade. ISO standards encompass most technical and nontechnical fields and affect aerospace, alphabetization and transliteration, chemistry, design, engineering, fuels, image technology, machines, manufacturing, measurements, medical equipment, methods of testing, production, specifications for parts, shipbuilding and tools, among other areas. Most of the standards are reviewed and optimized every five years.
ISO standards are identified in the format:
ISO nnnnn:yyyy: Title
where nnnnn is the number of the standard, yyyy is the year published and Title describes the subject.
EN ISO nnnnn is the European version of the international standard, and BS EN ISO nnnnn is the British variation. Some standards are issued in collaboration with other organizations and are identified accordingly.
Standards not covered by the ISO include electrical and electronic engineering, which is the responsibility of the International Electrotechnical Commission (IEC).
In addition to standards, the ISO also publishes technical reports, specifications, corrigenda and guides. Their documents are copyrighted and they usually charge for copies, however, the organization does not charge for most draft copies delivered in electronic format. Note that drafts are often substantially modified before they are finalized as standards.
ISO standards supplied with our pdCountry database product include:
- ISO 3166-1 alpha-2 – two-letter country code
- ISO 3166-1 alpha-3 – three-letter country code
- ISO 3166-1 numeric – three-digit country code
- ISO 4217 currency code
The product also provides data from the International Telecommunication Union (ITU) and other information sources.
How to Zip & Unzip Files
By · CommentsWe receive and send a lot of files as part of our data processing services, and some of our clients are unfamiliar with how to zip and extract files. The following instructions show how to compress and uncompress a file under the Windows, Mac and Linux operating environments. Note than under most systems you can select multiple files as well as folders/directories to zip into the same archive.
Zipping a file creates a compressed version of the file that is usually considerably smaller than the original file. The zipped version of the file has a .zip file extension.
Unzipping a file reverses the zip process and extracts the file from the compressed archive.
IMPORTANT: Please be cautious about opening .zip files from unknown e-mail senders because they can contain viruses. Confirm with known senders before opening a .zip file.
WINDOWS ME, XP, SERVER 2003, VISTA, SERVER 2008 and 7
Compress files (zip files)
- Locate the file you want to compress.
- Right-click the file; then point to Send to and click Compressed (zipped) folder.
A new compressed zip file is created in the same location.
Uncompress files (unzip files)
- Locate the compressed zip file you want to extract.
- Do one of the following:
- To extract a single file or folder, double-click the zip file to open it; then drag the file or folder from the archive to a new location.
- To extract the entire contents of the zip file, right-click the zip file; then click Extract All; then follow the instructions on the screen.
Windows 95, 98, 98SE and 2000 do not have built in zip file support, and it is necessary to utilize third-party software to create and extract zip files.
MAC OS X (10–10.4)
Compress files (zip files)
- Use Finder to locate the file you want to compress.
- Control-click or right-click the file icon; then click Compress [. . .]
A new compressed zip file is created in the same location.
Uncompress files (unzip files)
- Use Finder to locate the compressed zip file you want to extract.
- Double-click the file icon.
The files contained in the archive will be extracted to the same location.
MAC OS X (10.5–SNOW LEOPARD)
Compress files (zip files)
- Use Finder to locate the file you want to compress.
- Control-click or right-click the file icon; then click Create Archive of [. . .]
A new compressed zip file is created in the same location.
Uncompress files (unzip files)
- Use Finder to locate the compressed zip file you want to extract.
- Double-click the file icon.
The files contained in the archive will be extracted to the same location.
LINUX
Compress files (zip files)
- Open a shell prompt.
- Enter the following:
zip -r filename.zip filedirA new compressed zip file is created in the selected location.
Uncompress files (unzip files)
- Open a shell prompt.
- Enter the following:
unzip filename.zipThe files contained in the archive will be extracted to the same location as the zip file.
Making a Data Dictionary
By · CommentsA data dictionary is a document that catalogs the organization, contents and conventions of a database or collection of databases. It lists in written form all the databases, tables, views, fields and data definitions and often information about the table layouts, the relationships between tables and other details about the database schema.
It does not contain the actual data from the database system, only information necessary to manage and utilize it. It is also not an instruction manual, though a data dictionary is often included as part of an instruction manual.
There is no universal standard as to the level of detail in a data dictionary. What is included is dependent on the audience and the complexity of the database infrastructure. System administrators and programmers will usually have a highly detailed document, sometimes complete with visual depictions, while end users may only have the basics.
Below is an example of a data dictionary for a bookkeeping database with three tables. It shows the kinds of information typically included in a data dictionary, however, it is not meant to be all-inclusive. Other columns that might be provided could show if a field takes null values and the precise points where each field begins and ends. If scientific or technical information is involved, a column indicating normative ranges may be useful. The possibilities are myriad.
A data dictionary is an important part of database system documentation. Devoting the resources needed for a quality document will help insure fewer problems and significantly aid in productivity.
EXAMPLE DATA DICTIONARY FOR A BOOKKEEPING DATABASE
Number of Tables: 3
Table: name of the table. Field: name of the field. Rel: Table relationship key (if any); PK = primary key, FK = foreign key; see Foreign Key Relationships. Type: field data type. Width: field width. Dec: number of decimal points (if any). Description: data definition of the field contents.
Foreign Key Relationships: (1) points to Customers table Id field. (2) points to Sales table Invoice field.
| Table | Field | Rel | Type | Width | Dec | Description |
|---|---|---|---|---|---|---|
| CUSTOMERS | ID | PK | Character | 10 | Customer ID number | |
| NAME | Character | 25 | Customer name | |||
| CUST_TYPE | Character | 1 |
Customer type (key):
A = Active
I = Inactive P = Prospect |
|||
| TERMS | Character | 1 |
Payment terms (key):
N = Net Due
P = Prepaid |
|||
| SALES | INVOICE | PK | Character | 4 | Invoice number | |
| CUST_ID | FK (1) | Character | 10 | Customer ID number | ||
| SAL_DATE | Date | 8 | Date of sale | |||
| SAL_AMOUNT | Numeric | 10 | 2 | Amount of sale | ||
| RECEIPTS | ID | PK | Character | 10 | Unique ID number | |
| INV_NUM | FK (2) | Character | 4 | Invoice number | ||
| REC_DATE | Date | 8 | Date of receipt | |||
| REC_AMOUNT | Numeric | 10 | 2 | Amount of receipt |
CASS vs. DSF2
By · CommentsOne of the questions we are most often asked concerns the difference between CASS and DSF2 address/mail processing. These services have grown more similar in recent years, but there are still important differences.
CASS
CASS – Coding Accuracy Support System – standardizes addresses and improves the accuracy of carrier route, five-digit ZIP, ZIP+4 and delivery point codes that appear on mail pieces. When mailers CASS certify their lists, they can become eligible for bulk mail discounts from the U.S. Postal Service (USPS).
In the past, applying these codes only required that mailing address fall within a valid address range. The address itself was not necessarily accurate. For example, ZIP+4 codes typically represent a range of 100 or fewer house numbers on a given street. While 100-199 Rollins Road may be assigned to ZIP+4 code 91311-7007, not all those house numbers are actual delivery points.
Since November 2, 2006, the USPS has determined that CASS-related discounts will only apply when the agency’s Delivery Point Validation (DPV) process confirms the primary number—or the first line—of the addresses. If the primary street number of an address cannot be confirmed with DPV, then no ZIP+4 code is assigned to the mail. Without a ZIP+4 code, the mail ceases to qualify for postal discounts. DPV is therefore now included with CASS processing.
CASS also integrates Locatable Address Conversion System service (LACSLink) which converts some PO Box and rural route addresses to city-style addresses and updates addresses that have been renumbered or renamed.
CASS processing software is graded by the USPS National Customer Support Center (NCSC), located in Memphis, Tennessee, by processing a test file. To be certified a 98.5% accuracy rate is required.
DSF2
Prior to the November 2006 requirements added to CASS processing, DSF2 – Delivery Sequence File Second Generation – was the only fail-safe method of checking the accuracy of mailing addresses since CASS only determined if the address fell within a valid address range. But DSF2 continues to offer advantages that can make it a good choice.
In addition to all the processing and validation supplied with CASS, DSF2 also provides mailers with other important address attributes. It classifies an address as residential or business, identifies the delivery type—curb-side, door-slot, Neighborhood Delivery and Collection Box Unit (NDCBU) or central delivery—and flags seasonal and vacant addresses.
This information allows for more targeted mailings. For example, a company may want to send out a sample box of laundry detergent—with DSF2 they can target their mailing to residential addresses that do not have a door-slot mailbox since the sample would not fit through the slot.
DSF2 also appends codes that can be used to qualify for USPS Enhanced Carrier Route (ECR) Basic, High Density and Saturation postal discounts. These include Pseudo Delivery Sequence Numbers for walk sequence postal presort discounts and Enhanced Line of Travel codes (eLOT).
• • CASS and DSF2 have similarities and important differences. The choice is determined by the targeting required and the postal discounts that can be qualify for. In both cases, however, users will typically benefit from a significant reduction in undeliverable mail, speedier delivery and lower costs.
