Thread: Problem with VB-DirectODBC selecting text-fields [EBEWE: Virus checked]
Problem with VB-DirectODBC selecting text-fields [EBEWE: Virus checked]
From
Wolfgang.Fuertbauer@ebewe.com
Date:
Hi,
i have a problem with the following SQL-Statement:
Select Groessen from Weg where Bsnr = 14957 and Znr = 1 and wegkz = 1;
when executing it via direct-odbc in VB it gives me the error-nr: 3146
the VB-Code:
------------
Set myConn = myWs.OpenConnection("", _
dbDriverNoPrompt, _
False,_
"ODBC;dsn=PostgreSQL;uid=postgres;pwd=;")
sql = "Select Groessen from Weg where Bsnr = 14957 and Znr = 1 and wegkz =
1"
Set rs = myConn.OpenRecordset(Sql, _
dbOpenSnapshot, _
dbExecDirect, _
dbOptimistic)
the database:
-------------
7.1.3
the ODBC-Version:
-----------------
7.01.00.09
the table 'weg' in database 'fakt32':
-------------------------------------
fakt32=#\d weg
Attribute | Type | Modifier
-------------+-----------------------+----------------------------
inr | integer | not null default
nextval('"weg_inr_seq"'::text)
wegnr | integer | default 0
wegkz | smallint | default 0
bsnr | integer | default 0
znr | integer | default 0
arnr | character varying(15) |
arname | character varying(30) |
arlfnr | integer | default 0
arlfname | character varying(25) |
arlfarnr | character varying(15) |
datum | date |
bkz | integer | default 0
groessen | text |
bestellung | text |
Indices: weg_bsnr_key,
weg_pkey,
weg_wegnr_key
- excuting the same query via psql works fine:
fakt32=# Select Groessen from Weg where Bsnr = 14957 and Znr = 1 and
wegkz = 1;
groessen
---------------
6;72;0;7;32;0
(1 row)
- executing the same query via pgadmin II also works fine
- executing the same query via Access (linked table) also works fine
- looking at the logs:
in psqlodbc_4294820759.log beginning from line 170:
--------------------------
>conn=170740516, query='Select Groessen from Weg where Bsnr = 14914 and
>Znr = 1 and wegkz = 1'
> [ fetched 1 rows ]
that's OK
>conn=170740516, query='SELECT * FROM Select Groessen from Weg where Bsnr
>= 14914 and Znr = 1 and >wegkz = 1'
???? where does this "SELECT * " come from ?
>ERROR from backend during send_query: 'ERROR: parser: parse error at or
>near "Select"'
this is the result of the 2nd (wrong) query
mylog_4294820759.log shows the same beginning from line 94853
Please help!
(See attached file: psqlodbc_4294820759.zip)(See attached file:
mylog_4294820759.zip)
mfG
----------------------------------------------------------
Wolfgang Fuertbauer, wolfgang.fuertbauer@ebewe.com
c/o EBEWE Pharma Ges.m.b.H Nfg.KG Tel: +43 7665 8123 315
Mondeseestrasse 11 Fax: +43 7665 8123 11
4866 Unterach, Austria
Attachment
Hi, with psqlodbc 7.1.9 an attempt to write to a table that the current user
doesn't have enought privilege raise error
number -2147467259 and err.description contains "Permission denied", with
psqlodbc 7.1.11 it raise the same err number
but text has changed to "Error while executing the query", that is less
descriptive and I need a descriptive text because error number
-2147467259 is raised for various errors (emty row, referential integrity
violation, duplicate key ...) .
Is this a bug ? How can I know the correct error ?
Thanks
(extract of a class to trap errors)
Select Case Err.Number
Case -2147217864 'the row has changed
msgbox "El registre ha canviat mentres s'editava," & Chr$(13) &
_
"cancel.li els canvis i torni a recuperar el registre " &
Chr$(13) & _
"(Els seus canvis es perdràn)", vbExclamation + vbOKOnly, "Barra
Grid"
Resume Next
Case -2147467259
If InStr(1, Err.Description, "Empty row cannot be inserted",
vbTextCompare) > 0 Then
MsgBox "No es pot afegir un registre buit !"
ErrorTrobat = True
End If
If InStr(1, Err.Description, "Fail to add null value in not
null", vbTextCompare) > 0 Then
MsgBox "Falten Camps Obligatoris"
ErrorTrobat = True
End If
If InStr(1, Err.Description, "referential integrity violation",
vbTextCompare) > 0 Then
MsgBox "No es pot esborrar, hi ha registres relacionats a
altres taules"
ErrorTrobat = True
End If
If InStr(1, Err.Description, "cannot insert a duplicate key into
unique index", vbTextCompare) > 0 Then
MsgBox "No es pot realizar l'operació, crearia valors
duplicats"
ErrorTrobat = True
End If
If InStr(1, Err.Description, "Permission denied", vbTextCompare)
> 0 Then
MsgBox "No té permisos per ha crear nous registres i/o
modificar-los."
ErrorTrobat = True
End If
If InStr(1, Err.Description, "you don't have permissions to set
sequence", vbTextCompare) > 0 Then
MsgBox "No té permisos per ha modificar la sequència."
ErrorTrobat = True
End If
If ErrorTrobat = False Then
MsgBox "Error Inesperat " & Err.Description & Err.Number
End If
Case Else
MsgBox "Error numero :" & Err.Number & " descripcio :" &
Err.Description
End Select
> -----Original Message----- > From: Simeo Reig > > Hi, with psqlodbc 7.1.9 an attempt to write to a table that the > current user > doesn't have enought privilege raise error > number -2147467259 and err.description contains "Permission denied", with > psqlodbc 7.1.11 it raise the same err number > but text has changed to "Error while executing the query", that is less > descriptive and I need a descriptive text because error number > -2147467259 is raised for various errors (emty row, referential integrity > violation, duplicate key ...) . > > Is this a bug ? How can I know the correct error ? It seems my fault. Please retry the newest driver. regards, Hiroshi Inoue
Re: Problem with VB-DirectODBC selecting text-fields [EBEWE: Virus checked]
From
"Hiroshi Inoue"
Date:
> -----Original Message-----
> From: Wolfgang.Fuertbauer@ebewe.com
>
> Hi,
>
> i have a problem with the following SQL-Statement:
>
> Select Groessen from Weg where Bsnr = 14957 and Znr = 1 and wegkz = 1;
>
> when executing it via direct-odbc in VB it gives me the error-nr: 3146
>
> the VB-Code:
> ------------
> Set myConn = myWs.OpenConnection("", _
> dbDriverNoPrompt, _
> False,_
> "ODBC;dsn=PostgreSQL;uid=postgres;pwd=;")
> sql = "Select Groessen from Weg where Bsnr = 14957 and Znr = 1 and wegkz =
> 1"
> Set rs = myConn.OpenRecordset(Sql, _
> dbOpenSnapshot, _
> dbExecDirect, _
> dbOptimistic)
How are you creating the myWs ?
Are you setting the DefaultCursorDriver property as dbUseOdbcCursor ?
regards,
Hiroshi Inoue