Wiki Home

Using ADO


Namespace: Wiki
This is an example of using ADO and ODBC to read Customer data from the VFP Sample TESTDATA.DBC.

Set up odbc connection to vfp sample data
  1. Open windows control panel, ODBC Data Sources
  2. Create a User DSN: Add, Microsoft Visual FoxPro driver, Finish (even though you are not finished, just hit it.)
  3. Set Data Source Name to VfpSample
  4. Description to Vfp Sample data (or what ever you want)
  5. Path to c:\program files\microsoft visual studio\msdn\99oct\1033\samples\vfp98\data\testdata.dbc or wherever TESTDATA.DBC is.
  6. OK (now you are really finished.)
I have always been dissatisfied with this step! How do you REALLY do it? I mean, on a program that you are distributing to dozens or thousands of clients where the program has to create the DSN on installation? We need a topic Programmatically Creating DSNs ! Can you alternately use DSN-less connections like:
lh=SQLStringConnect([uid=]+lcUserName+[;pwd=]+lcUserPass+[;server=]+lcServerName+[;driver={SQL Server};database=]+lcDBName+[;DSN=''])


An alternative to creating your own DSN is to use DSN-less connections. In VFP you use the VFP Command SQL String Connect command, in ADO you use the rest of the properties of the Connection Object (correct me if I'm wrong but a File DSN simply stores the values for these properties: You could (more user friendily) include these options in your program's settings, then provide a neat configuration page instead of forcing the user to go to their control panel odbc connections stuff) Here's how (from MSDN documentation on Connection Object):
*' Initialize variables
cn=NewObject('ADODB.Connection')
LOCAL lcServerName, lcDatabaseName, lcUserName, lcPassword
*' Specify the OLE DB provider.
cn.Provider = "sqloledb"
*' Set SQLOLEDB connection properties.
cn.Properties("Data Source").Value     = lcServerName
cn.Properties("Initial Catalog").Value = lcDatabaseName
*' Decision code for login authorization type: WinNT or SQL Server.
* (I can't tell if this is a user option (probably) or a previously unmentioned ADODB object (probably not) that could tell us the current authorization type)
If optWinNTAuth.Value = .T.
  cn.Properties("Integrated Security").Value = "SSPI"
Else
  cn.Properties("User ID").Value  = lcUserName
  cn.Properties("Password").Value = lcPassword
EndIf
*' Open the database.
cn.Open
- ?wgcs


Now run this program:
loCon = NewObject("ADODB.Connection")
lcConStr = 'VfpSample'
loCon.open( lcConStr )
lcSQL = 'select * from customer where title = "Owner"'
loRS = loCon.execute( lcSQL )

? "First company record:"
for each loFld in loRS.Fields
   ? loFld.Name+":", loFld.Value
endfor
?

? "cust_id, Company, Contact"
do while !loRS.eof
   ? loRs.Fields('cust_id').value, loRs.Fields('company').value, loRs.Fields('Contact').value
   loRS.MoveNext
enddo
?

lcSQL = 'create table foo ( pkFoo i primary key,  )'
loRS = loCon.execute( lcSQL )
"Added table FOO to testdata.dbc"

This code assumes a Visual FoxPro ODBC Data source named "mydata".
objConn = Createobject("ADODB.Connection")
objRS = Createobject("ADODB.Recordset")
cSQL = "SELECT * FROM Customers"

objConn.Open("mydata","","") && No username or password required
objRS.Open(cSQL,objConn,3)

Ta da! --- Andrew MacNeill

This is similar to Intranet ADO calls which might look like this:
< %
Set objConn = Server.Createobject("ADODB.Connection")
...
% >

Here is a simple Oracle example:
1. Create a table.
2. insert some records
loCon = NewObject("ADODB.Connection")
lcConStr =  'provider=MSDAORA.1;USER ID= MyUserId;PASSWORD = MyPassWord;DATA SOURCE= MyOdbcDSN'
loCon.open( lcConStr )

* Create a table
lcSQL = 'create table Foo ( cFid1 c(10), cFid2 c(10), dFid3 d )'
lnRet = locon.execute( lcSQL )

* Insert records
loInsCmd = NewObject( "ADODB.Command" )

loInsCmd.CommandText = ;
   "insert into Foo ( cFid1, cFid2, dFid3 ) ;
   values ( ?, ?, ? )"
loInsCmd.CommandType = 1

prmFID1 = loInsCmd.CreateParameter( "cFid1", 200, 1, 10 )
prmFID2 = loInsCmd.CreateParameter( "cFid2", 200, 1, 10 )
prmFID3 = loInsCmd.CreateParameter( "dFid3", 133, 1, 8 )

loInsCmd.Parameters.Append( prmFID1 )
loInsCmd.Parameters.Append( prmFID2 )
loInsCmd.Parameters.Append( prmFID3 )

loInsCmd.ActiveConnection = loCon

prmFID1.value = "X"
prmFID3.value = "Y"
prmFID2.value = {^1999-1-1}

loInsCmd.Execute()

release locon, loInsCmd

Here is an example calling an Oracle stored procedure returning a value:

Stored procedure call in Oracle:
get_myID(tcmyID IN Char, timyID IN OUT Integer)
Local loConnection,lcConnectStringoConnection = CreateObject("adodb.connection")

With loConnection
*--- Note using OLE-DB connection instead of a DSN

lcConnectString = "Provider=MSDAORA.1;Password=mypw;" + ;
      "User ID=myuid;Data Source=data.mine.com"
   .ConnectionString = lcConnectString
   .Open
Endwith

liID = 0

tcmyID = "A4444"

loCommand = CreateObject("adodb.command")

With loCommand
   *-- Execution string from
   .CommandText = "get_my_pkg.get_myID"
   .ActiveConnection = loConnection
   .CommandType =  4 && adCmdStoredProc
Endwith

*--- Input Parameter
loTableParameter = CreateObject("adodb.parameter")
With loTableParameter
   .Name = "tcidNumber"
   .Type =  129 && adChar
   .Direction = 1 && adParamInput
   .Size = 1
   .Value = " "
Endwith

loCommand.Parameters.Append(loTableParameter)

With loTableParameter
   .Size = 5
   .Value = tcmyID
Endwith

loOutputParameter = CreateObject("adodb.parameter")

With loOutputParameter
   .Name = "tiMyID"
   .Type = 3 && adInteger
   .Direction = 2 && adParamOutput
   .Size = 4
   .Value = 0
Endwith

*-- Append the OUTPUT parameter to the Command object.
loCommand.Parameters.Append(loOutputParameter)

*--- Note Input/Output Parameter order
*--- should match your stored procedure.

loCommand.Execute

liMyID = INT(loOutputParameter.value)

-- Stephen Finegold
Contributors Carl Karsten Andrew MacNeill Stephen Finegold
Category ADO Category Code Samples Category Data
( Topic last updated: 2001.02.01 01:16:43 PM )