Обсуждение: Help : Microsoft SQL Server equivalents in PostGreSQL
Hello,
my customer, ntl:Telewest is using an open source 'file upload progress
bar' called NeatUpload to help users to upload video files to a MS SQL
database. They want to replace the MS SQL database with
Solaris/PostgreSQL and ZFS.
There is an extension to NeatUpload that enables this to work with MS
SQL Server. I emailed the author of this extension to ask how easy it
would be to replace MS SQL Server with PostgreSQL. His reply (below)
suggests that I need to use a postgreSQL equivalent for "Updatetext" and
"ReadText" in MS SQL Server. Any pointers on what the PostgreSQL
equivalent is?
Many thanks.
------------------------------- forwarded email
------------------------------------------------
Hi Benedict,
Re: NeatUpload SqlServerInputFile Extension with PostGres in Solaris 10.
In my implementation of SqlServerInputFile I use the commands
"updatetext" and "readtext", which are as far as I know Microsoft SQL
Server specific. If there is an equivalent in postgresql you could use
that instead.
If there is no equivalent commands, it is possible to use "standard" SQL
to do the same task (i.e. writing chunks of a file to a database) but
there is no such code in my code at this point.
If you indend to rewrite the code so that it works, look at the
SqlServerBlobStream class, and the functions GenerateReadCommand and
GenerateWriteCommand. They aer the ones responsible, and has to be
rewritten using neutral SQL language. To make the file upload work you
really only need to fix "GenerateWriteCommand".
If you manage to make it work I'd love to take a look at it! I've never
used postGreSQL, although I've read quite a lot about it. I think I even
have some 6 months old installation files here somewhere I never had
time to use... ;)
Any further questions, please ask.
Best regards / med vänlig hälsning
Joakim Wennergren, Hitone Nordic AB
Tel: +46 8 556 18 754, Fax: +46 8 556 18 755, Mobile: +46 70 191 42 59
On 1/17/07, *Benedict Faria* <Benedict.Faria@sun.com
<mailto:Benedict.Faria@sun.com>> wrote:
Joakim,
I have a requirement to upload content to a PostGres database on Solaris
10 instead of a Microsoft SQL database. PostGres is an open source SQL
database. See http://www.postgresql.org/ <http://www.postgresql.org/>
From the documentation at
http://www.brettle.com/NeatUpload-1.2/docs/Manual.html#SqlServerInputFile
I don't envisage any issues with doing this, but I though I'd email you
to confirm. Could you help?
Many thanks.
Benedict.
---------------------------------------------------------------------
Benedict Faria
Engagement Architect, Client Solutions - Comms & Media
---------------------------------------------------------------------
Sun Microsystems Computers, Tel : +44 (0)207-256-3200 (Switchboard)
Regis House, : +44 (0)207-469-9725 (Direct line)
55 King William Street, Fax : +44 (0)207-469-9910
London, Mob : +44 (0)7711-053-822
EC4R 9AD. Email: Benedict.Faria@sun.com
<mailto:Benedict.Faria@sun.com>
--------------------------------------------------------------------
Network with peers, keep pace with technology :
Technical community: http://www.sun.com/boardroom
Executive community: http://www.sun.com/innercircle
"The box said 'Windows Server 2003 or better', so I installed Solaris10"
Sorry, I know of no way to get a status bar that shows how far the an INSERT or COPY has progressed. People have asked for it, but no one has any idea how to implement it. --------------------------------------------------------------------------- Benedict Faria wrote: > Hello, > my customer, ntl:Telewest is using an open source 'file upload progress > bar' called NeatUpload to help users to upload video files to a MS SQL > database. They want to replace the MS SQL database with > Solaris/PostgreSQL and ZFS. > > There is an extension to NeatUpload that enables this to work with MS > SQL Server. I emailed the author of this extension to ask how easy it > would be to replace MS SQL Server with PostgreSQL. His reply (below) > suggests that I need to use a postgreSQL equivalent for "Updatetext" and > "ReadText" in MS SQL Server. Any pointers on what the PostgreSQL > equivalent is? > > Many thanks. > > ------------------------------- forwarded email > ------------------------------------------------ > Hi Benedict, > Re: NeatUpload SqlServerInputFile Extension with PostGres in Solaris 10. > > In my implementation of SqlServerInputFile I use the commands > "updatetext" and "readtext", which are as far as I know Microsoft SQL > Server specific. If there is an equivalent in postgresql you could use > that instead. > > If there is no equivalent commands, it is possible to use "standard" SQL > to do the same task (i.e. writing chunks of a file to a database) but > there is no such code in my code at this point. > > If you indend to rewrite the code so that it works, look at the > SqlServerBlobStream class, and the functions GenerateReadCommand and > GenerateWriteCommand. They aer the ones responsible, and has to be > rewritten using neutral SQL language. To make the file upload work you > really only need to fix "GenerateWriteCommand". > > If you manage to make it work I'd love to take a look at it! I've never > used postGreSQL, although I've read quite a lot about it. I think I even > have some 6 months old installation files here somewhere I never had > time to use... ;) > Any further questions, please ask. > Best regards / med v?nlig h?lsning > Joakim Wennergren, Hitone Nordic AB > Tel: +46 8 556 18 754, Fax: +46 8 556 18 755, Mobile: +46 70 191 42 59 > > > On 1/17/07, *Benedict Faria* <Benedict.Faria@sun.com > <mailto:Benedict.Faria@sun.com>> wrote: > > Joakim, > I have a requirement to upload content to a PostGres database on Solaris > 10 instead of a Microsoft SQL database. PostGres is an open source SQL > database. See http://www.postgresql.org/ <http://www.postgresql.org/> > > From the documentation at > http://www.brettle.com/NeatUpload-1.2/docs/Manual.html#SqlServerInputFile > I don't envisage any issues with doing this, but I though I'd email you > to confirm. Could you help? > Many thanks. > Benedict. > > --------------------------------------------------------------------- > Benedict Faria > Engagement Architect, Client Solutions - Comms & Media > --------------------------------------------------------------------- > Sun Microsystems Computers, Tel : +44 (0)207-256-3200 (Switchboard) > Regis House, : +44 (0)207-469-9725 (Direct line) > 55 King William Street, Fax : +44 (0)207-469-9910 > London, Mob : +44 (0)7711-053-822 > EC4R 9AD. Email: Benedict.Faria@sun.com > <mailto:Benedict.Faria@sun.com> > -------------------------------------------------------------------- > Network with peers, keep pace with technology : > Technical community: http://www.sun.com/boardroom > Executive community: http://www.sun.com/innercircle > "The box said 'Windows Server 2003 or better', so I installed Solaris10" > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
I don't think he's looking for progress information, I think he is looking to be able to insert in chunks, which I don't know much about, but I think the some of the binary types (bytea or blob) support this. Anyone? Bruce Momjian wrote: > Sorry, I know of no way to get a status bar that shows how far the an > INSERT or COPY has progressed. People have asked for it, but no one has > any idea how to implement it. > > --------------------------------------------------------------------------- > > Benedict Faria wrote: >> Hello, >> my customer, ntl:Telewest is using an open source 'file upload progress >> bar' called NeatUpload to help users to upload video files to a MS SQL >> database. They want to replace the MS SQL database with >> Solaris/PostgreSQL and ZFS. >> >> There is an extension to NeatUpload that enables this to work with MS >> SQL Server. I emailed the author of this extension to ask how easy it >> would be to replace MS SQL Server with PostgreSQL. His reply (below) >> suggests that I need to use a postgreSQL equivalent for "Updatetext" and >> "ReadText" in MS SQL Server. Any pointers on what the PostgreSQL >> equivalent is? >> >> Many thanks. >> >> ------------------------------- forwarded email >> ------------------------------------------------ >> Hi Benedict, >> Re: NeatUpload SqlServerInputFile Extension with PostGres in Solaris 10. >> >> In my implementation of SqlServerInputFile I use the commands >> "updatetext" and "readtext", which are as far as I know Microsoft SQL >> Server specific. If there is an equivalent in postgresql you could use >> that instead. >> >> If there is no equivalent commands, it is possible to use "standard" SQL >> to do the same task (i.e. writing chunks of a file to a database) but >> there is no such code in my code at this point. >> >> If you indend to rewrite the code so that it works, look at the >> SqlServerBlobStream class, and the functions GenerateReadCommand and >> GenerateWriteCommand. They aer the ones responsible, and has to be >> rewritten using neutral SQL language. To make the file upload work you >> really only need to fix "GenerateWriteCommand". >> >> If you manage to make it work I'd love to take a look at it! I've never >> used postGreSQL, although I've read quite a lot about it. I think I even >> have some 6 months old installation files here somewhere I never had >> time to use... ;) >> Any further questions, please ask. >> Best regards / med v?nlig h?lsning >> Joakim Wennergren, Hitone Nordic AB >> Tel: +46 8 556 18 754, Fax: +46 8 556 18 755, Mobile: +46 70 191 42 59 >> >> >> On 1/17/07, *Benedict Faria* <Benedict.Faria@sun.com >> <mailto:Benedict.Faria@sun.com>> wrote: >> >> Joakim, >> I have a requirement to upload content to a PostGres database on Solaris >> 10 instead of a Microsoft SQL database. PostGres is an open source SQL >> database. See http://www.postgresql.org/ <http://www.postgresql.org/> >> >> From the documentation at >> http://www.brettle.com/NeatUpload-1.2/docs/Manual.html#SqlServerInputFile >> I don't envisage any issues with doing this, but I though I'd email you >> to confirm. Could you help? >> Many thanks. >> Benedict. >> >> --------------------------------------------------------------------- >> Benedict Faria >> Engagement Architect, Client Solutions - Comms & Media >> --------------------------------------------------------------------- >> Sun Microsystems Computers, Tel : +44 (0)207-256-3200 (Switchboard) >> Regis House, : +44 (0)207-469-9725 (Direct line) >> 55 King William Street, Fax : +44 (0)207-469-9910 >> London, Mob : +44 (0)7711-053-822 >> EC4R 9AD. Email: Benedict.Faria@sun.com >> <mailto:Benedict.Faria@sun.com> >> -------------------------------------------------------------------- >> Network with peers, keep pace with technology : >> Technical community: http://www.sun.com/boardroom >> Executive community: http://www.sun.com/innercircle >> "The box said 'Windows Server 2003 or better', so I installed Solaris10" >> >> >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 5: don't forget to increase your free space map settings >
I think you can use the binary string functions (http://www.postgresql.org/docs/8.2/interactive/functions-binarystring.html)to achieve the same as ReadText and UpdateText. /Mikael > -----Original Message----- > From: Benedict Faria [mailto:Benedict.Faria@Sun.COM] > Sent: den 19 januari 2007 14:02 > To: pgsql-general@postgresql.org > Subject: Help : Microsoft SQL Server equivalents in PostGreSQL > > Hello, > my customer, ntl:Telewest is using an open source 'file upload progress > bar' called NeatUpload to help users to upload video files to a MS SQL > database. They want to replace the MS SQL database with > Solaris/PostgreSQL and ZFS. > > There is an extension to NeatUpload that enables this to work with MS > SQL Server. I emailed the author of this extension to ask how easy it > would be to replace MS SQL Server with PostgreSQL. His reply (below) > suggests that I need to use a postgreSQL equivalent for "Updatetext" and > "ReadText" in MS SQL Server. Any pointers on what the PostgreSQL > equivalent is? > > Many thanks. > > ------------------------------- forwarded email > ------------------------------------------------ > Hi Benedict, > Re: NeatUpload SqlServerInputFile Extension with PostGres in Solaris 10. > > In my implementation of SqlServerInputFile I use the commands > "updatetext" and "readtext", which are as far as I know Microsoft SQL > Server specific. If there is an equivalent in postgresql you could use > that instead. > > If there is no equivalent commands, it is possible to use "standard" SQL > to do the same task (i.e. writing chunks of a file to a database) but > there is no such code in my code at this point. > > If you indend to rewrite the code so that it works, look at the > SqlServerBlobStream class, and the functions GenerateReadCommand and > GenerateWriteCommand. They aer the ones responsible, and has to be > rewritten using neutral SQL language. To make the file upload work you > really only need to fix "GenerateWriteCommand". > > If you manage to make it work I'd love to take a look at it! I've never > used postGreSQL, although I've read quite a lot about it. I think I even > have some 6 months old installation files here somewhere I never had > time to use... ;) > Any further questions, please ask. > Best regards / med vänlig hälsning > Joakim Wennergren, Hitone Nordic AB > Tel: +46 8 556 18 754, Fax: +46 8 556 18 755, Mobile: +46 70 191 42 59 > > > On 1/17/07, *Benedict Faria* <Benedict.Faria@sun.com > <mailto:Benedict.Faria@sun.com>> wrote: > > Joakim, > I have a requirement to upload content to a PostGres database on > Solaris > 10 instead of a Microsoft SQL database. PostGres is an open source SQL > database. See http://www.postgresql.org/ <http://www.postgresql.org/> > > From the documentation at > http://www.brettle.com/NeatUpload- > 1.2/docs/Manual.html#SqlServerInputFile > I don't envisage any issues with doing this, but I though I'd email > you > to confirm. Could you help? > Many thanks. > Benedict. > > --------------------------------------------------------------------- > Benedict Faria > Engagement Architect, Client Solutions - Comms & Media > --------------------------------------------------------------------- > Sun Microsystems Computers, Tel : +44 (0)207-256-3200 (Switchboard) > Regis House, : +44 (0)207-469-9725 (Direct line) > 55 King William Street, Fax : +44 (0)207-469-9910 > London, Mob : +44 (0)7711-053-822 > EC4R 9AD. Email: Benedict.Faria@sun.com > <mailto:Benedict.Faria@sun.com> > -------------------------------------------------------------------- > Network with peers, keep pace with technology : > Technical community: http://www.sun.com/boardroom > Executive community: http://www.sun.com/innercircle > "The box said 'Windows Server 2003 or better', so I installed > Solaris10" > >
Bruce Momjian schrieb: > Sorry, I know of no way to get a status bar that shows how far the an > INSERT or COPY has progressed. People have asked for it, but no one has > any idea how to implement it. Well but thats aparently not the case here anyway. Fileupload is something different from looking how far a single insert proceeds. using lo and just counting the write chunks should at least work. When using bytea or text, he could probably use a function to send parts of the big string at a time where the function just concenates whats in the field + whats sent in the chunk. Probably not the performantest approach but should work. > > --------------------------------------------------------------------------- > > Benedict Faria wrote: >> Hello, >> my customer, ntl:Telewest is using an open source 'file upload progress >> bar' called NeatUpload to help users to upload video files to a MS SQL >> database. They want to replace the MS SQL database with >> Solaris/PostgreSQL and ZFS. >> >> There is an extension to NeatUpload that enables this to work with MS >> SQL Server. I emailed the author of this extension to ask how easy it >> would be to replace MS SQL Server with PostgreSQL. His reply (below) >> suggests that I need to use a postgreSQL equivalent for "Updatetext" and >> "ReadText" in MS SQL Server. Any pointers on what the PostgreSQL >> equivalent is? >> >> Many thanks. >> >> ------------------------------- forwarded email >> ------------------------------------------------ >> Hi Benedict, >> Re: NeatUpload SqlServerInputFile Extension with PostGres in Solaris 10. >> >> In my implementation of SqlServerInputFile I use the commands >> "updatetext" and "readtext", which are as far as I know Microsoft SQL >> Server specific. If there is an equivalent in postgresql you could use >> that instead. >> >> If there is no equivalent commands, it is possible to use "standard" SQL >> to do the same task (i.e. writing chunks of a file to a database) but >> there is no such code in my code at this point. >> >> If you indend to rewrite the code so that it works, look at the >> SqlServerBlobStream class, and the functions GenerateReadCommand and >> GenerateWriteCommand. They aer the ones responsible, and has to be >> rewritten using neutral SQL language. To make the file upload work you >> really only need to fix "GenerateWriteCommand". >> >> If you manage to make it work I'd love to take a look at it! I've never >> used postGreSQL, although I've read quite a lot about it. I think I even >> have some 6 months old installation files here somewhere I never had >> time to use... ;) >> Any further questions, please ask. >> Best regards / med v?nlig h?lsning >> Joakim Wennergren, Hitone Nordic AB >> Tel: +46 8 556 18 754, Fax: +46 8 556 18 755, Mobile: +46 70 191 42 59 >> >> >> On 1/17/07, *Benedict Faria* <Benedict.Faria@sun.com >> <mailto:Benedict.Faria@sun.com>> wrote: >> >> Joakim, >> I have a requirement to upload content to a PostGres database on Solaris >> 10 instead of a Microsoft SQL database. PostGres is an open source SQL >> database. See http://www.postgresql.org/ <http://www.postgresql.org/> >> >> From the documentation at >> http://www.brettle.com/NeatUpload-1.2/docs/Manual.html#SqlServerInputFile >> I don't envisage any issues with doing this, but I though I'd email you >> to confirm. Could you help? >> Many thanks. >> Benedict. >> >> --------------------------------------------------------------------- >> Benedict Faria >> Engagement Architect, Client Solutions - Comms & Media >> --------------------------------------------------------------------- >> Sun Microsystems Computers, Tel : +44 (0)207-256-3200 (Switchboard) >> Regis House, : +44 (0)207-469-9725 (Direct line) >> 55 King William Street, Fax : +44 (0)207-469-9910 >> London, Mob : +44 (0)7711-053-822 >> EC4R 9AD. Email: Benedict.Faria@sun.com >> <mailto:Benedict.Faria@sun.com> >> -------------------------------------------------------------------- >> Network with peers, keep pace with technology : >> Technical community: http://www.sun.com/boardroom >> Executive community: http://www.sun.com/innercircle >> "The box said 'Windows Server 2003 or better', so I installed Solaris10" >> >> >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 5: don't forget to increase your free space map settings >
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 01/19/07 18:21, Bruce Momjian wrote: > Sorry, I know of no way to get a status bar that shows how far the an > INSERT or COPY has progressed. People have asked for it, but no one has > any idea how to implement it. How hard *would* it be to patch the \copy code to print out a "#" every N number of input records? Or is it "just" a matter of someone with a sufficient itch sending in a relatively simple patch? -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFFskQyS9HxQb37XmcRAip2AJ46AZeQZPFx5SbYyYNBdDvSM9eSIgCeMlM8 9wu0Zi2t6ARyoWPIqzwqqw4= =qGLP -----END PGP SIGNATURE-----
Ron Johnson wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On 01/19/07 18:21, Bruce Momjian wrote: > > Sorry, I know of no way to get a status bar that shows how far the an > > INSERT or COPY has progressed. People have asked for it, but no one has > > any idea how to implement it. > > How hard *would* it be to patch the \copy code to print out a "#" > every N number of input records? > > Or is it "just" a matter of someone with a sufficient itch sending > in a relatively simple patch? Yea, probably, but considering they probably want a graphic progress bar, the big question is what API would you use to allow flexible use of that feature. -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> writes:
> Ron Johnson wrote:
>> How hard *would* it be to patch the \copy code to print out a "#"
>> every N number of input records?
> Yea, probably, but considering they probably want a graphic progress
> bar, the big question is what API would you use to allow flexible use of
> that feature.
This is not a server-side problem; as Ron noted, it'd be \copy's problem
not COPY's. And psql doesn't have a graphic API to worry about.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 01/20/07 10:52, Bruce Momjian wrote: > Ron Johnson wrote: >> -----BEGIN PGP SIGNED MESSAGE----- >> Hash: SHA1 >> >> On 01/19/07 18:21, Bruce Momjian wrote: >>> Sorry, I know of no way to get a status bar that shows how far the an >>> INSERT or COPY has progressed. People have asked for it, but no one has >>> any idea how to implement it. >> How hard *would* it be to patch the \copy code to print out a "#" >> every N number of input records? >> >> Or is it "just" a matter of someone with a sufficient itch sending >> in a relatively simple patch? > > Yea, probably, but considering they probably want a graphic progress > bar, the big question is what API would you use to allow flexible use of > that feature. GUIs are for WIMPs. If $DEITY wanted everyone to use GUIs, he would have made them widespread across computing. Oh, wait. Never mind... -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFFsmgGS9HxQb37XmcRAjqSAKCwpj9iVzGeoijCLxWMln2HGHcpIACgpDCl BsTFETubhRiEV1oK5DA683A= =vo8H -----END PGP SIGNATURE-----
Benedict Faria wrote:
> I need to use a postgreSQL equivalent for "Updatetext" and
> "ReadText" in MS SQL Server. Any pointers on what the PostgreSQL
> equivalent is?
Hi Benedict,
I don't see an exact equivalent to MS SQL Server's UpdateText (and
ReadText) commands in pgsql or any other PostgreSQL procedural
language. The closest thing would be to use array syntax (e.g. UPDATE
table SET field[start:end] = {val1, val2, val3}) but npgsql doesn't
seem to support passing array datatypes yet. Also it isn't clear from
the PostgreSQL docs whether the array slice syntax can be used on the
bytea type. If not, I think you'd need to use an array of smallints
(2-byte ints) which might hurt performance and storage.
I think your best bet is to use a large object. Npgsql has support for
writing and reading large objects in chunks. The primary difference
from the UpdateText SQL function that MS SQL Server provides is that
you will pass the chunks to Npgsql's LargeObject.Write() method instead
of passing the chunks to an SQL command or stored proc. Also, instead
of putting the data directly in the row, you'll put it in a large
object and then put the OID of the large object in the row.
For more info about Npgsql's large object support see:
http://npgsql.projects.postgresql.org/docs/manual/UserManual.htm
http://npgsql.projects.postgresql.org/docs/api/NpgsqlTypes.LargeObject.html
Hope that helps,
--Dean