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