More PostgreSQL Madness

[Occam49]

It seems that A5 V9 does not correctly support time type fields in PostgreSQL. If I define time type field in a native DBF table, and export the table to PostgeSQL, the correct type, timestamp, is created in PostgreSQL, but when linked, the link field definition in A5 changes to date, striping off the time.

I thought A5 V9 was certified as PostgreSQL compatible!!

This is the second A5-PostgreSQL anomaly I've discovered in to days, and I've just started!!!
See my post about auto increment fields. http://msgboard.alphasoftware.com/al...ad.php?t=78882

Can someone please point out what other PostgreSQL bugs exist so I don't waste any more time banging my head against Alpha Software's wall.

[Peter.Wayne]

Re: More PostgreSQL Madness

I haven't used PostgreSQL but I haven't experienced your problems using MySQL.
When you created the active link table, did you override the default field types to make the linked field into an Alpha Five Time field?

[Occam49]

Re: More PostgreSQL Madness

No I didn't. During the link process, the PostgreSQL time stamp field was defined as a time type when I looked at the A5 override dialog of the export utility . After the link process, the field type in the A5 field rules dialog is shown as date! In the field rules dialog, you can't change the field type ... is there some way to change this?

[Peter.Wayne]

Re: More PostgreSQL Madness

Right click on the active link table, then choose Active Link, Edit Link Definition. After you define the connection string there is a link in the lower left hand corner of the dialog to "Override field definitions". Choose that, select the field, choose Edit Field Definition, and change the field type to Time.

[Occam49]

Re: More PostgreSQL Madness

The field type in the link definition IS time, but when you open a browse or default form, only date data is shown and only date data can be entered!!! I can enter time data into the field using the PostgreSQL admin tool, but it appear as date in A5 Right clicking on the linked table icon and opening properties shows the field type as date!!!

[Peter.Wayne]

Re: More PostgreSQL Madness

As I said, I don't have PostgreSQL. The DateTime field type works fine with MySQL -- when I created an active link table against it, the field type was Time. The default browse shows an Alpha Five Time field, as does the default form for the active link table.
I created a simple table in MySQL:

create table timeTest (
id integer auto_increment primary key,
dt datetime);

Then I created an active link table against the MySQL table. I simply accepted the defaults and A5 made it into an Alpha Five Time field, which shows up that way in the default form.
If you can't get this to work in PostgreSQL, you should report it as a bug. Alpha generally fixes any reproducible bug within days.

[Occam49]

Re: More PostgreSQL Madness

The PostgreSQL syntax and semantics are different, but have the same effect.


CREATE TABLE t3
(
"C1" SERIAL NOT NULL, -- this will automatically create a sequence generator
"C2" timestamp without time zone, -- date-time type
CONSTRAINT t3_pkey PRIMARY KEY ("C1")
)
WITH (OIDS=FALSE);
ALTER TABLE t3 OWNER TO postgres;

-- add the sequence as default value to column C1

ALTER TABLE t3 ALTER COLUMN "C1" SET STORAGE PLAIN;
ALTER TABLE t3 ALTER COLUMN "C1" SET DEFAULT nextval('"t3_C1_seq"'::regclass);

-- the sequence generator DDL

CREATE SEQUENCE "t3_C1_seq"
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
ALTER TABLE "t3_C1_seq" OWNER TO postgres;


I create this table which behaves at it should when I access it directly using the PostgreSQL admin tool. C1 auto increments, and c2 will accept MM/DD/YY HH:NN:SS input

When I link it, C1 is type number 11,0 and does not auto increment, and c2 ends up as type date and will only accept date data e.g. MM/DD/YYYY . Note: C1 is defined as 11 digits in the link definition, which is less than the automatically created max size of the sequence generator. This is also an "anomaly" ... I don't think it will ever cause real-world problem , but it should be fixed.

As I indicated in post http://msgboard.alphasoftware.com/al...ad.php?t=78882 I can get auto increment link table fields to work by following a silly sequence of steps.

I have not figured out how to “work around” the time type link issue. It looks to me like something is hosed in the link table definitions. Is there someway to examine and edit the link definition data? Where is this kept, and what is the format? I guess I can find this data and reverse engineer it, but don't want to reinvent the wheel if it's been done.

Thanks for your feedback.

I'll report my findings as a possible bug.

[Peter.Wayne]

Re: More PostgreSQL Madness

I don't know about the timestamp, and I know nothing about PostgreSQL, but this comment is, I think, wrong:

Note: C1 is defined as 11 digits in the link definition, which is less than the automatically created max size of the sequence generator. This is also an "anomaly" ... I don't think it will ever cause real-world problem , but it should be fixed.

You defined C1 as "serial", which is a four-byte integer. The range is 1 to 2147483647, which fits in N 11,0.

As for the timestamp issue, you should report it as a bug.

[Occam49]

Re: More PostgreSQL Madness

In actuality, the original PostgreSQL DDL [that I cut and pasted to create t3] was created when I used the A5 export tool to export a native DBF table (t2) that had C1 as an auto increment integer field, and C2 as a time field so ... Alpha created the DDL, and Alpha needs to fix it. I didn't make that clear previously.

So, the A5 export utility is creating the correct data types in PostgreSQL, but the link utility is failing.

[Occam49]

Re: More PostgreSQL Madness

Bump ... Anyone from Alpha Software have anything to say on this ?

[Doug Page]

Re: More PostgreSQL Madness

Anyone from Alpha Software have anything to say on this ?

Since this is a user supported forum and Alpha employees don't respond much here, it would be best to report this as a bug and give them specifics on how to recreate the problem. They will respond to this much quicker.

[Occam49]

Re: More PostgreSQL Madness

Doug,

Thanks, I did that. I do see that Alpha employees have chimed in on issues.

There don't seem to be too many (any?) Alpha 5/PostgreSQL users.

[NoeticCC]

Re: More PostgreSQL Madness

I haven't used PostgreSQL but I haven't experienced your problems using MySQL.
When you created the active link table, did you override the default field types to make the linked field into an Alpha Five Time field?

I suspect that is because Postgres and Oracle use similar date/time fields, I have had the same sort of issue with Oracle "Time" fields (they are called Date in Oracle but contain a time component as well), but not had the same problem with mySQL.