Postgresql and small office p2p networks & ODBC

I have made some good progress today since your help in makeing VB talk to
Postgresql through ODBC.

My application will run on a small p2p network of 5 Dell computers, one of
which is a server for Postgresql. The other workstations can access the
server through ODBC.

The owner, who is age 76, is constantly getting phone calls all day long,
sometimes 3 at once. At his desk will be a workstation running this code (once
I soup it up and test it).

The window minimizes and goes into a look, selecting the "calls" table for
calls where "processed" is zero. As soon as it finds one or more, it
maximizes the form window and beeps. It will display which Line (there are 5 lines)
the call is on, the name and the phone number. I set a default for datetime
stamp and date (I am just starting to learn about such things.)

The boss may or may not get into the habit of clicking on which calls are
taken. So I am planning on making the code go into a wait loop for two minutes,
and then minimize, and mark the calls processed = 1, and go back to polling
the file for more calls.

The obstacles I faced in doin all this were several:

First, I had to decide on a SQL engine. I was very familiar with php and
Mysql from a website I worked on, as a hobby. But somehow, I heard that
Postgresql was more powerful and a better choice. (I cant honestly remember how I
arrived at that impression).

My next obstacle was to fine out exactly where there was a Windows install
download, which was not easy, and when I found the page, to understand which
download to choose, and then to decide how to install, as a windows process
(which scared me because I thought it might permanently change my machine). So
I stayed up til 3 am playing unsuccessfully with the install, until the next
day, I received advice from this group that the windows process was the only
route to take (since I am not in a position to do initdb myself) and that it
would not harm my computer, and that the uninstall would remove everything
fine, should I feel the need to do that.

Another hurdle was for me to understand how to install it at work, and get
the other workstations to communicate with it through IP addresses. It would
not work at all until I realized that the XP fire wall was blocking it. Once
I turned that off, it worked like a charm.

Then, I needed to find a way to write a script or program which would do
what this VB program is doing. Again the group here helped direct me to a link
with a very clear example of code. I had to get used to Visual Basic, and
realize that nothing would work for me until I added a data environment to the
project, choosing ODBC, and then configure the Postgresql ODBC (which I had
to find and download and install). I knew that I could do what I want in
MSACCESS which I have at home on my Dell, but we could not afford to buy it at
work (we use Star Office for $80).

But I purchased a copy of VB 6.0 several years ago, and played with it a
little. So that was my cheapest alternative.

There were certain things about VB that really confused me. Like, once I
saved a project, I could not figure out how to open it again and redisplay it.
There are some odd buttons one must click to make that happen. The big
advantage to VB is that there are so many books (I had purchased several back when
I purchased VB.

We use Act! Contact management all the time at work to manage all the phone
call contacts and correspondence. There are things about Act! that I do not
like. And the Act company never seems to offer any upgrades. I think the real
deal is that they want you to spend lots of money to get the functionality
you really need. But since people can put Postgresql on a simple p2p network
for FREE, why, in the 1980s, the company I was at had to spend a lot of money
for a novell network, and a database manager program.

Anyway, if I can get this project to work, why then I can keep adding to it
until it is a full fledged Contact Management system.

Thanks everyone for your help! I hope that if I document some of my
learning experiences it may help other beginners, and folks who need software but
cannot afford to pay a lot of money.

I spent a lot of time with the Liberty Basic people, and the ease of use of
Liberty Basic rekindled my desire to do all these things. But I realized that
I would be foolish to spend lots of time developing applications that use
anything other than SQL (I mean like random fixed lenght files that Liberty
Basic uses.)

I was thinking of trying to write in VB a program to poll a folder, looking
for text files placed there by Liberty Basic programs. Those text files could
have SQL commands to pass through to Postgresql. Then, the VB program could
output the results of the pass through SQL command to some other ascii file,
and the Liberty Basic appication could be waiting and polling for that file,
and scoop up the info, and continue with its processing. This would be a very
simple but effective way to let Liberty Basic folks use Postgresql. I know
that Liberty Basic compiler is written in Smalltalk, and I know there are
versions of Smalltalk that have drivers for Postgresql. So, perhaps one day,
Liberty Basic will have some way to talk directly with Postgres.


Here is the table I defined in Postgresql

displayed, line, company, firstname, lastname, areacode, phone3, phone4,
extension, date, timestamp, time

CREATE SEQUENCE "id_seq" start 1 increment 1 maxvalue 2147483647 minvalue 1
cache 1 ;
CREATE TABLE "calls" (
"id" int4 NOT NULL DEFAULT NEXTVAL('id_seq'),
"displayed" numeric(1) NULL,
"phoneline" varchar(10) NULL,
"company" varchar(50) NULL,
"firstname" varchar(40) NULL,
"lastname" varchar(40) NULL,
"areacode" varchar(3) NULL,
"phone3" varchar(3) NULL,
"phone4" varchar(4) NULL,
"extension" varchar(10) NULL,
"date" date NULL,
"timestamp" timestamp NULL,
PRIMARY KEY ("id")
);
COMMENT ON TABLE "calls" IS 'Phone calls awaiting answer';

ALTER TABLE calls ALTER date SET DEFAULT now();
ALTER TABLE calls ALTER timestamp SET DEFAULT now();

=========================================
And here is the Visual Basic code

===========================start of code
Private Sub Command1_Click()
Secretary.WindowState = 1
Secretary.Hide
finished = False

While Not finished
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

cn.Open "DSN=PostgreSQL;" & _
"UID=neil;" & _
"PWD=password;" & _
"Database=bpsimple"

rs.Open "SELECT id, displayed, phoneline, company, firstname, lastname,
areacode, phone3, phone4, extension FROM calls where displayed = 0", cn,
adOpenDynamic
rowcount = 0
While Not rs.EOF
rowcount = rowcount + 1
' Debug.Print rs!fname & ": " & rs!lname
'displayed, company, firstname, lastname, areacode, phone3, phone4,
extension, date, timestamp, time
Beep
Printline = Str$(rs!id) & ", " & rs!phoneline & ", " & rs!company & ", " &
rs!firstname & ", " & rs!lastname & ", " & rs!areacode & "-" & rs!phone3 &
"-" & rs!phone4 & " ext. " & rs!extension
Text2.Text = Text2.Text & Chr$(13) & Chr$(10) & Printline
rs.MoveNext
Wend
If rs.State <> adStateClosed Then rs.Close
Set rs = Nothing
If cn.State <> adStateClosed Then cn.Close
Set cn = Nothing

If rowcount > 0 Then
finished = True
End If
For wcount = 1 To 100000000
Next wcount

' Debug.Print Str$(waitcounter)
Wend

Secretary.Show
Secretary.WindowState = 2

MsgBox "rowcount = " & Str$(rowcount)

'Text2.Text = Text2.Text & Chr$(13) & Chr$(10) & Text1.Text

End Sub
===========================end of code