Wiki Home

VFP Command SQL String Connect


Namespace: VFP
Other useful web sites: http://www.connectionstrings.com/ and http://www.carlprothman.net/Default.aspx?tabid=81

See Also: VFP Command SQL Connect

SQLSTRINGCONNECT([cConnectString])
Returns Numeric
Arguments
cConnectString - Specifies the data source connection string required by some ODBC drivers. Visual FoxPro passes the connection string to the ODBC driver.

If SQLSTRINGCONNECT() is issued without cConnectString, the SQL Data Sources dialog box is displayed, allowing you to choose a data source.

Example data source connection string:
ConnectionHandle = SQLSTRINGCONNECT('dsn=MyFoxSQLNT;uid=sa;pwd=FOXPRO')
ConHnd2 = SQLSTRINGCONNECT([uid=]+ lcUserName+ [;pwd=]+ lcUserPass+[;server=]+ lcServerName+ [;driver={SQL Server};database=]+ lcDBName+ [;DSN='';Trusted_Connection=Yes])
conHnd3=SQLSTRINGCONNECT("filedsn=c:\dsn\DsnFile.dsn")

DSNLess Connections

Creating DSN-less connections to SQL Server:
nHandle = SQLStringConnect("Driver={SQL Server};Server=Mingus;Database=pubs;Uid=sa;Pwd=secret;")

If you need to attach to a non-standard SQL Server port, add the port number after the server name, separated by a comma (i.e. Server=MyServer,MyPort)
-- Richard Kaye

Creating DSN-less connections to VFP Data:

I thought this would be a cinch, after raking over the manuals and the wiki, I decided to work backwards. (Much better examples on this wiki than in the documentation!)
Here is what I did:
  • Created a regular old DSN and connected to it
  • Used SQLGetProp() to capture the connection string
  • Looked at the connection string in the debugger. Tip: copy connection string to clipboard then paste into your prg and make necessary changes:
    nHandle = SQLConnect("{your DSN here}")
    cConnection = SQLGetProp(nHandle,"ConnectString")
    _cliptext=cConnection
    
    -- Alex Feldstein
  • Found the exact driver name in the ODBC Data Source Administrators Dialogue Box on the Driver's Tab
  • Replaced DSN=TestData; with Driver=Microsoft Visual FoxPro Driver;
    nMyHandle = SQLCONNECT([TestData])
    lcConnectionString = SQLGETPROP(nMyHandle,[ConnectString])

    Connection string returned:
    lcConnectionString = [DSN=TestData;UID=;PWD=; SourceDB=C:\Data\test.dbc; SourceType=DBC;]; [Exclusive=No; BackgroundFetch=No; Collate=Machine; Null=Yes; Deleted=Yes;]

    Revised Connection string:
    lcConnectionString = [Driver=Microsoft Visual FoxPro Driver; UID=; PWD=; SourceDB=C:\Data\test.dbc; SourceType=DBC; Exclusive=No; BackgroundFetch=No; Collate=Machine; Null=Yes; Deleted=Yes;]

    nMyNewHandle = SQLSTRINGCONNECT(lcConnectionString)

    The following link - http://www.carlprothman.net/Default.aspx?tabid=81 - contains very useful ADO connection strings for various data sources. It's very easy to convert them to SQL String Connect() syntax. -- Daniel Gramunt

    I wish I had known about that page before, it is an excellent resource! cs
    Here's a caveat to using DSN-Less connections - if the driver name includes a version specific identifier (as in Driver={INFORMIX 3.81 32 BIT} below), if someone installs a different driver version, your connection string will no longer work. In our case, since we use SQLSETPROP(0,"DispLogin",3), we recieve a generic message like:

    Message: Connectivity error: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

    In the example above (Driver=Microsoft Visual FoxPro Driver), I would imagine that the driver name is consistent across versions which would prevent the problem from cropping up.

    So, depending on your driver, it may be a good idea to use DSN's since that should allow you to use different driver versions across end-user workstations. Which is where - Programmatically Creating DSNs should come in handy.

    -- William Fields

    But..wait: If you are Programmatically Creating DSNs you still need to know the exact name of the driver... same problem. If you make the user pick the driver (or programmatically pick it from a list gotten through an API) you can then substitute that driver string in either the Connect String, or the DSN create call
    -- wgcs
    Here's a good connection string for the Informix v3.81 ODBC driver that comes bundled with the Informix SDK v2.8 (substitute your values accordingly). - William Fields
    l_cConnectionString = 	"DRIVER={INFORMIX 3.81 32 BIT};" +;
    			"UID=UserID;" +;
    			"PWD=Password;" +;
    			"DATABASE=DBAlias;" +;
    			"HOST=HostIPAddress;" +;
    			"SERVER=DBName;" +;
    			"SERVICE=7101;" +;
    			"PROTOCOL=sesoctcp;" +;
    			"CLIENT_LOCALE=en_US.CP1252;" +;
    			"DB_LOCALE=en_US.CP1252"
    

    Ms Sql parameters:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbcsql/od_odbc_d_4x4k.asp
    The latest MDAC can be downloaded free from Microsoft at: http://msdn.microsoft.com/data/downloads/updates/default.aspx
    -- Alex Feldstein
    Connection String for MySQL ODBC Driver 3.51
    lcServer="localhost"
    lcDatabase="test"
    lcUser = "root"
    lcPassword = "root"
    lcStringConn="Driver={MySQL ODBC 3.51 Driver};Port=3306"+;
                 ";Server="+lcServer+;
                 ";Database="+lcDatabase+;
                 ";Uid="+lcUser+;
                 ";Pwd="+lcPassWord
    *** Don't prompt for login
    SQLSETPROP(0,"DispLogin",3)
    lnHandle=SQLSTRINGCONNECT(lcStringConn)
    IF lnHandle  > 0
       ? SQLTABLES(lnHandle,"TABLES")
       browse last nocaptions
       SQLDISCONNECT(lnHandle)
    ELSE
       =AERROR(laError)
       MESSAGEBOX("Error at Connecting"+CHR(13)+;
                  "Description:"+laError[2])
    ENDIF
    

    MySQL optional parameters. Add into command string with sum of constants below as "OPTION=262657;" or for better documentation "OPTION=" + ALLTRIM(STR(... constants...,6,0))

    ************************************
    * Constants for MyODBC
    * based on   http://dev.mysql.com/doc/refman/5.0/en/connection-parameters.html
    * by: Ted Roche, http://www.tedroche.com
    ************************************
    
    #DEFINE MYODBC_NO_COL_WIDTH_OPTIMIZE  1
    #DEFINE MYODBC_RETURN_MATCHING_ROWS   2
    #DEFINE MYODBC_TRACE                  4
    #DEFINE MYODBC_ALLOW_BIG              8
    #DEFINE MYODBC_NO_PROMPT             16
    #DEFINE MYODBC_ODBC_10               32
    #DEFINE MYODBC_IGNORE_HASH           64
    #DEFINE MYODBC_MANAGER_CURSOR       128
    #DEFINE MYODBC_NO_SETLOCALE         256
    #DEFINE MYODBC_PAD_CHAR             512
    #DEFINE MYODBC_SQL_DESCRIBE_TABLE  1024
    #DEFINE MYODBC_COMPRESSED          2048
    #DEFINE MYODBC_IGNORE_SPACE        4096
    #DEFINE MYODBC_NAMED_PIPES         8196
    #DEFINE MYODBC_BIGINT_TO_INT      16384
    #DEFINE MYODBC_NO_CATALOG         32768
    #DEFINE MYODBC_READ_OPTIONS       65536
    #DEFINE MYODBC_SAFETY            131072
    #DEFINE MYODBC_NO_TRANS          262144
    #DEFINE FLAG_MULTI_STATEMENTS  67108864 
    

    • Database container (.DBC):
    lcDbc = _samples + [Tastrade\Data\TasTrade.dbc]
    lcCon = [Driver={Microsoft Visual FoxPro Driver};SourceType=DBC;SourceDB=] + lcDbc + [;Exclusive=No;NULL=NO;Collate=Machine;BACKGROUNDFETCH=NO;DELETED=NO]
    ? SQLStringConnect( lcCon )
    

    • Free Table directory:
    lcDbfDir = _samples + [Solution\Europa\]
    lcCon = [Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=] + lcDbfDir + [;Exclusive=No;NULL=NO;Collate=Machine;BACKGROUNDFETCH=NO;DELETED=NO]
    lh = SQLStringConnect( lcCon )
    ? SQLTables(lh)
    Browse
    ? SQLExec( lh, "select * from music" )
    ? SqlResult.source
    

    Note: you can do Use (_samples + [Solution\Europa\photos] ) but
    ? SQLExec( lh, "select * from photos" )
    ? AError(lax)
    ? lax(2) && Connectivity error: [Microsoft][ODBC Visual FoxPro Driver]Not a table.
    

    This is because MS hasn't updated VFP's ODBC driver since VFP 6.
    Which means a VFP developer can't use a tool like DBDesigner that uses odbc, which makes me very sad. - cfk
    Access

    lcMDBPath = [x:\vfp\application\data\YourAccessTable.mdb]
    STORE SQLSTRINGCONNECT('Driver={Microsoft Access Driver (*.mdb)};Dbq=' + lcMDBPath + ';Exclusive=1;Uid=admin;Pwd=') TO lnConnectionHandle
    ? lnConnectionHandle
    ? SQLEXEC(lnConnectionHandle, 'SELECT * FROM YourTable')

    Kevin Cully

    Postgre SQL

    I had to make a change to the connection string to get it to work. Notice the "ANSI" portion. "UNICODE" also seems to work. VFP9; Postgre SQL ODBC Driver version 8.02.04.00.

    lcConnectString = "Driver={PostgreSQL ANSI};Server=192.168.1.10;Database=mydatabase;Uid=myuser;Pwd=mypassword"

    Kevin Cully

    Maybe this doesn't exactly belong on this page but it is related: I created an ODBC connection to VFP
    data. I was actually serving webpages through PHP and Apache. I created some tables on the web server
    and got it all going, then when I pointed it to tables on our separate file server nothing worked,
    all I could get were "file not found" errors. I Googled a few other people suffering the
    same problem but found no answer. Thanks to filemon I found that the Apache service which invoked the
    ODBC connection was running as Local System which basically has no permissions outside the machine it
    is running on. Consequently when it started ODBC, that couldn't see any of the tables on the file
    server. Editing the service to start as a user with permissions to the file server fixed the problem.
    It's the kind of thing you don't really forget afterwards but it caused a fair bit of head scratching
    for half a day or so.
    Warning: The VFP ODBC driver is not thread, as in multi-threaded, safe.
    Please Google or go the MS KB for more information. It might be ok to use under light load as long as there are no updates to the data.
    Contributors: wgcs cs Esparta Palma William Fields Daniel Gramunt Alex Feldstein Ted Roche cfk David TAnderson
    Category VFP Commands, Category Client / Server, Category DSNLess Connections
  • ( Topic last updated: 2013.12.06 05:50:43 PM )