It sounds like you are not very familiar with ODBC. If you haven't done
anything in the Windows Control Panel, then you likely do not have the
PostgreSQL ODBC driver installed, nor did you create the DSN necessary.
Please note your issues have absolutely nothing to do with the fact
PostgreSQL currently runs under Cygwin, as you'll likely have the same
problem when PostgreSQL goes native on Windows. The issue isn't the
software, it's your understanding/use of ODBC.
Without going into much detail (I'll leave that for you to Google), the
whole point of ODBC (Open Database Connectivity) is to be "A standard
method of sharing data between databases and other programs. ODBC
drivers use the standard Structured Query Language (SQL) to gain access
to data from sources." If you are familiar at all with databases, you
will know that every one has its one 'flavor' of a query langauge. In
the case of SQL-based DBMS systems (MySQL, PostgreSQL, MS SQL Server,
Oracle, etc.), this can wreak havoc on your ability to get the data
you're after, since some have commands/parameters which others do not.
ODBC creates another shim/layer between you/your app and the database.
Prior to ODBC, you would hook in code which requires a SPECIFIC db
backend (like PostgreSQL)--for example, writing a C program which uses
the PostgreSQL API. This unfortunately meant your software could ONLY
use that one DBMS, since the API was for that DBMS.
[your app] <---native calls---> [DBMS]
If you wanted to change your DBMS, you would have to rewrite your app to
use a different API for the new DBMS. This gets old fast.
Now, you write your app to make ODBC calls. Those calls in turn are
handled by an ODBC driver which maps ODBC calls to your DBMS of choice.
Then your DBMS does the work and passes back the results via the ODBC
driver to your app. If you decide you don't want to use DBMS #1
anymore, all you need to do is install DBMS #2 and ITS associated ODBC
driver, configure a DSN with the same name as you had for DBMS #1, and
your app will never know the difference.
[Your app] <---ODBC calls---> [ODBC driver] <---native calls---> [DBMS]
ODBC drivers are to a DBMS what a video driver is for a video card.
Once you install that new gee-whiz 512-bit ooh-aah video card into your
Windows PC and install its associated video driver, do you ever worry
that your applications won't be able to display on the screen? No. As
long as the driver supports the generic DirectX or OpenGL calls, you're
in business. Ditto with ODBC.
Now here's the thing. If you want to use PostgreSQL, you need to
install the PostgreSQL ODBC driver. You can find the latest one here:
Your mileage may vary, but I tend to install the MSI version.
Next, assuming you have PostgreSQL installed and running, you create
your database using pgsql or whatever client tool you prefer (e.g.,
Then you go to your Control Panels, and this will vary from one version
of Windows to the next, but as an example, in Windows XP you'll find this in
'Performance and Maintenance' | 'Adminitrative Tools' | 'Data Sources
Here you will want to click on the 'System DSN' tab, then click
[Add...], and here comes the key part: knowing which DBMS your data is
stored in, find its matching ODBC driver. If you installed the psqlODBC
driver above, it should list 'PostgreSQL' as an option. Select the
appropriate ODBC driver and click [Finish].
At this point, you get a dialog box where you fill in the meat. Note
the DSN name ('Data Source') is the key, as this is what you'll code
into your app. The rest of the information is where you describe the
backend DBMS setup.
Data Source: <DSN> (e.g., MyApp)
Database: <name of PostgreSQL database> (e.g., MyAppDB)
Server: <DNS/IP of PostgreSQL server> (e.g., myserver.com)
Port: <TCP port PostgreSQL listens to> (e.g., 5432)
Username: <PostgreSQL username w/ access to database>
Password: <PostgreSQL user password>
Once you've done this, all you need to do is put the DSN name into your
app, whether written in Perl, C, whatever. In the future, if you decide
you don't like PostgreSQL and would rather use MS Access (yeah, right),
then all you have do (once you've obviously moved the data from one DBMS
to another) is come back to the ODBC Control Panel, delete out the ODBC
setup you created above, and add one for your new DBMS, making sure to
use the same 'Data Source' name. Voila! Your app now uses the new DBMS.
The centerpiece of ODBC is at the OS level, since at the core everything
revolves around the DSN name chosen.
A few notes regarding PostgreSQL ODBC:
1. Note above that Port: is one of the options. Though I can't say for
certain, this to me indicates that PostgreSQL ODBC requires that you
setup your PostgreSQL server to listen via TCP/IP (as opposed to
using the Unix sockets approach). This means you had better be
familiar with configuring PostgreSQL, which typically involves
making changes to a few config files, now typically located in
The files you want to familiarize yourself with are pg_hba.conf,
where you set your security (what IP addresses/users can access
PostgreSQL, etc.), and postgresql.conf, where you'll need to
specify that PostgreSQL should use TCP/IP (tcpip_socket = true)
and specify the port to listen on (5432 is the default).
2. If this seems like a bit of work, note that no matter what DBMS
backend you intend to use, the steps won't really change above.
You must install an ODBC driver for the DBMS you intend to use
(unless it's already part of the default Windows install). You
must create a database. And you must create a DSN which maps to
that database. So if you're intent on using ODBC, be aware of
It seems you're trying to do several things all at once. I suggest you
take it one step at a time and learn each piece of the puzzle. If
you're new to Cygwin, toy with it a bit, especially if you're new to
Unix-type environments. If you're new to databases, learn what they
truly are/do and how to manage them. If you're new to Perl, that's a
whole new language right there.
Regarding which perl you run when you type a command, that's actually
quite easy if you understand how Windows works. You say you associated
the Perl extensions to ActiveState's Perl. This means if you
double-click on a .pl file it will run ActiveState's Perl...always. If
you type a .pl file on the command line by itself, same thing. This is
because it's the only way Windows can figure out what to do with the
file, by its extension.
Now when you type a command like 'perl filename.pl', here you had better
know what your current PATH is. Simply typing 'set' should give you
your current environment, in which you should find a line called 'PATH'.
Note which directory comes first, ActiveState's or Cygwin's /bin
directory. Typically you'll find ActiveState's showing up first if
you're at a Command Prompt, whereas if you're running the Cygiwn BASH
shell, since Cygwin adds its /bin directory to the front of the PATH,
then it's typically Cygwin's perl.
Another way to tell is, just before executing your actual perl script,
just type 'perl -v' and see which version you get (ActiveState has their
name in their version info). That's the one you'll be running when you
type 'perl filename.pl' from that location.
Hope something in the above is useful. Good luck with what you're doing.
- Barry - wrote:
> Hi Mike,
>>In cygwin the perl module is named DBD:Pg.
>>In activestate the same perl module is named DBD::PgPP.
> I think DBD::Pg and DBD::PgPP are two different modules, at least to
> Activestate and Cpan. If Cygwin only includes DBD:Pg and it's the same as
> Cpan's and Activestates DBD::Pg, then I don't think it's what I want. I
> heard that DBD::Pg requires an old version of MSVC, and I know someone who
> couldn't get DBD::Pg to work on Windows even with Cygwin and MSVC, so
> DBD::PgPP was suggested to me, but DBD::PgPP has alot of non-core module
> dependencies and is only up to version .05, so I want to avoid that too.
> Then someone suggested ODBC, which is what I'm having a problem with.
>>DSN should be replaced with the name of odbc connection you created in
> your DBI connect statement.
>>If you used the default when creating the odbc in the control panel->data
> sources I believe it is named Postgresql.
> I tried replacing "DSN" with "Postgresql" and "PostgreSQL" and I got the
> same error. I don't remember doing anything with any control panel, and I
> don't know where to find the proper DSN. I see now that if I searched the
> DBI module documentation for "environment" rather then "environmental" I
> would have learned which environmental variable to set, but I still wouldn't
> know what to set it with.
> If I remember correctly, I just followed the first two instructions at
> http://www.postgresql.org/docs/faqs/text/FAQ_MSWIN then I decided not to do
> the basic installation because I don't want to be limited, so I followed the
> "NT services Cygwin PostgreSQL installation procedure" at
> http://www.tishler.net/jason/software/postgresql/postgresql-7.3.4.README . I
> had to search for the solutions to many problems that I encountered along
> the way--in other words, the instructions didn't work. I found most
> solutions, including the proper way to set environmental variables, other
> packages that needed installing, and chmod commands that "usually" solve one
> of the problems (the last email to this list, from David, lists those
> commands, which are included in the instructions I was following, but in a
> bad place), but now I have this problem with the DSN. This is why I didn't
> read the entire documentation for DBD::ODBC and DBI. I had no confidence
> that it would have helped me and I needed step-by-step confirmation that
> stuff is working, starting with an initial connection to the database.
> I'm seriously considering avoiding anything that requires Cygwin. I'll
> either use MySQL or I could build a suitable storage solution for my needs
> in less than one day. Trying to get PostgreSQL to work has taken me a week,
> and I'd still have to learn SQL after that.
> Sorry if this post isn't in the spirit of this list. I appreciate the help,
> but I just about had enough.
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
In response to
pgsql-cygwin by date
|Next:||From: David P. Lurie||Date: 2004-06-15 19:45:26|
|Subject: Re: Updated Cygwin Package: postgresql-7.4.2-1|
|Previous:||From: Jason Tishler||Date: 2004-06-15 11:03:04|
|Subject: Re: Updated Cygwin Package: postgresql-7.4.2-1|