Thread: problem with add

Afternoon all,

        We have been using the PostgreSQL with 6.4 and the WIN ODBC drivers for a number of years.  The ODBC is a link to reporting writting and mostly enables MS Access & Crystal Reports users access to an applications database.  My problem is that we have literally Hundreds of queries and reports written and none of them now work.  The reason is that when we did the upgrade to PostgreSQL 7.3 recently and now when we link to the DB/Tables they all come back as public_nnnnnnnnn  and  therefore, none of the existing references work.

        I am sure someone else must have worked through this by now, although I have only seen limited references to this issue in my searches which is the reason for my post.  I really do not want to rebuild all of these tools !! 

        TIA,
        JMS...

Jeffrey Silberberg
CompuDesigns, Inc.
Atlanta, GA. 30350

You're leaving out several important details:

PostgreSQL version
PgSQL-ODBC version
Client environment

... having said that ...

Primary keys are implicitly "unique".

To keep from getting an error on insert, "CREATE INDEX tbl1_idx ON tbl1 (a,b);"
instead of making (a,b) a primary key... However, this will allow duplicate
values in thost columns.

If you NEED the values in those columns to be unique, then you will have to
trap the error. Assuming you're using something like vbscript, use :

on error resume next
...do query here...
on error goto 0
if err.number <> 0 then
...it must have failed...
end if

Delphi use: try...except...end;

etc...

CG

--- lists <lists@insyde.com.br> wrote:
> (this is a long post, sorry, but I have to explain the history)
>
> Hi,
>
> I've posted this some time ago, but I received an answer
> that do not satisfy me, and possibly others.
>
> What I'm trying to do is:
>
> I have a taable, say TBL1 built like this:
>
> CREATE TABLE TBL1 ( A INT,
>                                     B CHAR(4),
>                                     C CHAR(3) );
> ALTER TABLE TBL1 ADD PRIMARY KEY ( A, B);
>
> Then I add some values to it, via ODBC
>
> INSERT INTO TBL1 (A,B,C) VALUES (1,'aaaa', 'bbb');
> INSERT INTO TBL1 (A,B,C) VALUES (2,'aaaa', 'bbb');
> INSERT INTO TBL1 (A,B,C) VALUES (3,'aaaa', 'bbb');
> INSERT INTO TBL1 (A,B,C) VALUES (4,'aaaa', 'bbb');
> INSERT INTO TBL1 (A,B,C) VALUES (5,'aaaa', 'bbb');
>
> It's ok so far.
>
> But a new user of the system tries to insert a value that
> already exists:
>
> INSERT INTO TBL1 (A,B,C) VALUES (4,'aaaa', 'ccc');
>
> then the program crashes!
>
> You can say that I didn't treated the error, but I did treat the error,
> but the error code I was expecting (23000 - duplicated primary key)
> didn't came back. Instead an error message from the SGBD came.
>
> Is there any chance this would be corrected? Anyone else had the same
> problem/experience? Any work-around?
>
> If I know more programming, I would correct this, but I can't program.
>
> TIA,
> Ricardo.
>


__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
http://platinum.yahoo.com

Using ODBC 7.00.02.05 ---
Latest at ftp://ftp.us.postgresql.org/odbc/versions/msi/

Entered the SET in the Connect Setting on Page 2.

No Joy, still getting the names of the Tables as public.Table ......

Anyone use have a suggest, or know why this might not be functioning  ??



At 01:05 PM 4/2/2003 +0200, you wrote:

Hi,
try to add the next sentence to the driver configuration: into the "connect
settings"

SET search_path = public, pg_catalog;

Regards

Miguel Juan

----- Original Message -----
From: "Jeff Silberberg" <jsilberberg@mindspring.com>
To: <pgsql-odbc@postgresql.org>
Sent: Tuesday, April 01, 2003 7:44 PM
Subject: Re: [ODBC] problem after upgrade ---


:
: Afternoon all,
:
:          We have been using the PostgreSQL with 6.4 and the WIN ODBC
: drivers for a number of years.  The ODBC is a link to reporting writting
: and mostly enables MS Access & Crystal Reports users access to an
: applications database.  My problem is that we have literally Hundreds of
: queries and reports written and none of them now work.  The reason is that
: when we did the upgrade to PostgreSQL 7.3 recently and now when we link to
: the DB/Tables they all come back as public_nnnnnnnnn  and  therefore, none
: of the existing references work.
:
:          I am sure someone else must have worked through this by now,
: although I have only seen limited references to this issue in my searches
: which is the reason for my post.  I really do not want to rebuild all of
: these tools !!
:
:          TIA,
:          JMS...
:
: Jeffrey Silberberg
: CompuDesigns, Inc.
: Atlanta, GA. 30350
:
:

Jeffrey Silberberg
CompuDesigns, Inc.
Atlanta, GA. 30350
(770) 399-9464

When you say Access reports the tables as public.Table, is that in the "table
view" ?

Do an ODBC trace and see what SQL query it uses to pull the name of the
tables... Conversely, you can enable verbose SQL logging in PostgreSQL and see
it on that end... As a matter of fact, it might be easier to read from the
postgresql end. set debug_print_query = true in
/usr/local/pgsql/data/postgresql.conf, and pg_ctl restart. that may shed some
light on what it is doing.

Did you ask about the "preferred" upgrade path from 6.4 to 7.3?


--- Jeff Silberberg <jsilberberg@mindspring.com> wrote:
>
> Using ODBC 7.00.02.05 ---
> Latest at ftp://ftp.us.postgresql.org/odbc/versions/msi/
>
> Entered the SET in the Connect Setting on Page 2.
>
> No Joy, still getting the names of the Tables as public.Table ......
>
> Anyone use have a suggest, or know why this might not be functioning  ??
>
>
>
> At 01:05 PM 4/2/2003 +0200, you wrote:
> >Hi,
> >try to add the next sentence to the driver configuration: into the "connect
> >settings"
> >
> >SET search_path = public, pg_catalog;
> >
> >Regards
> >
> >Miguel Juan
> >
> >----- Original Message -----
> >From: "Jeff Silberberg" <jsilberberg@mindspring.com>
> >To: <pgsql-odbc@postgresql.org>
> >Sent: Tuesday, April 01, 2003 7:44 PM
> >Subject: Re: [ODBC] problem after upgrade ---
> >
> >
> >:
> >: Afternoon all,
> >:
> >:          We have been using the PostgreSQL with 6.4 and the WIN ODBC
> >: drivers for a number of years.  The ODBC is a link to reporting writting
> >: and mostly enables MS Access & Crystal Reports users access to an
> >: applications database.  My problem is that we have literally Hundreds of
> >: queries and reports written and none of them now work.  The reason is that
> >: when we did the upgrade to PostgreSQL 7.3 recently and now when we link to
> >: the DB/Tables they all come back as public_nnnnnnnnn  and  therefore, none
> >: of the existing references work.
> >:
> >:          I am sure someone else must have worked through this by now,
> >: although I have only seen limited references to this issue in my searches
> >: which is the reason for my post.  I really do not want to rebuild all of
> >: these tools !!
> >:
> >:          TIA,
> >:          JMS...
> >:
> >: Jeffrey Silberberg
> >: CompuDesigns, Inc.
> >: Atlanta, GA. 30350
> >:
> >:
>
> Jeffrey Silberberg
> CompuDesigns, Inc.
> Atlanta, GA. 30350
> (770) 399-9464
>


__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
http://tax.yahoo.com

> that  when we did the upgrade to PostgreSQL 7.3 recently and now when we
> link to  the DB/Tables they all come back as public_nnnnnnnnn  and
> therefore, none  of the existing references work.
>
Here is the Access VBA Code that I use to relink tables from 7.3.x. Please
note in the code that I filter out some tables.

I hope that this h
'*********************************************************************************
Private Sub RelinkTables()
'*********************************************************************************
 '-----------------------------------------------
 'Make sure all tables and forms are closed!!!!!!
 '-----------------------------------------------
    'DB_DNS As String = "ODBC;DSN=BFData;"

    Static wsp As Workspace
    Static con As Connection
    Static init As Integer
    Dim stable As String
    Dim rst As Recordset
    Dim sSQL As String

    If init = 0 Then 'Detect if this is the first time we have run this
function
       init = 1 'Mark we have been through here
       'Initialize the ODBC Connection
       Set wsp = CreateWorkspace("ODBC Workspace", "", "", dbUseODBC)
       Set con = wsp.OpenConnection(DB_DNS, , , DB_DNS)
    End If
    sSQL = "SELECT tablename as Name " & _
              "FROM pg_tables " & _
             "WHERE schemaname = 'public' " & _
          "ORDER BY 1; "
    Set rst = con.OpenRecordset(sSQL, dbOpenSnapshot)
    'con.Execute sSQL, dbRunAsync
    'While con.StillExecuting
    '  DoEvents
    'Wend
    On Error Resume Next
    Do While Not rst.EOF
       stable = rst![Name]
       rst.MoveNext
       If (stable <> "mkt_contactdata") And (stable <> "msysconf") And
(stable <> "partcost_changes") And (Left(stable, 2) <> "zz") Then
          On Error Resume Next
          DoCmd.DeleteObject acTable, stable
          On Error GoTo 0
          DoCmd.TransferDatabase acLink, "ODBC Database", DB_DNS, acTable,
stable, stable
       End If
    Loop
    sSQL = "SELECT viewname as Name " & _
              "FROM pg_views " & _
             "WHERE schemaname = 'public' " & _
          "ORDER BY 1; "
    Set rst = con.OpenRecordset(sSQL, dbOpenSnapshot)
    'con.Execute sSQL, dbRunAsync
    'While con.StillExecuting
    '  DoEvents
    'Wend
    On Error Resume Next
    Do While Not rst.EOF
       stable = rst![Name]
       rst.MoveNext
       If (stable <> "mkt_contactdata") And (stable <> "msysconf") And
(stable <> "partcost_changes") And (Left(stable, 2) <> "zz") Then
          On Error Resume Next
          DoCmd.DeleteObject acTable, stable
          On Error GoTo 0
          DoCmd.TransferDatabase acLink, "ODBC Database", DB_DNS, acTable,
stable, stable
       End If
    Loop

End Sub