connect to SQL Server 2008

[damianodec]

hi guys
I would like an example of string connection to SQL server 2008 with ADO

thank you

[Rick Lipkin]

Damianodec

This is the code to create a global connection for Sql Server and then to use that connection object to open a recordset .. I will mention that Rao has created many Ado FW_Wrappers that simplify this code ...

Rick Lipkin

Code: Select all  Expand view  RUN

xPROVIDER := "SQLOLEDB"
xSOURCE    := "Your Server"
xCatalog      := "your database"
xUserId       := "xxxxxxx"
xPASSWORD := "xxxxxxx"


// example using my local sql server
xPROVIDER := "SQLOLEDB"
xSOURCE   := "RICKLIPKIN-PC\SQLEXPRESS"
xCatalog     := "vehicle"
xUserId       := "xxxxxx"
xPASSWORD := "xxxxxxx"

xString := 'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Initial Catalog='+xCATALOG+';User Id='+xUSERID+';Password='+xPASSWORD

// create global connection
//

// global connection string
oCn := CREATEOBJECT( "ADODB.Connection" )

TRY
   oCn:Open( xString )
CATCH oErr
   Saying := "Could not open a Global Connection to Database "+xSource
   MsgInfo( Saying )
   RETURN(.F.)
END TRY

// open a recordset with global connection

oRsAGEN := TOleAuto():New( "ADODB.Recordset" )
oRsAGEN:CursorType     := 1        // opendkeyset
oRsAGEN:CursorLocation := 3        // local cache
oRsAGEN:LockType       := 3        // lockoportunistic

cSQL := "SELECT AGENEID,AGENCY,OWNER FROM AGENCY WHERE agency ='"+xAGENCY+"'"
TRY
  oRSAGEN:Open(cSQL,oCn )
CATCH oErr
   MsgInfo( "Error in Opening AGENCY table" )
      Return(.f.)
END TRY


 

[damianodec]

thank you Rick
I'll try it!

[nageswaragunupudi]

As always we keep repeating our advice to use FWH functions to connect to any ADO Server, instead of trying your own connection string. This has the advantage that FWH creates suitable connection string based on the version of provider installed on the target computer. For example, any of these providers could be installed on your client's computer viz, SQLNCLI11, SQLNCLI10,SQLNCLI or the default SQLOLEDB. FWH function searches for the latest provider (driver) installed and establishes connection.

This is a sample of our recommended usage:

Code: Select all  Expand view  RUN

   local cServer     := "GNRHP\SQLEXPRESS"
   local cUser       := "SA"
   local cPassword   := "<yourpassword>"
   local cDatabase   := "FWH"
   local cTable      := "CUSTOMER"
   local oCn, oRs

   oCn   := FW_OpenAdoConnection( { "MSSQL", cServer, cDatabase, cUser, cPassword }, .t. )
   if oCn == nil
      ? "Connect Fail"
   else

      oRs := FW_OpenRecordSet( oCn, cTable )
      if oRs == nil
         ? "Fail to open table"
      else
         XBROWSER oRs FASTEDIT TITLE cTable
         oRs:Close()
      endif

      oCn:Close()

   endif

return nil
 

It is also possible that the server is configured to login with Windows security. In that case you can use:

Code: Select all  Expand view  RUN

   oCn   := FW_OpenAdoConnection( { "MSSQL", cServer, cDatabase }, .t. )
 

If you use FW functions, you need not again ask this question : "How to connect?" to any database, be it MySql, MsSql, Oracle, etc.

[damianodec]

hi Mr Rao
this is my code:

Code: Select all  Expand view  RUN

function Main()
   local cServer     := "MYSERVER\MSSQLSERVER08"
   local cUser       := "sa"
   local cPassword   := "pass"
   local cDatabase   := "DATABASE"
   local cTable      := "TABLE"
   local oCn, oRs

   oCn   := FW_OpenAdoConnection( { "SQLOLEDB", cServer, cDatabase, cUser, cPassword }, .t. )  //--> LINE 14
   if oCn == nil
      ? "Connect Fail"
   else

and I get this:
Time from start: 0 hours 0 mins 0 secs
Error occurred at: 07/10/17, 09:05:21
Error description: Error ADODB.Connection/0 S_OK: _CONNECTIONSTRING
Args:
[ 1] = A { ... }

Stack Calls
===========
Called from: => TOLEAUTO:_CONNECTIONSTRING( 0 )
Called from: .\source\function\OLEFUNCS.PRG => FW_OPENADOCONNECTION( 98 )
Called from: sqlmrrao.prg => MAIN( 14 )

this works:

Code: Select all  Expand view  RUN

local Connessione := "Provider=SQLOLEDB;server=MYSERVER\MSSQLSERVER08;database=DATABASE;uid=sa;pwd=pass"
local oWnd

oCn := FW_OpenAdoConnection( Connessione )
oRs := FW_OpenRecordSet( oCn, "SELECT * FROM TABLE order by FIELD1", 1 )

   DEFINE WINDOW oWnd TITLE "Auto edit browse"

   @ 0,0 XBROWSE oBrw OF oWnd AUTOCOLS CELL LINES NOBORDER RECORDSET oRs

   WITH OBJECT oBrw
      :CreateFromCode()
   END

   oWnd:oClient := oBrw
   ACTIVATE WINDOW oWnd ON INIT (obrw:gotop(), oBrw:SetFocus()) 
oRs:Close()
oCn:Close()

[nageswaragunupudi]

This is not correct

Code: Select all  Expand view  RUN

  oCn   := FW_OpenAdoConnection( { "SQLOLEDB", cServer, cDatabase, cUser, cPassword }, .t. )  //--> LINE 14
 

This is correct:

Code: Select all  Expand view  RUN

  oCn   := FW_OpenAdoConnection( { "MSSQL", cServer, cDatabase, cUser, cPassword }, .t. )  //--> LINE 14
 

[bpd2000]

As always we keep repeating our advice to use FWH functions to connect to any ADO Server, instead of trying your own connection string. This has the advantage that FWH creates suitable connection string based on the version of provider installed on the target computer. For example, any of these providers could be installed on your client's computer viz, SQLNCLI11, SQLNCLI10,SQLNCLI or the default SQLOLEDB. FWH function searches for the latest provider (driver) installed and establishes connection.

This is a sample of our recommended usage:

Code: Select all  Expand view  RUN

   local cServer     := "GNRHP\SQLEXPRESS"
   local cUser       := "SA"
   local cPassword   := "<yourpassword>"
   local cDatabase   := "FWH"
   local cTable      := "CUSTOMER"
   local oCn, oRs

   oCn   := FW_OpenAdoConnection( { "MSSQL", cServer, cDatabase, cUser, cPassword }, .t. )
   if oCn == nil
      ? "Connect Fail"
   else

      oRs := FW_OpenRecordSet( oCn, cTable )
      if oRs == nil
         ? "Fail to open table"
      else
         XBROWSER oRs FASTEDIT TITLE cTable
         oRs:Close()
      endif

      oCn:Close()

   endif

return nil
 

It is also possible that the server is configured to login with Windows security. In that case you can use:

Code: Select all  Expand view  RUN

   oCn   := FW_OpenAdoConnection( { "MSSQL", cServer, cDatabase }, .t. )
 

If you use FW functions, you need not again ask this question : "How to connect?" to any database, be it MySql, MsSql, Oracle, etc.

Dear Rao,
Excellent example of connection with Server
How to retrieve name of database and name of Tables
Is there any method to know name of instance, we have IP address of server

[nageswaragunupudi]

FW_AdoCatalogs( oCn ) --> aCatalogs
aCatalogs lists names of all catalogs. (Note: Catalog is same as database )

FW_AdoTables( oCn ) --> aTables
List of all tables in the logged in database/catalog

If you want to know more information about the Server:

Code: Select all  Expand view  RUN

XBROWSER oCn

Please refer to fwh\source\function\adofuncs.prg for more functions.

[bpd2000]

Thank you, Mr. Rao
Short and sweet, done

[bpd2000]

This is a sample of our recommended usage:

Code: Select all  Expand view  RUN

   local cServer     := "GNRHP\SQLEXPRESS"
   local cUser       := "SA"
   local cPassword   := "<yourpassword>"
   local cDatabase   := "FWH"
   local cTable      := "CUSTOMER"
   local oCn, oRs

   oCn   := FW_OpenAdoConnection( { "MSSQL", cServer, cDatabase, cUser, cPassword }, .t. )
   if oCn == nil
      ? "Connect Fail"
   else

      oRs := FW_OpenRecordSet( oCn, cTable )
      if oRs == nil
         ? "Fail to open table"
      else
         XBROWSER oRs FASTEDIT TITLE cTable
         oRs:Close()
      endif

      oCn:Close()

   endif

return nil
 

Dear Rao
1. How can I add Checkbox to select xbrowser result and obtain selected items
2. Is FW SQL server command/Class are common for MsSQL, MySQL, MARIADB, SQLITE etc.