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