Skip site navigation (1) Skip section navigation (2)

Postgresql and small office p2p networks & ODBC

From: Typing80wpm(at)aol(dot)com
To: pgsql-general(at)postgresql(dot)org
Cc: pgsql-odbc(at)postgresql(dot)org
Subject: Postgresql and small office p2p networks & ODBC
Date: 2005-04-30 23:55:19
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-generalpgsql-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,
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
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;"  & _

rs.Open "SELECT id, displayed, phoneline,  company, firstname, lastname, 
areacode, phone3, phone4, extension FROM calls  where displayed = 0", cn, 
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
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
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)
Secretary.WindowState = 2

MsgBox "rowcount = " & Str$(rowcount)

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

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

pgsql-odbc by date

Next:From: Typing80wpmDate: 2005-05-01 21:27:39
Subject: I receieved an example of Rekall script
Previous:From: Raymond O'DonnellDate: 2005-04-30 19:59:53
Subject: Re: retrieving images stored by php / pgsql

pgsql-general by date

Next:From: Alvaro HerreraDate: 2005-05-01 00:48:29
Subject: Re: Fatal error
Previous:From: Stas OskinDate: 2005-04-30 23:16:09
Subject: Using pgcrypto with AES-256 bits?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group