Re: Source name not found

I'm a newbie to PostgreSQL and cygwin, and I'm having a problem with ODBC.

When I paste the path of the below script to Cygwin, to connect to a
database using Perl's DBD::PgPP module, it seems to work because I get no
error message, but when I comment-out the $dsn definition that uses PgPP
and uncomment any of the other $dsn definitions, which use ODBC, I recieve
the error:

"DBI connect('DSN','postgres',...) failed: [Microsoft][ODBC Driver
Manager] Data source name not found and no default driver specified
(SQL-IM002)(DBD: db_login/SQLConnect err=-1) at [file name] line 14"

I don't expect the code to do anything useful. I'm just getting started
with PostgreSQL and I wanted to test what little I learned so far.

At http://testers.cpan.org/show/DBD-ODBC.html it says that the latest
DBD::ODBC versions fail for Windows, but I know that ActiveState's version
passes. I have ActivePerl installed along with Cygwin's Perl. When I
configured ActivePerl, I had .pl files be associated with it, and I'm not
sure what Perl is used when I paste "perl <path>" in Cygwin, or if there's
a conflict, or if my error is caused by Cygwin's version of DBD::ODBC not
working for Windows XP.

My search for DBD::ODBC using Cygwin's mailing list search tool returned
nothing, but searching Google got me the following Q and A from
http://www.cygwin.com/ml/cygwin/2001-08/msg00254.html:

>> Is there any pre-build perl modules for the perl in Cygwin?
Particularly DBD::Mysql, DBD::ODBC.

> No need for them to be pre-built. Get them from CPAN. Have a look at the
DBI mail list archives or these mail archives for any further
information.

I have a feeling that information is out of date. Do I have to build
DBD::ODBC?

Thanks,
Barry

---------------------------------------------

use DBI;
#
#
#
$user = 'postgres';
$password = 'omitted';

###$dsn = 'dbi:ODBC:DSN';
###$dsn = 'dbi:ODBC:dbname=template1';
###$dsn = 'dbi:ODBC:dbname=template1;host=localhost';

$dsn = 'dbi:PgPP:dbname=template1;host=localhost';

$dbh = DBI->connect($dsn, $user, $password, { RaiseError => 1, AutoCommit
=> 0 });

Hi,

I believe if you do perl nameoffile it executes cygwin perl.
If you do just nameofofile.pl then activestate perl is used.

In cygwin the perl module is named DBD:Pg.
In activestate the same perl module is named DBD::PgPP.

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 used both cygwin and activestate perl on XP and done't have any problems connecting.

I don't have my work pc running at the moment but I don't believe I reference the Perl ODBC module in my scripts. Just the Pg modules.

You can run CPAN from within a cygwin shell and install Perl modules for cygwin just as you would for activestate.

There is also a postgresql odbc driver for windows that can be downloaded off the gborg site.

HTH.

Mike

On Sun, Jun 13, 2004 at 12:59:25PM -0400, Barry wrote:
> I'm a newbie to PostgreSQL and cygwin, and I'm having a problem with ODBC.
>
> When I paste the path of the below script to Cygwin, to connect to a
> database using Perl's DBD::PgPP module, it seems to work because I get no
> error message, but when I comment-out the $dsn definition that uses PgPP
> and uncomment any of the other $dsn definitions, which use ODBC, I recieve
> the error:
>
> "DBI connect('DSN','postgres',...) failed: [Microsoft][ODBC Driver
> Manager] Data source name not found and no default driver specified
> (SQL-IM002)(DBD: db_login/SQLConnect err=-1) at [file name] line 14"
>
> I don't expect the code to do anything useful. I'm just getting started
> with PostgreSQL and I wanted to test what little I learned so far.
>
> At http://testers.cpan.org/show/DBD-ODBC.html it says that the latest
> DBD::ODBC versions fail for Windows, but I know that ActiveState's version
> passes. I have ActivePerl installed along with Cygwin's Perl. When I
> configured ActivePerl, I had .pl files be associated with it, and I'm not
> sure what Perl is used when I paste "perl <path>" in Cygwin, or if there's
> a conflict, or if my error is caused by Cygwin's version of DBD::ODBC not
> working for Windows XP.
>
> My search for DBD::ODBC using Cygwin's mailing list search tool returned
> nothing, but searching Google got me the following Q and A from
> http://www.cygwin.com/ml/cygwin/2001-08/msg00254.html:
>
> >> Is there any pre-build perl modules for the perl in Cygwin?
> Particularly DBD::Mysql, DBD::ODBC.
>
> > No need for them to be pre-built. Get them from CPAN. Have a look at the
> DBI mail list archives or these mail archives for any further
> information.
>
> I have a feeling that information is out of date. Do I have to build
> DBD::ODBC?
>
> Thanks,
> Barry
>
> ---------------------------------------------
>
> use DBI;
> #
> #
> #
> $user = 'postgres';
> $password = 'omitted';
>
> ###$dsn = 'dbi:ODBC:DSN';
> ###$dsn = 'dbi:ODBC:dbname=template1';
> ###$dsn = 'dbi:ODBC:dbname=template1;host=localhost';
>
> $dsn = 'dbi:PgPP:dbname=template1;host=localhost';
>
> $dbh = DBI->connect($dsn, $user, $password, { RaiseError => 1, AutoCommit
> => 0 });
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match

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.

Barry

>
> 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.

Ok, I would guess that the Perl module looks for an existing odbc connection that existed on that same client. Did you install the postgresql odbc driver on the pc you are trying to connect from? That is where the DSN name would come from. For ODBC a connection must be created on each client. It can be manually done through the control panel. You could try connecting to the db through another program like MS Access which would use an ODBC connection. Then you would know it is a problem with the Perl ODBC module for sure. I don't know if the driver is included in the perl odbc module for windows. I installed the postgres odbc driver from gborg well before using the Perl modules.

Things have changed a bit from 7.3.4 which is the link mentioned below. The default install from cygwin has been 7.4.1 for a while. 7.4.2 was just released in cygwin. I haven't tried to upgrade from .1 yet.....

Mysql has it's own plusses and minuses. Can't answer which one would be better for your situation. Sorry.

Mike

In a few months a native windows version of Postgresql will be available so if you really want to avoid cygwin and can wait a bit that would be an option.

>
> 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.
>
> Barry
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

Barry,

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.

Visually:

[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.

Visually:

[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:

http://gborg.postgresql.org/project/psqlodbc/genpage.php?downloads

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.,
pgAdmin III).

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
(ODBC)'

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

/var/postgresql/data

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
this.

FINAL COMMENT:

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.
>
> Barry
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

Thanks for all the details. A previous reply to my post led me to the driver
and where to do the Windows configuring, and I was just about to continue
the database setup, but now it seems even more complicated. I have a single,
home PC, and I wanted the database to run from this one computer. Possibly,
in the future, a second home computer would write to the database. I don't
know where to find the DNS/IP of the "server" or whether there is one, I'm
not at all familiar with configuring PostgreSQL, and I'm generally uncertain
about much of what I've quoted below. I've lost interest (and lots of time)
in this.

I thought I'd be able to find a simple alternative to saving data in regular
files. I could have easily created a storage solution that meets my needs
with Perl, even without getting fancy with a function like seek, and another
option was Perl's DBM::Deep module, but I figured a regular database would
make accessing the data faster and easier once I learned SQL or one of its
flavors. It's debatable whether I should have bothered even if it were as
simple as I thought. As it turns out, there was no installer, the
instructions didn't work, the fixes I found elsewhere only got me to the
command prompt interface, and it now sounds like that wouldn't have worked
even if I didn't need access from Perl.

All this it too much. There's too big a learning curve from my old methods
of flat file storage to this, and the only help I've found at this stage is
through helpful people like you who are willing to take the time to explain
some of the things involved, rather than through the instructions that I've
been following or links from them to information I need to complete the
installation.

Thanks for your help everyone, but no more databases for me.

Barry

>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)

> 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
>
> /var/postgresql/data
>
> 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).

Barry,

Sorry to hear that the experience has left you disinterested. DataBase
Management Systems (DBMS) are, by their very nature, a bit of a
challenge, though. When it comes to any decent DBMS, there's all the
system admin challenges (install / config / running / making DBMS work
on a network / etc.). Then there's the DBA challenges (managing the
datastores themselves, creating databases where users do their storage /
queries / etc.). Then there's the user/developer end (learning SQL,
doing the 'work' as it were, etc.). When you're wearing all the hats,
it can be a bit much. I know. I have a LOT of hats. :-)

Of course, just to be clear on a few things, you appear to have made
things more complicated than possibly necessary. Might I offer some advice?

DBMS can be fantastic storage facilities for data (the name kinda
implies it, right?). But their relative complexity usually scales with
their performance ability. That is, for simple db tasks, apps like MS
Access can be great, since they store the entire database
(tables/forms/reports/etc.) all in a single file. And MS Access is
'easy' to use with its GUI and commonality to other Office apps.

But MS Access starts to suffer as you throw more data / interaction at
it. So then you might look at more 'serious' systems...MySQL (very fast
in solutions doing most reads/queries)... PostgreSQL (far more ANSI SQL
compliant and kind of a poor man's Oracle)... MS SQL Server.... But
you'll note that the amount of skill/time needed for these also grows.

Finally, if you're a really serious user (financial markets, whatnot),
either a commercially supported version of MySQL/PostgreSQL/<other open
source DBMS> with possible additional features like replication,
built-in backup/recovery solutions/etc., or you go get a serious,
commercial, proprietary DBMS like Oracle, IBM's DB2, etc. At this point
you may be looking at consulting time, hiring people to handle the
individual tasks mentioned earlier, and so on. More skill/time/money again.

When I look at using a database as a solution to a problem, I basically
walk the following list to see where I need to be:

1. flat text file
* good for simplest tasks
2. SQLite (www.sqlite.org)
* embedded DBMS; small, fast, multi-platform library
* works with many languages (including Perl; see site)
* stores all data in single file which is binary compatible
across OSes
* limited to situations where you embed DBMS into your app
(i.e., not for client/server, high-volume connections, or
setups where DBMS runs on one box, your app on another)
3. MS Access or similar
* fine for office db needs where user needs quick/dirty
simple solution
4. MySQL
* far better performance than MS Acces
* various GUI frontends exist
* free for toying but with option for paid support/etc.
* has db replication capability built-in
* very popular with lots of code/support/whatnot available
* excellent in environs where you mostly do reads/queries
5. PostgreSQL
* excellent ANSI SQL support; great for learning/using SQL
commands at every level (basic queries, creating tables/etc.,
and doing dba work)
* again, free for toying and even high-end use, with options
for commercial support if needed.
* again, various GUI frontends exist
* not as popular as MySQL, but depending on tasks, better suited
* easier to port other DBs to like Oracle, due to its strong
SQL support
6. Oracle/IBM DB2/etc.
* when you can't afford any downtime, want someone else's butt
to chew out when there are issues, and you have deep deep
pockets...

To be perfectly honest, I'm not a fan of MS SQL Server, but if I had it
in the list, it would be at the same level as MySQL or PostgreSQL. (I
just can't personally see a need for an expensive, proprietary solution
when a free, open-source better one exists, but that's me).

Now in your case, it sounded like you're tackling quite a few things,
which may in part be why you feel so overwhelmed. Perl, Perl DB
modules, Windows, Cygwin, PostgreSQL, ODBC, SQL... that's a good bit of
stuff to get through when you "don't expect the code to do anything
useful." :-)

One step: skip ODBC and use a Perl DB module which talks directly to
PostgreSQL. That's one possibility.

More than likely, however, you might want to look into something like
SQLite instead, at least for starters. At least 2 wrappers exist for Perl:

http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers

With this, all you do is write your Perl scripts using the same kind of
Perl DBI. The difference is that there's no DBMS administration, no
installation, no nothing. SQLite generates a single, binary file to
store all the tables, data, etc. It's about the easiest way to use a
SQL engine there is.

Once you're comfortable with the DBI coding and SQL itself, if you
should write an app that scales or requires the DBMS to be on a
different PC than the Perl script, at this point try to come back to
PostgreSQL. Yes it requires more work. I doubt anyone would deny that.
And though right now it may not seem that way, I don't know anyone
who's more committed to helping folks run PostgreSQL under Cygwin than
Jason Tishler. Not only does he write up the READMEs, but he's the one
who compiles the binaries which you can install by simply using Cygwin's
setup.exe. And those READMEs, though may appear overwhelming now, will
make more sense as your experience grows.

I've been where you seem to be at right now (at least in your
frustration with PostgreSQL). But please don't let it deter you. I had
years of DBMS background before coming at PostgreSQL, and though the
initial install/setup was no point/click affair, once you've seen what
it can do and are familiar with the various systems out there,
PostgreSQL's actually quite a robust piece of software.

It's just that right now it appears you're trying to kill a fly with an
elephant gun. Anyway, just some food for thought. Best of luck
whatever you choose to do.

P.S. Apologies on not explaining clearly about DNS/IP addresses, etc.
Yet one more piece of the puzzle: networking. In Windows, at
the Command Prompt type the command
ipconfig
and it should tell you what your PC's IP address is. Networking
is what I do for a living, so I got a little flippant. But to
keep it simple, if you want to run a database that you can
access from a network, there must be some 'address' that you can
reach the database at, does that make sense? Kinda like knowing
my phone number if you want to call me.

The most common network system in the world is the Internet,
which relies on a networking protocol known as TCP/IP. Though
explaining networking is well beyond an email like this, just
note that every computer on a network must have a unique address
or things just get ugly. Imagine if we only had first names.
How would we communicate with each other? "Hey Frank?" Now
who would answer?

Depending on your network setup, you may be using
something like DHCP which 'dynamically' provides your PC with
an address. This is like getting a different phone number each
day you wake up. Sure you can call others, but how are they
supposed to reach you? This is only a problem if your job is
to be someone others must reach (i.e., a database system which
your app needs to talk to). So if you decide to run something
like a database (or webserver, etc.), it is imperative that you
have some set (static) way of identifying the address of the
server so that your program can reach it. Does that make sense?

Next is security, where in the case of PostgreSQL, you can
specify in pg_hba.conf the addresses (or phone numbers) from
which you will accept connections (or calls). It's like
CallerID for computers. :-)

Anyway, this is already too long a message. Sorry about that.
But just note that if you want to run a database server on a
network, yes, you'll also have to add some basic networking
knowledge to your toolbelt. Welcome to the information age, eh?

- Barry - wrote:
> Thanks for all the details. A previous reply to my post led me to the driver
> and where to do the Windows configuring, and I was just about to continue
> the database setup, but now it seems even more complicated. I have a single,
> home PC, and I wanted the database to run from this one computer. Possibly,
> in the future, a second home computer would write to the database. I don't
> know where to find the DNS/IP of the "server" or whether there is one, I'm
> not at all familiar with configuring PostgreSQL, and I'm generally uncertain
> about much of what I've quoted below. I've lost interest (and lots of time)
> in this.
>
> I thought I'd be able to find a simple alternative to saving data in regular
> files. I could have easily created a storage solution that meets my needs
> with Perl, even without getting fancy with a function like seek, and another
> option was Perl's DBM::Deep module, but I figured a regular database would
> make accessing the data faster and easier once I learned SQL or one of its
> flavors. It's debatable whether I should have bothered even if it were as
> simple as I thought. As it turns out, there was no installer, the
> instructions didn't work, the fixes I found elsewhere only got me to the
> command prompt interface, and it now sounds like that wouldn't have worked
> even if I didn't need access from Perl.
>
> All this it too much. There's too big a learning curve from my old methods
> of flat file storage to this, and the only help I've found at this stage is
> through helpful people like you who are willing to take the time to explain
> some of the things involved, rather than through the instructions that I've
> been following or links from them to information I need to complete the
> installation.
>
> Thanks for your help everyone, but no more databases for me.
>
> Barry
>
>
>>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)
>
>
>>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
>>
>> /var/postgresql/data
>>
>> 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).
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

> One step: skip ODBC and use a Perl DB module which talks directly to
> PostgreSQL. That's one possibility.
>
> More than likely, however, you might want to look into something like
> SQLite instead, at least for starters. At least 2 wrappers exist for
Perl:
>
> http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers

Before I began my struggle with Perl's DBD::ODBC module, I thought of using
Perl to pipe queries to psql and read responses through stdin, but others
persuaded me not to. I don't know what module could help me do use
PostgreSQL directly, but in a more reasonable way. I rejected DBD::Pg and
DBD::PgPP from the start, and now DBD::ODBC. I don't feel like reading the
DBI module (I already had a glance when struggling with DBD::ODBC) to see if
it alone could somehow help me, and I'm equally clueless about SQLite,
except I remember reading that it's not a good choice for networking, which
I might be doing if things work out.

My needs aren't well defined, but I would like to be prepared for various
possibilities. I might require a method of storage that's portable and easy
to write an installer for (or to provide installation instructions) incase I
distribute the application (initially, it will be a web app). A license that
enables me to distribute the database commercially without having to pay a
fee is a plus. It would also help if the database is suitable for
simultaneous connections in an intranet.

All I know for sure is that I want to store information about various
clients' accounts, including web pages that my application created for them,
their passwords and/or IP addresses, billing information, dates and other
data regarding usage of the application, and I'd like to access all of the
above by supplying any of the above. I don't have a clue about how many
accounts there will be.

Anyway, I'm pretty sure that the instructions I'd need to set up ODBC,
PostgreSQL, Cygwin, the necessary Perl modules, Windows XP, etc. so they all
play together nicely, would take no more than a couple of pages. If it
somehow took much more than that, that would be fine. I know I'd have to
learn SQL after that--that's fine too because documentation on SQL and psql
commands is available. What bothers me is that there are no such
instructions for the initial setup. Not even one set of instructions that
refer to further instructions, that refer somewhere else, etc. With some
luck, I found two or three relevant sets of instructions and thought that
was all I needed, but then came the complications, and I overcame them just
to learn that it's not enough. The information trail then ran cold and I was
forced to seek personalized assistance.

I don't think my setup and needs are so unusual, yet it seems like the
information I need spans several technologies, and I'd need a computer
engineering degree, or to read several technical books to gather the
necessary pieces of information. I just want(ed) to install PostgreSQL on
Windows XP and access it with Perl in a reasonable way (not my piping/stdin
way, which people think is unreasonable), with or without ODBC, but not with
version .05 of some module that has a load of non-core dependencies. I don't
see why an installer can't handle that, much less why there are no suitable
instructions for it.

Well, now I know about SQLite's wrappers, and it sounds more simple. I'm not
sure what I'll be doing yet.