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