Thread: pgaccess
hi, is pgaccess still a good software ? because my readline don't work with psql, so i'm looking for an alternate solution. thank you for your suggestion patrick
I have built an application using interval data type to hold things like the time between two events etc.... (as you mightexpect) I am now required to deploy that app on SQL server too... and I just discovered they have no interval data type... or atleast it looks that way to me... (no sql server gurus available where I am). Not sure how I am going to handle this yet... So I am looking for any sort of sage advise that you all might have for meat this point. thanks in advance, Brian
try PG Explorer http://www.pgexplorer.com ----- Original Message ----- From: "Patrick Coulombe" <pcoulombe@mediacces.com> To: <pgsql-novice@postgresql.org> Sent: Tuesday, April 16, 2002 6:34 PM Subject: [NOVICE] pgaccess > hi, > > is pgaccess still a good software ? because my readline don't work with > psql, so i'm looking for an alternate solution. > > thank you for your suggestion > patrick > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html
Brian, > I have built an application using interval data type to hold things > like the time between two events etc.... (as you might expect) > > I am now required to deploy that app on SQL server too... and I just > discovered they have no interval data type... or at least it looks > that way to me... (no sql server gurus available where I am). Well, I am a SQL Server guru (or at least I can get people to pay me unreasonable amounts of money on that presumption), so I'll tell you flat out: If your application has to handle scheduling, you're in *big* trouble with MS SQL Server. MS SQL Server does not support Interval, does not support date math, and even its date/time data types are text-based and prone to errors. There are no easy workarounds ... it's like trying to code a text-parsing application in Visual Basic; you can do it, you'll just wish you'd learned Perl instead. Basically, tell your boss that if you are doing a scheduling-heavy app and it has to be ported to MS SQL Server, double your budget. If the scheduling/interval parts are a minority of the application, then maybe we can do something. Is this SQL Server 2000 or SQL Server 7? > Not sure how I am going to handle this yet... So I am looking for any > sort of sage advise that you all might have for me at this point. I could port it for you, but I'd have to charge you a small fortune. Does your employer have one to spend? And you will have to re-do the porting process, manually, every time you update the application. If not, at least take a look at the porting advice at http://techdocs.postgresql.org/ -Josh Berkus P.S. SQL Server does do *some* things well. Just not very many.
Patrick, > is pgaccess still a good software ? because my readline don't work > with > psql, so i'm looking for an alternate solution. No, PGAccess is sadly out of date due to lack of porject staff. Wanna learn TCL/Tk? If you're having trouble with linking the readline library to Postgres, you probably need to re-compile postgres (no initdb required) after installing libreadline-devel. Most Linux installations only install the binary of the library, and as a result postgres (and several other apps, like proftp) can't access the library. -Josh Berkus
Hey Josh, Thanks for the insight. My task is to get the application... Coldfusion.... to work with both postgres and sql server 2000 with the same coldfusioncode... this makes maintenance and ongoing development of the app easier because any changes can be exported toall the various sites running it... regardless of the backend database. The deal is that the database... 30 or so tables... holds maybe 30 or so records across all the tables upon initialization...Not a big deal. I have not used any postges functions except the obvious ones like MAX etc... that should work across most any backend...(sqlserver, postgres, oracle, mySQL). So my task is to decide how to store the interval... in a ??? char ??? field perhaps ??? and then process that with coldfusionfor presentation... I know I've got a bit of work(understatement) ahead of me. Sooo.... any and all hints give me ideas and clues to feed the monster ;) Thanks! Brian At 09:42 PM 4/16/02 -0700, you wrote: >Brian, > >> I have built an application using interval data type to hold things >> like the time between two events etc.... (as you might expect) >> >> I am now required to deploy that app on SQL server too... and I just >> discovered they have no interval data type... or at least it looks >> that way to me... (no sql server gurus available where I am). > >Well, I am a SQL Server guru (or at least I can get people to pay me >unreasonable amounts of money on that presumption), so I'll tell you >flat out: If your application has to handle scheduling, you're in >*big* trouble with MS SQL Server. > >MS SQL Server does not support Interval, does not support date math, >and even its date/time data types are text-based and prone to errors. > There are no easy workarounds ... it's like trying to code a >text-parsing application in Visual Basic; you can do it, you'll just >wish you'd learned Perl instead. > >Basically, tell your boss that if you are doing a scheduling-heavy app >and it has to be ported to MS SQL Server, double your budget. > >If the scheduling/interval parts are a minority of the application, >then maybe we can do something. Is this SQL Server 2000 or SQL Server >7? > >> Not sure how I am going to handle this yet... So I am looking for any >> sort of sage advise that you all might have for me at this point. > >I could port it for you, but I'd have to charge you a small fortune. > Does your employer have one to spend? And you will have to re-do the >porting process, manually, every time you update the application. > >If not, at least take a look at the porting advice at >http://techdocs.postgresql.org/ > >-Josh Berkus > >P.S. SQL Server does do *some* things well. Just not very many. > >---------------------------(end of broadcast)--------------------------- >TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/users-lounge/docs/faq.html
Brian, > My task is to get the application... Coldfusion.... to work with both > postgres and sql server 2000 with the same coldfusion code... this > makes maintenance and ongoing development of the app easier because > any changes can be exported to all the various sites running it... > regardless of the backend database. Coldfusion? Things just get worse and worse. I feel your pain. > So my task is to decide how to store the interval... in a ??? char > ??? field perhaps ??? and then process that with coldfusion for > presentation... I know I've got a bit of work(understatement) ahead > of me. Well, SQL Server 2000 gives you the ability to create custom functions, which should be your salvation. 1. Create your interval column as and integer and text, and save interval values like so: interval_number interval_type 3 months 15 hours This destroys your ability to store compound intervals (like '3 months 12 days 8 hours') but it's a sacrifice you'll have to make. 2. Create a function in each database called "add_interval(datetime, integer, text)" In postgres, this function will be very simple: CREATE FUNCTION add_interval ( timestamp, int4, text ) RETURNS timestamp AS ' SELECT ($1 + "interval"(($2 || '' '' || $3))); ' LANGUAGE 'sql'; In SQL Server 2000, you will have to use some if/then statements and the various DATEADD() functions. But you can do it. ORACLE should be as easy as Postgres. 3. Then, anywhere in your application that you normally would simply add intervals, you can call add_interval and it will work on all three database platforms. Now, you owe me one for the advice. So, I demand that you write up this solution to be posted at Techdocs once you've done it, with all code. -Josh Berkus
Brian, Also, you want to buy O'Reilly's "SQL in a Nutshell", which has a dictionary of support for various SQL statements across the 4 most popular databases (MySQL, Postgres, SQL Server, and Oracle). -Josh
Hey Josh, You Rock! You know, the last incarnation of this huge app ran against a btrieve backend... interval data was stored as type real andunderstood to be days. We built lots of routines to manipulate this for user output... there is some pressure buildingto repeat that. SQL in a nutshell sounds like one nut I should crack! Brian At 02:32 PM 4/17/02 -0700, you wrote: >Brian, > >Also, you want to buy O'Reilly's "SQL in a Nutshell", which has a >dictionary of support for various SQL statements across the 4 most >popular databases (MySQL, Postgres, SQL Server, and Oracle). > >-Josh > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > >http://archives.postgresql.org