Обсуждение: problem with add
(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.
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