1 |
After you created a poll, you will also probably want to access it's data |
2 |
which is stored in PostgreSQL. If you are doing that from Windows machine, |
3 |
easiest way is to install PostgreSQL ODBC driver and than create datasource |
4 |
on your Windows box that connects directly to database. That datasource |
5 |
can be used from any ODBC-enabled application like M$ Access, M$ Excel, |
6 |
OpenOffice etc, etc... |
7 |
|
8 |
Download PostgreSQL ODBC driver from |
9 |
|
10 |
http://odbc.postgresql.org |
11 |
|
12 |
and install it. Version 7_03_0200 is used here. |
13 |
|
14 |
Bare in mind that PostgreSQL from time to time change protocol specification, |
15 |
so you will have to download driver for your particular PostgreSQL version. |
16 |
|
17 |
Create ODBC source: |
18 |
|
19 |
Select ODBC Data Sources in Control Panel and then System DSN. This will |
20 |
create source which will be available to all users and applications on this |
21 |
computer. |
22 |
|
23 |
Click Add and under "Create New Data Source" select "PostgreSQL" (don't use |
24 |
Legacy or beta which might also be installed). Click on Finish and you will |
25 |
get options for this source: |
26 |
|
27 |
- Data Source - descriptive name used by Windows side of ODBC connection |
28 |
- Description - oh, well... description! |
29 |
- Database - name of PostgreSQL database, usually, wopi_[poll name] |
30 |
- Server - hostname or IP address of PostgreSQL server |
31 |
- Port - 5432 is default |
32 |
- User Name - user which has to be created on PostgreSQL and have permissions |
33 |
to access this database from IP address of Windows machine (or via some |
34 |
other authorisation method) |
35 |
- Password - password for that user |
36 |
|
37 |
Click on Save and proceed to create user. |
38 |
|
39 |
For example if your /etc/postgresql/pg_hba.conf has line like: |
40 |
|
41 |
host all all 0.0.0.0 0.0.0.0 md5 |
42 |
|
43 |
you can use following SQL to add user with permissions to wopi_[poll name] |
44 |
databse: |
45 |
|
46 |
$ psql template1 |
47 |
template1=# create user _user_name_ with encrypted password '_password_' nocreatedb nocreateuser ; |
48 |
CREATE USER |
49 |
|
50 |
Where _user_name_ and _password_ are user name and password that you specified |
51 |
in ODBC Setup. You can test connection using: |
52 |
|
53 |
$ psql -U _user_name_ -W _database_ |
54 |
|
55 |
Now, add permissions to select data to your newly created user: |
56 |
|
57 |
_database_=# grant select on _poll_name_ to _user_name_ ; |
58 |
GRANT |
59 |
|
60 |
|
61 |
Now it's time to define ODBC source in M$ Excel (yak!). Use: Data -> Get |
62 |
External Data -> New Database Query. There, on tab "Databases" select name of |
63 |
your ODBC source. |
64 |
|
65 |
To be written... |