PxPlus User Forum

Twitter Twitter Twitter

Author Topic: Database Conversion Utility  (Read 1484 times)

Thomas Bock

  • Diamond Member
  • *****
  • Posts: 129
    • View Profile
Database Conversion Utility
« on: June 26, 2019, 10:29:07 AM »
I took a look at the new DCU. Here are some wishes concerning this tool.

  • There should be a way to control the decimals for numeric values. A field defined with PRICE:[LEN(10)] may be DECIMAL(9,2) in a SQL-table. Perhaps the print format can be used for that.
  • There should be mapping rules for datatypes. A padded string field should be CHAR instead of VCHAR and a delimited string field with >100 bytes should be text instead of VCHAR. Just to mention a few.
  • There is no way to use company numbers in table names, like the C$ in the ODBC-DD. We would like to use an expression like in the definition of the physical filename.
  • It should be possible to define a standard for the connection values and the name of the prefix file.
And this one seems to be an issue:
The standard option DATEFMT=JUL-0000 doesn't work. We use julian values, but after loading the table there are nothing but NULL values in the date fields.

Len Vassos

  • Staff
  • Gold Member
  • *****
  • Posts: 82
    • View Profile
Re: Database Conversion Utility
« Reply #1 on: June 26, 2019, 10:47:49 AM »
Thomas,

We will look into the issue with DATEFMT and will review your wishlist items.
PVX Plus Technologies, Ltd.

Mike King

  • Administrator
  • Diamond Member
  • *****
  • Posts: 3403
  • Mike King
    • View Profile
    • PVX Plus Technologies
Re: Database Conversion Utility
« Reply #2 on: June 26, 2019, 10:55:59 AM »
Thomas

Note that a numeric defined in the PxPlus data dictionary with a length of "10" will be assumed to have no decimals.  You should have defined it in the PxPlus data dictionary as 10.2 or similar.

If you don't specify any decimals in the PxPlus data dictionary then our ODBC interface will also report no decimal which could cause trouble for other programs.

Stéphane Devouard

  • Gold Member
  • ****
  • Posts: 56
  • Once PxPlus guru, reskilled in C#, Java, PHP, JS
    • View Profile
    • Stéphane's Web Resume
Re: Database Conversion Utility
« Reply #3 on: June 27, 2019, 02:11:35 PM »
Len,

I second Thomas wishes.

**EDIT**
Other ones I’ve run into today :
- varchar(max) is not an allowed syntax with a MySQL database and should be replaced by text
- If you have named your primary keys in the data dictionary (such as PK_TableName), the generated primary key constraint is PK_TableName_TableName. This could probably be improved.

Regards
« Last Edit: June 28, 2019, 01:44:25 AM by Stéphane Devouard »
Stéphane Devouard
Portfolio | Company

Thomas Bock

  • Diamond Member
  • *****
  • Posts: 129
    • View Profile
Re: Database Conversion Utility
« Reply #4 on: June 28, 2019, 02:46:56 AM »
I want to modify my #3.

There should be a possibility to dynamically define the company number and year like in the ODBC-DD.
And there should be a possibility to dynamically define the complete connection data. So in case of changes on the DB-side we can just reconfigure these values in our application parameters. This will avoid the need to rewrite the complete prefix file.

Mike King

  • Administrator
  • Diamond Member
  • *****
  • Posts: 3403
  • Mike King
    • View Profile
    • PVX Plus Technologies
Re: Database Conversion Utility
« Reply #5 on: June 28, 2019, 06:31:44 PM »
Thomas,

A couple of thoughts about making the connection more dynamic;

If you are using ODBC then you simply need to specify a DSN which can then be changed on the server/workstation.  This would be the preferred method when running on Windows

For non-ODBC connections most of the value provided in the OPT= string can be set in the INI file so things such as Host name, port, userid and password can be placed in the INI file as opposed to the PREFIX file.  This allows you to leave most setting out of the Prefix file so simple changes can be made to the INI file.

Stéphane Devouard

  • Gold Member
  • ****
  • Posts: 56
  • Once PxPlus guru, reskilled in C#, Java, PHP, JS
    • View Profile
    • Stéphane's Web Resume
Re: Database Conversion Utility
« Reply #6 on: June 29, 2019, 01:09:50 PM »
Another issue that could be improved in the DCU

Setup (Windows 10 system):
  • myapp
    => folder that contains the data dictionary files, and a prefixdb prefix file
    => in the dictionary, each logical table name is associated with data/physical as the physical pathname
  • myapp\data
    => folder that contains the ProvideX data files

When using the DCU, the prefix file entry is created as DATA\PHYSICAL (since the key has the uppercase option set)

Therefore, if you do something like :

Code: [Select]
OPEN DICTIONARY
PREFIX FILE "prefixdb"
OPEN (nn) TABLE "logical"

ProvideX translates logical to data/physical (as set in the dictionary)
And thus does not find the entry in the PREFIX FILE and does not open the SQL table but rather the keyed file

Two possible solutions :
  • Either create the PREFIX FILE entry the same as in the dictionary
  • Or create 2 entries (DATA/PHYSICAL and DATA\PHYSICAL)

Thanks,
Stéphane Devouard
Portfolio | Company

Thomas Bock

  • Diamond Member
  • *****
  • Posts: 129
    • View Profile
Re: Database Conversion Utility
« Reply #7 on: July 04, 2019, 02:02:03 AM »
As it turns out, DATEFMT=DDMMYY seems to be an issue as well.
In this case the date columns have values, but they are all wrong.

311218 becomes 0013-11-07
140119 becomes 0013-11-21

Thomas Bock

  • Diamond Member
  • *****
  • Posts: 129
    • View Profile
Re: Database Conversion Utility
« Reply #8 on: July 04, 2019, 02:16:30 AM »
Correction

311218 becomes 0018-12-31
140119 becomes 0019-01-14