I have two databases, one from a pre-merger company, and one from a
post-merger company (the company otherwise being the same). The
post-merger database was created as an empty replica of the old. I have
a VB application (no, VB was not *my* choice) that's used by employees
to (sometimes simultaneously) access the old and new dbs, on the same
server, with the same encoding and configuration and ODBC drivers. At
login the user chooses which db to connect to, and the application
proceeds to establish an ODBC connection using ADO to the appropriate
db. All of it is working fine, except a form that uploads templates as
los - same app, same code, different db works on old crashes on new.
Running PostgreSQL 8.2 on Win2K server, connecting via psql ODBC Unicode
8.2.2.0, psqlodbc35w.dll
using a text file containing "testfile.contents" as the lo:
The logfile lines when it works:
{
LOG: statement: BEGIN
LOG: fastpath function call: "lo_creat" (OID 957)
LOG: fastpath function call: "lo_open" (OID 952)
LOG: fastpath function call: "lo_lseek" (OID 956)
LOG: fastpath function call: "lowrite" (OID 955)
LOG: fastpath function call: "lo_close" (OID 953)
LOG: statement: COMMIT
LOG: statement: insert into doc.templates (description, last_update,
template) values ('testing', '12/29/2006', '51661751'::lo)
}
The logfile lines when it doesn't:
{
WARNING: nonstandard use of \\ in a string literal at character 94
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
LOG: statement: insert into doc.templates (description, last_update,
template) values ('test', '12/29/2006', 'testfile\\056contents')
ERROR: invalid input syntax for type oid: "testfile\056contents"
STATEMENT: insert into doc.templates (description, last_update,
template) values ('test', '12/29/2006', 'testfile\\056contents')
}
Clearly the 5 lo_ functions are being invoked in the old db, but not in
the new, although I'm certainly not manually coding anything to invoke
them. Did I miss a configuration step when I set up the newer db? Which one?
The lo type was created using lo.sql from the contrib directory on both dbs.
the table definition:
(identical between the two dbs)
{
CREATE TABLE doc.templates
(
templid serial NOT NULL,
last_update date,
description character varying(100),
"template" lo,
CONSTRAINT templates_pkey PRIMARY KEY (templid)
)
WITH OIDS;
CREATE TRIGGER template_lo
BEFORE UPDATE OR DELETE
ON doc.templates
FOR EACH ROW
EXECUTE PROCEDURE lo_manage('template');
}
the vb connection code for the new db where it doesn't work
(both dbs connect via Windoze Registry ODBC DSN definitions that are
identical except for the user and catalog)
{
Dim dc As New CRijndael
output = dc.DecryptData(pcode, pw)
Dim connparam As String
connparam = "Provider=MSDASQL.1;Persist Security Info=False;Data
Source=[newdbODBCname];Initial Catalog=[newdbcatalog];Password=" &
CStr(output)
On Error GoTo noConn
Core_functions.conn.Open connparam
On Error GoTo 0
goodLogin = True
GoTo theEnd
noConn:
If InStr(Err.description, "password authentication failed for user") Then
MsgBox "The database is currently unavailable. Please double-check
your network connection and try again. Please contact corporate IT if
the problem persists."
End If
}
for the old db where it DOES work
{
Dim dc As New CRijndael
output = dc.DecryptData(pcode, pw)
Dim connparam As String
connparam = "Provider=MSDASQL.1;Persist Security Info=False;Data
Source=[olddbODBCname];Initial Catalog=[olddbcatalog];Password=" &
CStr(output)
On Error GoTo noConn
Core_functions.conn.Open connparam
On Error GoTo 0
goodLogin = True
ADODCstr = "Provider=MSDASQL.1;Persist Security Info=False;Data
Source=celia_corp;Initial Catalog=CELIA;Password="
GoTo theEnd
noConn:
If InStr(Err.description, "password authentication failed for user") Then
MsgBox "The database is currently unavailable. Please double-check
your network connection and try again. Please contact corporate IT if
the problem persists."
End If
}
the vb code on the upload form calls
{
PutFile "insert into doc.templates (description, last_update, template)
values ('" & description.Text & "', '" & Format(Now(), "mm/dd/yyyy") &
"', ?)", File1.Path & "\" & File1.filename
}
which in turn calls the following sub
{
Public Sub PutFile(queryText As String, theFile As String)
'QueryText should contain the insert query with a question mark where
the file (as data) should go
Dim theQuery As ADODB.command
Dim chunk() As Byte
Dim fileID As Integer
Dim fileLen As Long
'setup the query
Set theQuery = New ADODB.command
theQuery.ActiveConnection = conn
theQuery.CommandText = queryText
theQuery.CommandType = adCmdText
'open the file for reading
fileID = FreeFile
Open theFile For Binary Access Read As fileID
fileLen = LOF(fileID)
If fileLen = 0 Then
MsgBox "Could not open the specified file"
GoTo theEnd
End If
'DBFileField parameter
'fourth parameter indicates the memory to allocate to store the object
Set DBFileField = theQuery.CreateParameter("DBFileField",
adLongVarBinary, adParamInput, fileLen + 100)
ReDim chunk(1 To fileLen)
Get fileID, , chunk()
'insert the file into the parameter object
DBFileField.AppendChunk chunk()
theQuery.Parameters.Append DBFileField
'execute
theQuery.Execute
theEnd:
'clean up
Close fileID
Set theQuery = Nothing
End Sub
}
Thanks for anyone who can help (or at least tries to!)
Sean Murphy