Add Large Object support to database programmatically

I have been working to create a VB6 program to automatically create a
PostgreSQL database, tables and triggers for an application I am updating. I
have everything working great, except one of my tables needs to store a
bitmap image. I am using the Large Object (lo) contrib module to do this.
Using pgAdmin III, I can run the Query Tool and load the lo.sql file from
the \share\contrib folder and execute it on my database and it succeeds.

What I need to be able to do is load this contrib module on the fly from VB6
after creating my database. Using the content of the lo.sql file, I created
the following code:

Private Function AddLargeObjectDataType(connConnection As ADODB.Connection)
As Boolean
Dim cmdCommand As New ADODB.Command

With cmdCommand
.ActiveConnection = conConnection
.CommandType = adCmdText
.CommandText = "SET search_path = public; CREATE DOMAIN lo AS
pg_catalog.oid;" _
& "CREATE FUNCTION lo_oid(lo) RETURNS pg_catalog.oid
AS" _
& "'SELECT $1::pg_catalog.oid' LANGUAGE SQL STRICT
IMMUTABLE;" _
& "CREATE FUNCTION lo_manage() RETURNS
pg_catalog.trigger" _
& "AS '$libdir/lo' LANGUAGE C;"
Call .Execute
End With
Set cmdCommand = Nothing
End Function

However, when I execute this code, I get a SQL "syntax error at or near
'$libdir/lo'" and the contrib module is not loaded. So I tried using a hard
coded path 'C:\Program Files\PostgreSQL\8.1\lib\lo' instead of '$libdir/lo'
and it also fails. I tried using double backslashes, same result. Forward
slashes, same result.

Any ideas?
--
View this message in context: http://www.nabble.com/Add-Large-Object-support-to-database-programmatically-tp25283311p25283311.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

acordner wrote:
> .CommandText = "SET search_path = public; CREATE DOMAIN lo AS
> pg_catalog.oid;" _
> & "CREATE FUNCTION lo_oid(lo) RETURNS pg_catalog.oid
> AS" _
> & "'SELECT $1::pg_catalog.oid' LANGUAGE SQL STRICT
> IMMUTABLE;" _
> & "CREATE FUNCTION lo_manage() RETURNS
> pg_catalog.trigger" _
> & "AS '$libdir/lo' LANGUAGE C;"
> Call .Execute
> End With
> Set cmdCommand = Nothing
> End Function
>
> However, when I execute this code, I get a SQL "syntax error at or near
> '$libdir/lo'" and the contrib module is not loaded.

Turn statement logging on server-side and you'll see what's happening.

My VB isn't the greatest, but doesn't the "_" join together multi-line
strings? In which case you don't need the "&".

However, it looks to me like the actual problem is that you're missing
some spaces/newlines and getting SQL like:
RETURNS pg_catalog.triggerAS '$libdir/lo'

Turn up your server logging and you'll know for sure.
--
Richard Huxton
Archonet Ltd

Wow, do I feel stupid now! After creating the database, 15 tables and a
trigger using the same exact process, I somehow overlooked adding the spaces
to the front of each line. %-|

Thanks, that worked great! I guess I stared at the code too long to see it.

acordner wrote:
>
> I have been working to create a VB6 program to automatically create a
> PostgreSQL database, tables and triggers for an application I am updating.
> I have everything working great, except one of my tables needs to store a
> bitmap image. I am using the Large Object (lo) contrib module to do this.
> Using pgAdmin III, I can run the Query Tool and load the lo.sql file from
> the \share\contrib folder and execute it on my database and it succeeds.
>
> What I need to be able to do is load this contrib module on the fly from
> VB6 after creating my database. Using the content of the lo.sql file, I
> created the following code:
>
>
> Private Function AddLargeObjectDataType(connConnection As
> ADODB.Connection) As Boolean
> Dim cmdCommand As New ADODB.Command
>
> With cmdCommand
> .ActiveConnection = conConnection
> .CommandType = adCmdText
> .CommandText = "SET search_path = public; CREATE DOMAIN lo AS
> pg_catalog.oid;" _
> & "CREATE FUNCTION lo_oid(lo) RETURNS
> pg_catalog.oid AS" _
> & "'SELECT $1::pg_catalog.oid' LANGUAGE SQL STRICT
> IMMUTABLE;" _
> & "CREATE FUNCTION lo_manage() RETURNS
> pg_catalog.trigger" _
> & "AS '$libdir/lo' LANGUAGE C;"
> Call .Execute
> End With
> Set cmdCommand = Nothing
> End Function
>
> However, when I execute this code, I get a SQL "syntax error at or near
> '$libdir/lo'" and the contrib module is not loaded. So I tried using a
> hard coded path 'C:\Program Files\PostgreSQL\8.1\lib\lo' instead of
> '$libdir/lo' and it also fails. I tried using double backslashes, same
> result. Forward slashes, same result.
>
> Any ideas?
>

--
View this message in context: http://www.nabble.com/Add-Large-Object-support-to-database-programmatically-tp25283311p25295203.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.