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

VB questions re: Postgresql, Connect...etc

From: Typing80wpm(at)aol(dot)com
To: jeff_eckermann(at)yahoo(dot)com
Cc: pgsql-general(at)postgresql(dot)org, pgsql-odbc(at)postgresql(dot)org
Subject: VB questions re: Postgresql, Connect...etc
Date: 2005-04-30 13:56:27
Message-ID: 86.273fdd3b.2fa520cb@aol.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-odbc
Thanks Jeff, and all, for suggestions:
 
_http://gborg.postgresql.org/project/psqlodbc/genpage.php?howto-visualbasic_ 
(http://gborg.postgresql.org/project/psqlodbc/genpage.php?howto-visualbasic) 
 
 
I have Visual Basic 6.0
 
The code at this page looks like exactly what I need.
 
I anticipate my problems as follows:
 
1:) A reference in the VB project to Microsoft ActiveX Data Objects I know  
that I must somehow go to tools and check off certain tools for the project so  
that the VBA code will be able to make the odbc connection.  I am assuming  
that the above "Microsoft ActiveX Data 
 
Objects" will somehow guide me to checking the correct box and adding the  
correct object(s) to the project.
 
2:) I always have difficulties with statements like this:
cn.Open  "DSN=<MyDataSourceName>;" &  _
"UID=<MyUsername>;" &  _
"PWD=<MyPassword>;" &  _
"Database=<MyDatabaseName>"


I know, for example that my user name will be neil and my password will be  
"password" , based upon an exercise I did in my book on Postgresql.    I assume 
that my database name will be "bpsimple"  which is the name of the  database 
created by the example, for user 
"neil".   I know that I can go into windows, into the ODBC  administration, 
and create a new DSN name, and I shall be offered some choices  like "system", 
"user", "file",.... but the DSN name is where I run into  confusion and 
trouble. It will SAVE the DSN name someplace under the name of my  choosing, and I 
know that MSAccess displays such names to me during the dialogue  to link to an 
external datasource.
 
3.) Dim cn as New ADODB.Connection
Dim rs as New  ADODB.Recordset

I know that, if I do not add the correct  objects/modules to my vb project, 
and get the DSN correct, that I shall have  problems with Dim cn as New 
ADODB.Connection.
 
But I shall begin to experiment with all these things, for the script  
example is exactly what I need, IF I can get it to work and talk to the  Postgresql 
server through odbc under windows.
 
Hence, I shall create a simple .exe project in VB with a window and a  
button. I shall endeavor to add the objects to that project with pertain to  
"Microsoft ActiveX Data Objects".
 
For example, here comes the maddening part for me right now. I have  launched 
VB, and started a project and placed a button on the form, but NOW, I  must 
remember from tutorials what to click on to ADD the Microsoft ActvieX Data  
Objects
 
I just went to Projects, added a Dataenvironment , am looking at the  
Datalink Properties, have chosen PostgreSQL, added user name and password, which  is 
postgres and my password.... it verifies that the connection works, but it  
does not work with the INITIAL CATALOG TO USE field, nor am i quite certain what 
 to put in such a field. WAIT....!!! 
now, upon further inspection, I PAGE DOWN and see that there is a POSTGRES  
OLE provider... so obviously I should choose that (i think)...so NOW it asks 
for  DATASOURCE and LOCATION... so, do I put "localhost" or some ip address... 
or  what..... 
SOOO... I go back to my ODBC PostgreSQL choice,... and see that connection  
works.... AND, if I take the choice that says BUILD CONNECTION STRING, then it  
returns: 
 
DRIVER={PostgreSQL};DATABASE=bpsimple;SERVER=localhost;PORT=
5432;UID=neil;PWD=password;ReadOnly=0;Protocol=6.4;FakeOidInd
ex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;Con
nSettings=;Fetch=100;Socket=8192;UnknownSizes=0;MaxVarcharSiz
e=254;MaxLongVarcharSize=8190;Debug=0;CommLog=0;Optimizer=
1;Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLon
gVarchar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;ExtraSysTa
blePrefixes=dd_;LFConversion=1;UpdatableCursors=0;DisallowPrema
ture=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseServerSide
Prepare=0
 
 
So this represents great learning progress for me, because for the first  
time, I have found a way to build that complex string, which might be needful in  
VBA script. But, now I must understand how to translate all of this into a 
DSN  name for the vba script example, DSN=<MyDataSourceName> Now, the  
DATASOURCE NAME seems to be PostgreSQL in the Data Link Properties dialogue, and  yet, 
in the ODBC windows administrator, 
FILE DSN, I have a definition which I saved as "bpsimple"
 
So, how to fill in properly:
 
  'Open the connection
cn.Open "DSN=bpsimple;" &  _
"UID=neil;" &  _
"PWD=password;"  & _
"Database=bpsimple"
 
????????????????????????????????????????
 
or should it be: 
 
  'Open the connection
cn.Open "DSN=PostgreSQL;" &  _
"UID=neil;" &  _
"PWD=password;"  & _
"Database=bpsimple"
 
??????????
 
I shall experiment over and over with Windows ODBC to define a DSN and  store 
it in different places, until I get it to work. And I shall post my  problems 
to these groups.  Thanks for the help!
 
=============== code example
 
Sub Main()
Dim cn as New ADODB.Connection
Dim rs as New  ADODB.Recordset

'Open the connection
cn.Open  "DSN=<MyDataSourceName>;" &  _
"UID=<MyUsername>;" &  _
"PWD=<MyPassword>;" &  _
"Database=<MyDatabaseName>"

'For updateable recordsets  we would typically open a Dynamic 
 
recordset.
'Forward Only recordsets are much faster but can only  scroll forward 
 
and 
'are read only. Snapshot recordsets are read only, but  scroll in both 
'directions. 
rs.Open "SELECT id, data FROM  vbtest", cn, adOpenDynamic

'Loop though the recordset and  print the results
'We will also update the accessed column, but this  time access it 
 
through 
'the Fields collection. ISO-8601 formatted dates/times  are the safest 
 
IMHO.
While Not rs.EOF
Debug.Print rs!id  & ": " & rs!data
rs.Fields("accessed") =  Format(Now, "yyyy-MM-dd hh:mm:ss")
rs.Update
rs.MoveNext
Wend

'Add a new record to the recordset
rs.AddNew
rs!id =  76
rs!data = 'More random data'
rs!accessed = Format(Now,  "yyyy-MM-dd hh:mm:ss")
rs.Update
 
  'Insert a new record into the table
cn.Execute "INSERT  INTO vbtest (id, data) VALUES (23, 'Some random 
 
data');"
 
  'Refresh the recordset to get that last record...
rs.Refresh
 
  'Get the record count
rs.MoveLast
rs.MoveFirst
MsgBox rs.RecordCount & " Records are in the  recordset!"
 
  'Cleanup
If rs.State <> adStateClosed Then  rs.Close
Set rs = Nothing
If cn.State <> adStateClosed  Then cn.Close
Set cn = Nothing
End Sub
=================end code  example
 
 

pgsql-odbc by date

Next:From: Typing80wpmDate: 2005-04-30 14:22:23
Subject: It WORKS! I am exultant. Hallelujah!
Previous:From: Typing80wpmDate: 2005-04-30 12:00:17
Subject: Adventures with P2P and Scripts in Windows

pgsql-general by date

Next:From: Ian HardingDate: 2005-04-30 14:19:39
Subject: Re: Problem while using commit..
Previous:From: Typing80wpmDate: 2005-04-30 12:00:17
Subject: Adventures with P2P and Scripts in Windows

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