Thread: Frustrating LO problem

Frustrating LO problem

From
Sean Murphy
Date:
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

Re: Frustrating LO problem

From
"Andy Shellam (Mailing Lists)"
Date:
Hi Sean,

I'm certainly not a PostgreSQL expert when it comes to large objects
etc, but there's one thing that jumps out at me here (perhaps someone
else can see the same line of thought and into more depth as I can't
think of much else to suggest?)

> 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"
>
^^ here it seems to be thinking that column 'template' is of type 'oid',
not 'lo' (if I'm reading the error right:  "invalid input syntax for
type oid").

Just double-check what data-type that column is in this database.

Regards,

Andy.

Re: Frustrating LO problem

From
Sean Murphy
Date:
Thanks Andy,

The type was declared and the column was defined as lo... after my big
long explanation about how everything was identical, turns out that it
wasn't... on closer examination I discovered that I had mistakenly set
the ByteaAsLongVarBinary flag on in the ODBC setup for the db where it
wasn't working (it was, of course, off for the db that was working)

Set the flag to off and everything works fine 'n dandy.

Andy Shellam (Mailing Lists) wrote:
> Hi Sean,
>
> I'm certainly not a PostgreSQL expert when it comes to large objects
> etc, but there's one thing that jumps out at me here (perhaps someone
> else can see the same line of thought and into more depth as I can't
> think of much else to suggest?)
>
>> 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"
>>
> ^^ here it seems to be thinking that column 'template' is of type 'oid',
> not 'lo' (if I'm reading the error right:  "invalid input syntax for
> type oid").
>
> Just double-check what data-type that column is in this database.
>
> Regards,
>
> Andy.
>