Re: [GENERAL] ADO and timestamp/date errors
Craig Bryden wrote: Hi I am trying to read a data set in ADO. If I exclude a timestamp field that I have in the return type, all works wonderfully. The moment I include the timestamp (i've even tried casting it as date) it bombs out with an OLEDB Exception of some type. Try reading the text of the exception. You are using an old version of OLE DB. Try the latest version (http://gborg.postgresql.org/project/oledb). It should either work, or give you a proper error message. Has anyone got any ideas? OLE DB is a binary interface. As such, it needs to know the binary structure of any data type it encounters. It does not yet support all the data types returned by Postgres 8. Please place (CC to this list) the way you defined the column that offends OLE DB. Please note that a newer version of OLE DB did have some additions in the time types category, so I heartily suggest you try that first. Thanks Craig Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. Have you backed up today's work? http://www.lingnu.com/backup.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] ADO and timestamp/date errors
Craig Bryden wrote: Hi Shachar I have confirmed that I am running the latest version. The error that I get (in ADO .Net) is : ** An unhandled exception of type 'System.ArgumentOutOfRangeException' occurred in system.data.dll Additional information: Specified argument was out of the range of valid values.\r\nParameter name: Year, Month, and Day parameters describe an unrepresentable DateTime Can you please send me the table creation command, and the command that failed? It sounds to me like a bug in the time handling by the OLE DB. Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. Have you backed up today's work? http://www.lingnu.com/backup.html ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Unicode support on Win32
Bruce Momjian wrote: Caleb wrote: Hi, I am using Windows as a development environment (and database design), and it seems that Unicode is not supported on Win32. Yes, the problem is that we only support UTF-8 and the Win32 collation routines only support UTF-16. You can actually use UTF-8 if you don't care about ordering of the character set, but pginstaller doesn't support those options --- you have to run initdb manually. I know this is the wrong list for it, but here are some thoughts about solutions: 1. Convert to UTF-16 - Windows give you builtin functions to do that. 2. Use another library set (ICU?) Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. Have you backed up today's work? http://www.lingnu.com/backup.html ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] skip weekends: revisited
Jeff Amiel wrote:
Back in 2002, Jean-Luc Lachance gave a nifty algorithm
(http://archives.postgresql.org/pgsql-sql/2002-06/msg00301.php) for
determining the resulting date given a starting date and number of
'workdays' in the future. The trick was that weekends (Saturday and
Sunday) could not be counted.
...
What am I doing/interpreting wrong?
I'm not sure about the algorithm. I can tell you, however, that such an
algorithm is locale dependent.
For example, in Israel the work week is Sunday-Thursday. Weekend is
Friday and Saturday. I'm fairly sure that there are places around the
globe in which a work-week is six days long.
Shachar
--
Shachar Shemesh
Lingnu Open Source Consulting ltd.
Have you backed up today's work? http://www.lingnu.com/backup.html
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] Version 1.0.0.19 of OLE DB released
Hi all, Version 1.0.0.19 has been released. This provides better backend mode detection for time and timestamp handling. With this version all known type related problems are now fixed. I repeat - if you have been postponing checking out OLE DB because you have heard that it is still alpha, now is the time to check it out. If you still find problems, please post them to [EMAIL PROTECTED] The new version can be downloaded from http://gborg.postgresql.org/project/oledb/download/download.php. Share and enjoy. Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. Have you backed up today's work? http://www.lingnu.com/backup.html ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] [Oledb-dev] A major rewrite of the Postgres OLE DB Provider.
Jeremy Lea wrote: > - Runs multiple queries (using PQexec) if you pass a command with no >parameters and IID_NULL for the return record set. > You should be aware that there is a security advantage to NOT being able to do that, in fact. On the plus side, it does allow servers older than 7.4 to work with the provider, under certain cases. On the don't-care side, so what? > - Proper type handling. > I'd love an explanation in what, you think, was missing. > - Much cleaner code (IMHO), no more STL or exceptions. > A matter of taste. I think this design breaks every OOD instruction out there (read - no switch-case on child classes). > - IMultipleRowsets. libpq can't return multiple results. What was >there was a complete hack. > No, it was an interface between provider and server. It was documented, and was used. > - Parsing of parameters on stored procedures. This was a hack, Yes, but the hack is in the OLE-DB's specs. Quite a number of programs don't work without it. > which >was being done at the wrong place in the code. > I'd love it if you elaborated on that. > - Support for some types (_text, utinyint, varcharci) > - TIMESTAMP WITH TIMEZONE is reported in GMT not local, since there is >no OLE DB type for this (so you can't go backwards). > If you cannot support data-types that are implemented in PG, and have a reasonable representation in OLE DB, what makes this type support "proper"? Also, if I understand my very brief passing over the code correctly, if I have two coloumns, one of type timestamp and one of type timestamptz, and I read both in, and then send a parametrized update for both to the same values I read, one of them will change. > - check threading and locking. > Unless you switched the provider from "apartment" mode (and I see you haven't), you don't need to. It will run in a single thread anyways. > I hope this is useful to someone. I would welcome any patches to add > other features. I'm managing this in a local CVS repository at the > moment... Not quite sure what I'm going to do about this and the > PgFoundry project yet. > Then allow me to make a suggestion, if I may. We're already (and I am, at least to some extent, to blame for the lack of activity here) in a pretty bad place. Having two forks on a project that only lists one developer means something has gone very wrong with the project already. In fact, we now have as many forked versions as we do developers. If the mailing list was swarming with rejected patches, I'd understand this situation. As is, I think this was totally uncalled for. The way to manage open source projects is to "release often, release early". This gives you early access to feedback (and, at the very worst, is still not worse off than keeping a local copy). For example, if you started sending patches when you just started off, I'm sure a more reasonable discussion of what is the best way for the type system to be like, thus possibly leading to an implementation that was: 1. Single - i.e. less confusing for the users. 2. Better than either existing implementations. It's obviously too late for that now. What I suggest is this: Open a new pgfoundry project for your provider. Please also rename it. It is confusing enough to have two driver implementations. Having two driver implementations with the same DLL name is really too much. I think that it would be best if you did NOT open a new mailing list for the fork, but used oledb-devel, at least initially. I think that it would be best, long term, to only have one provider, and a unified list will help get the community to vote on which one it should be. Finally, a word of caution. The ATL code is copyrighted by Microsoft. The license under which they gave you the code (appears as part of EULA.TXT in Visual Studio 6) is NOT LGPL compatible (nor any other free license compatible, but you are bound to LGPL by my copyright). This means, as far as I can tell, that you are not allowed to copy code from ATL and include it, modified or not, in the provider's code. Had you send me those pieces of code as patches, they would have been rejected immediately for that reason alone. Here is the relevant section from the VS6 EULA (emphasis mine): > 3.8Redistributable Code-Visual C++ and Visual Studio: Microsoft > Foundation Classes (MFC), Template Libraries (ATL), and C runtimes > (CRTs). If this EULA accompanies Visual C++ or Visual Studio, then in > addition to the rights granted in Section 1, Microsoft grants you the > right to use and modify the source code version of those portions of > the SOFTWARE PRODUCT that are identified as MFC, ATL, or CRTs > (collectively, the "VC Redistributables"), for the sole purposes of > designing, developing, and testing your software product(s). Provided > you comply with Section 4.1 and you rename any files created by you > that are included in the Licensed Product (defined below), Microsoft > grants you a nonexclusive, royalt
[GENERAL] sequencing two tables
Hi list, I need to create a list of all rows of the combination of two tables. For reasons I can't go into, I need this list to be in a single query, and not write a function to do it. I thought about doing something like this: select |COALESCE(table1.field, table2.field) as field from table1 full outer join table2 on false There is just one small problem. Even though logically this does what I'm trying to do, postgres says: "FULL JOIN is only supported with merge-joinable conditions". I don't even understand the error message, much less whether I'm doing anything wrong, and whether I'm missing something stupifyingly obvious here. Help? Postgres version is 7.4 Shachar | -- Shachar Shemesh Lingnu Open Source Consulting ltd. http://www.lingnu.com/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] sequencing two tables
Stephan Szabo wrote: We don't support join conditions like the above at this point. The message isn't terribly helpful though. Purely out of interest, what does that message mean? Why aren't they supported? Fortunately, I think some variant like select table1.field from table1 union all select table2.field from table2; will give you the output you're looking for. It's exactly what I'm looking for. Thank you and Tom. Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. http://www.lingnu.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] ADO adCmdStoredProc PlPgSql-SP Parameters
[EMAIL PROTECTED] wrote: Hi, How do I call a StoredProcdure, written in PlPqSql which returns a set of records (or tableWhatever) and wants parameters, from ADO while using "adCmdStoredProc" ? Which driver are you using? If it's the OLE DB provider, then there is a solution, provided that you modify your stored procedure a little. You can probably write a wrapper procedure, if that's not acceptable as it is. If your function returns "refcursor", then OLE DB will interpret that to mean that the function has rowset returns, and will emulate the standard calls surrounding returning multiple results. Please feel free to let me know if this solves your problem. Please CC me on replies. Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. Have you backed up today's work? http://www.lingnu.com/backup.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] postgres server failure during some database opearion
Surabhi Ahuja wrote:
I have a query regarding, the postgres Sever failure, for eg say i am
parsng a file and inserting rows into a table. Now while this
opeartion is taking place the server crashes, or say the power goes
off, or say someone kills the postmaster demon.
what happens to the rows that have been inserted till now? can we get
a status of how many rows have been installed or not?
I highly suggest you read about "transactions". That's exactly what they
are here for.
If you opened a transaction for the entire insert operation, or if it is
done in a single operation (say - insert ... from select ... ), then
none of the rows are in the database, and you can start the entire
operation over with no risk for data integrity.
If each operation is in a different transaction then it is up to you to
put some mechanism in place to count how many rows got in. For example,
assuming you don't need to support multiple clients simultaneously and
you can figure out by the last row inserted which is the next one, you
can can open a transaction, insert a row, update a second table with the
identifier of the last row successfully inserted, and flush the
transaction. This way, you can always know which is the last row
inserted in a reliable way.
From the sound of it, you really just want to perform the entire insert
operation inside one transaction.
regards
surabhi
Shachar
--
Shachar Shemesh
Lingnu Open Source Consulting ltd.
Have you backed up today's work? http://www.lingnu.com/backup.html
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] OLEDB and the port number
Craig Bryden wrote:
Hi
Can anyone help me with the oledb driver.
I have RC1 of Postgres 8 installed on the default(5432) port. I have the
full release version installed on port 5433. I am trying to get the OLEDB
connection to look at port 5433. Below is my connection string. It works for
the RC1 installation (on port 5432).
Location=MyDBName;Provider="PostgreSQL.1";User ID=MyUser;Data
Source=localhost;Extended Properties=;Port=5433;Password=Mypassword";
Thanks
Craig
OLE DB does not support getting custom parameters from the user at this
time. This will probably be fixed in the near future (read - about a
month's time frame, no promises). At the moment, I am very much afraid
there is no way to achieve what you are trying to do.
The two most obviously missing features are port number and SSL support.
Sorry about that.
Shachar
--
Shachar Shemesh
Lingnu Open Source Consulting ltd.
Have you backed up today's work? http://www.lingnu.com/backup.html
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Windows Library for libpq
Karam Chand wrote: Hello Thanks for all the reponses yesterday for my queries regarding moving from MySQL to PgSQL. As I see there is no native port of POstgresql for Windows. My applications is being developed in Windows primarily due to better editors. We are using wxWindows cross platform toolkit to develop the application. I searched the sitebut i couldnt find a link...where can I get compiled libraries for libpq and its coreresponding header files for Windows. Regards Karam Hmm, I need to PROVIDE this for the oledb project on gborg. I would rather not put it inside the OLE DB project, as it doesn't really bear any relation to it. In any case, Karam, I'll uptload it to my project and let you know on this list. In the mean while, http://www.postgresql.org/docs/7.4/static/install-win32.html has instructions on how to compile it yourself. -- Shachar Shemesh Lingnu Open Systems Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Windows Library for libpq
Karam Chand wrote: Hello I have read the docs.you need to install PostgrSQL using cygwin etc. I am not in a position right now to do that :) Are no standard libpq.lib for Windows available? In MySQL you get a precompiled library for C API and that is very helpful. Can some body mail me a compiled library of C API for windows. That will be very helpful... Karam A. Please reply to the list as well. B. The link I sent you does not require cygwin, and will in fact work better without cygwin. Like I said before, I will compile it and post it, I just think it's the wrong place to put it. I will let you know when I did that. Shachar -- Shachar Shemesh Lingnu Open Systems Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Windows Library for libpq
Shachar Shemesh wrote: Karam Chand wrote: Hello I have read the docs.you need to install PostgrSQL using cygwin etc. I am not in a position right now to do that :) Are no standard libpq.lib for Windows available? In MySQL you get a precompiled library for C API and that is very helpful. Can some body mail me a compiled library of C API for windows. That will be very helpful... Karam A binary for libpq.dll is now available for download from http://gborg.postgresql.org/project/oledb/download/download.php. Enjoy. -- Shachar Shemesh Lingnu Open Systems Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Tom Lane heads up
DeJuan Jackson wrote:
Just dropping a quick not for Tom Lane. I sent a personal message
today, but I wasn't sure if you'd get it after I remembered all of the
spam filters you've got set up.
Sorry for the off topic post.
That's ok. He is only filtering me :-)
Actually, you get a rejection notice if his spam filters catch you. If
you didn't get one, your'e ok.
Shachar
--
Shachar Shemesh
Lingnu Open Systems Consulting
http://www.lingnu.com/
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] ole db
Grant Allen wrote: It's a dead project ... Used to be dead. Not any more. I couldn't find any useable output (that's more an indictment of my coding skills than anything else). Then you haven't tried over the past month or two. The version released there already has some results support. The CVS is much better already. I'm attempting to get some sort of basic functionality ordered out, and then I'll do another release. I signed up and contacted the other two developers listed, but never got a response. If you're keen on the OLE DB / ADO play ground, you'll need to use the OLEDB-ODBC bridge with the ODBC driver. Yes, so did I. I then contacted the hackers list. I then opened a new project, at which point the gborg admin just handed me control over the old project. Along the way, I found out the email for the old project maintainer, as well as another group which is not tightly related to postgres who began work on similar project. They are all subscribed to the oledb-dev mailing list (though I have not heard from any of them of late). At the moment, the ole-db driver (the CVS version) is quite capable of doing basic queries (yes, including getting the results to the app :-). I'm slowly putting more stuff in, but would love to get any help with this you (or glenn) can offer. For example - current version still does not properly report NULL columns, resulting in junk results in columns with no data. Still loads of work ahead, but defenitely better than it used to be. Instructions on how to pull the CVS version and compile it can be found at http://gborg.postgresql.org/project/oledb/cvs/co.php/oledb/INSTALL?r=1.3 Ciao Fuzzy :-) Shachar -- Shachar Shemesh Lingnu Open Systems Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Version 1.0.0.4 released
Hi all, Version 1.0.0.4 of the Postgresql OLE DB provider for Windows was just released. Get it at http://gborg.postgresql.org/project/oledb/download/download.php?branch=devel. Release includes a source only version, and a compiled version. The compiled version will require libpq (precompiled version available at http://gborg.postgresql.org/project/oledb/download/download.php). New features since 1.0.0.1 (previous released version): * Correctly reports cells with NULL values. * Most numeric and textual types handled * Generic infrastructure for handling new types, including types for which only the name is known at compile time (user-defined types). * Transactions are now supported (though row-updates are not yet supported). All in all, this version should provide the basic functionality for many day to day uses. The provided compiled version is of a Debug build, which means that stepping outside of the handled path will probably result in ASSERTS. The provider requires libpq version 7.4 or greater, and a backend of version 7.4 or greater. The later requirement may be relaxed in the future. -- Shachar Shemesh Lingnu Open Systems Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Stored procedures output variables
Hi List, How do I define a stored procedure that has an output variable? I am looking for a way that will not significantly change the way the argument is called. Can I, for example, pass a reference to variable in some way? Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] [HACKERS] Remove MySQL Tools from Source?
Tom Lane wrote: These tools are a not insignificant part of our Plan for World Domination ;-) so it would be good if somebody stepped up to the plate and volunteered to take care of 'em. Anybody? Which brings me to another question I have a bunch of perl scripts, as well as one user-defined type, for porting from SQL Server. Where should I place these? Inside the PG source seemswrong. Then again, gborg does not seem to be accepting new projects at the moment. I can put them on sourceforge/berlios etc, and ask for a link, if you like. Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] How can I do conditional 'drop table' in Postgres
Dragan Matic wrote: if exists (select * from sysobjects where id = object_id(N'[dbo].[pp_fisk]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[pp_fisk] GO For instance, this is a valid script in Ms SQL, it will drop table pp_fisk only if it exists, is there a way to do something similar in Postgres? Tnx in advance. Dragan I'm working from memory here, so exact syntax might be incorrect. perform select * from pg_table where ; if found then drop table ... end if This applies to plpgsql only. Please pay attention, however, that if you are going to be dropping and recreating tables from this function, you must have all queries relating this table use "execute". Otherwise, the table's OID is going to be cached the first time code referencing this table (in "from") is run, and subsequent runs in the same session will not find the table (even if you create a new table with the same name). If that is a problem for you, consider replacing "drop table" with "delete from table", which will delete all elements form the table, but leave the table itself. Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Mixed Case column names (php 4.3.5 & pg 7.4.2)
Matthew Morvant wrote: Typo on that post. Here is what is should be: echo pg_fetch_result($rsTest,0,"ridStudent"); which still doesn't work. Try: echo pg_fetch_result($rsTest,0,"\"ridStudent\""); Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Windows Library for libpq
Shridhar Daithankar wrote: Karam Chand wrote: I searched the sitebut i couldnt find a link...where can I get compiled libraries for libpq and its coreresponding header files for Windows. HTH Shridhar Try at the OLE DB project. http://gborg.postgresql.org/project/oledb/projdisplay.php. That will not, however, do you any good for compiling, as it contains the runtime DLL, not the headers and link time libraries. On the other side, the readme that comes with the project has some links for how to create this library yourself, in a pretty straight forward way. Shachar -- Shachar Shemesh Lingnu Open Source Consulting http://www.lingnu.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] porting a full Ms Sql Server to postgres
[EMAIL PROTECTED] wrote: Hi, I'm looking for details, or tools about porting a full Ms Sql Server 2000 database to Postgres on Linux. Is this possible? We have the whole nine yards, stored procedures, triggers, and all. Is there a way to automate this process? Thanks Totally automate - no. There are tools to help you along, however. They are not as complete as I would like them, and I need lots of help in maintaining the site. It's located at http://pgfoundry.org/projects/sql2pg/, but there are currently no released files. In CVS, however, you will find a perl script that translates the exported database description from MS SQL to a more-or-less PG friendly format. Also you will find there support for two data types - varcharci (case insensitive) and utinyint (1 byte unsigned integer). Any help with maintaining this project would be greatly appreciated. I do have a few questions for you. What drivers do you currently use to access your database? Do you use the stored procedure's ability to return more than one result? Shachar -- Shachar Shemesh Lingnu Open Source Consulting ltd. http://www.lingnu.com/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
