Alpha Video Training
Results 1 to 13 of 13

Thread: More PostgreSQL Madness

  1. #1
    Member
    Real Name
    Darrell
    Join Date
    Feb 2009
    Location
    So Cal
    Posts
    16

    Default More PostgreSQL Madness

    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.

  2. #2
    "Certified" Alphaholic
    Real Name
    Peter Wayne
    Join Date
    Apr 2000
    Posts
    1,728

    Default 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?

  3. #3
    Member
    Real Name
    Darrell
    Join Date
    Feb 2009
    Location
    So Cal
    Posts
    16

    Default 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?

  4. #4
    "Certified" Alphaholic
    Real Name
    Peter Wayne
    Join Date
    Apr 2000
    Posts
    1,728

    Default 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.

  5. #5
    Member
    Real Name
    Darrell
    Join Date
    Feb 2009
    Location
    So Cal
    Posts
    16

    Default 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!!!

  6. #6
    "Certified" Alphaholic
    Real Name
    Peter Wayne
    Join Date
    Apr 2000
    Posts
    1,728

    Default 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:
    Code:
    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.

  7. #7
    Member
    Real Name
    Darrell
    Join Date
    Feb 2009
    Location
    So Cal
    Posts
    16

    Default 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.
    Last edited by Occam49; 03-29-2009 at 04:39 PM.

  8. #8
    "Certified" Alphaholic
    Real Name
    Peter Wayne
    Join Date
    Apr 2000
    Posts
    1,728

    Default 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.
    Last edited by Peter.Wayne; 03-29-2009 at 05:20 PM. Reason: clarity

  9. #9
    Member
    Real Name
    Darrell
    Join Date
    Feb 2009
    Location
    So Cal
    Posts
    16

    Default 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.

  10. #10
    Member
    Real Name
    Darrell
    Join Date
    Feb 2009
    Location
    So Cal
    Posts
    16

    Default Re: More PostgreSQL Madness

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

  11. #11
    Member
    Real Name
    Doug Page
    Join Date
    Jan 2002
    Location
    Vancouver, BC Canada
    Posts
    963

    Default Re: More PostgreSQL Madness

    Quote Originally Posted by Occam49 View Post
    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.

  12. #12
    Member
    Real Name
    Darrell
    Join Date
    Feb 2009
    Location
    So Cal
    Posts
    16

    Default 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.

  13. #13
    "Certified" Alphaholic NoeticCC's Avatar
    Real Name
    Andrea Gill
    Join Date
    Nov 2007
    Location
    Golcar, West Yorkshire, UK
    Posts
    2,070

    Default Re: More PostgreSQL Madness

    Quote Originally Posted by Peter.Wayne View Post
    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.

Similar Threads

  1. Postgresql on Linux as a backend
    By dixonpete in forum Alpha Five Version 9 - Desktop Applications
    Replies: 5
    Last Post: 06-14-2011, 11:37 AM
  2. Active link to PostgreSQL table with auto increment madness
    By Occam49 in forum Alpha Five Version 9 - Desktop Applications
    Replies: 1
    Last Post: 04-01-2009, 07:21 PM
  3. Postgresql and bytea column type
    By omagarc in forum Alpha Five Version 8
    Replies: 0
    Last Post: 09-05-2008, 02:15 PM
  4. Panel Madness - or ???
    By Peter.Greulich in forum Alpha Five Version 8
    Replies: 11
    Last Post: 03-11-2007, 11:43 PM
  5. SQL connecting PostgreSQL
    By coleman in forum Alpha Five Version 5
    Replies: 0
    Last Post: 08-05-2003, 09:57 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •