locked
No Connection Sting handle RRS feed

  • Question

  • I cannot figure out why I cannot get a valid handle for a connection I am able to establish in Command Window as well as in a method. The question mark statement consistently returns -1 which means: no handle (error).

    OPEN DATABASE ("D:\vfp_Projects\Data\d_data\histdata.dbc")
    CREATE CONNECTION Myconn DATASOURCE "MyFoxSQLNT" USERID "Administrator" PASSWORD ""
    IF EMPTY (DBGETPROP ("Myconn","CONNECTION","ConnectString"))
      myUserID   = 'Administrator'
      myPassword = ""
      STORE SQLSTRINGCONNECT ('dsn=MyFoxSQLNT;uid=myUserID;pwd=myPassword') TO gnConnHandle
      ?gnConnhandle
    ENDIF

    The connection is being established because otherwise I would have gotten an error at DBGETPROP statement.

    Thanks for any help. I am sure it is a simple one for experts.

    Monday, June 26, 2006 6:39 PM

Answers

  • SQLSTRINGCONNECT uses a connection string witch may or may not be using an DSN (File,system or User ) . It does not require a dbc or any connection object, Just a connection string.

    The Connection object is generally used for creating remote View within the DBC.

    There three basic ways Fox pro connects to External Data.

    1) Remote Views (perhaps using the connection object)

    2) Sql pass Though using (SQLSTRINGCONNECT(), SQLEXEC( ) and all those other SQLxxx Commads.
    3) The Cursor Adaptor

    You a mixing the First two together.

    Go to http://www.connectionstrings.com to find the proper connection string format for whatever data source you are connecting to and plug in the correct connection string to the Code I gave you and try it again. Then you can start a fight in this board by asking which of the above Three is the best what to connect to external data.

    Dave M.

    Ps : Don’t forget to SQLDISCONNECT( )

    Monday, June 26, 2006 7:33 PM

All replies

  • try this

    myUserID = 'Administrator'
    myPassword = ""
    STORE SQLSTRINGCONNECT ('dsn=MyFoxSQLNT;uid=myUserID;pwd=myPassword') TO gnConnHandle
    ?gnConnhandle
    IF gnConnhandle<0
        AERROR(merr)
        ?merr[1]
        ?merr[2]
        ?merr[3]
    endif clear

    i don't know why you are using :
    IF EMPTY (DBGETPROP ("Myconn","CONNECTION","ConnectString"))"

    Dave M.

    Monday, June 26, 2006 6:48 PM
  • Thank you, Dave.

    It said: Data Source Name not found and no default driver specified.

    I thought the data source was set up by the first statement:

    CREATE CONNECTION Myconn DATASOURCE "MyFoxSQLNT" USERID "Administrator" PASSWORD ""

    And I thought that the driver is IMPLIED? Not?

    I thought they are set up in ODBC.ini ???

    I need some more education here.

     

    Monday, June 26, 2006 7:16 PM
  • SQLSTRINGCONNECT uses a connection string witch may or may not be using an DSN (File,system or User ) . It does not require a dbc or any connection object, Just a connection string.

    The Connection object is generally used for creating remote View within the DBC.

    There three basic ways Fox pro connects to External Data.

    1) Remote Views (perhaps using the connection object)

    2) Sql pass Though using (SQLSTRINGCONNECT(), SQLEXEC( ) and all those other SQLxxx Commads.
    3) The Cursor Adaptor

    You a mixing the First two together.

    Go to http://www.connectionstrings.com to find the proper connection string format for whatever data source you are connecting to and plug in the correct connection string to the Code I gave you and try it again. Then you can start a fight in this board by asking which of the above Three is the best what to connect to external data.

    Dave M.

    Ps : Don’t forget to SQLDISCONNECT( )

    Monday, June 26, 2006 7:33 PM
  • Thank you. It is food for thought.
    Monday, June 26, 2006 7:44 PM
  •  Dave M. wrote:

    SQLSTRINGCONNECT uses a connection string witch may or may not be using an DSN (File,system or User ) . It does not require a dbc or any connection object, Just a connection string.

    The Connection object is generally used for creating remote View within the DBC.

    There three basic ways Fox pro connects to External Data.

    1) Remote Views (perhaps using the connection object)

    2) Sql pass Though using (SQLSTRINGCONNECT(), SQLEXEC( ) and all those other SQLxxx Commads.
    3) The Cursor Adaptor

    You a mixing the First two together.

    Go to http://www.connectionstrings.com to find the proper connection string format for whatever data source you are connecting to and plug in the correct connection string to the Code I gave you and try it again. Then you can start a fight in this board by asking which of the above Three is the best what to connect to external data.

    Dave M.

    Ps : Don’t forget to SQLDISCONNECT( )



    hi Dave,

    i have 2 questions about sqlstringconnect

    first question :-

    May i know what is the different in multiuser environment between SQLSTRINGCONNECT(ConnectionString) and SQLSTRINGCONNECT(ConnectionString, .t.) ? is that mean that i have to use SQLSTRINGCONNECT(ConnectionString, .t ) in multiuser environment ?

    second question :-

    before sqlstringconnect, i created a system dsn in Control Panel -> Administrative Tools -> Data Sources (ODBC). i even setup some configuration within the option, i listed in the following :-

    [Connect Option]
    Port : 3306
    Initial Statement : SET NAMES big5

    [Advanced]
    Tick -> Don't Optimize Column Width
    Tick -> Change BIGINT Columns to Int

    my question is how to define the above configuration when i use sqlstringconnect ?
    Monday, December 10, 2007 4:18 AM
  • Use OdbcConnectionStringBuilder Class. There are samples:

     

    ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.NETDEVFX.v20.en/cpref4/html/P_System_Data_Common_DbConnectionStringBuilder_ConnectionString.htm

     

    It is my (perhaps uneducated) impression that OleDb drivers are more up to date, better supported by MS and preferable in more ways than one.

    Monday, December 10, 2007 2:02 PM