Hemangi


Hi All,

I got really very good help through this forum!

i need to access sql server database through VFP. pls show me how can i do that.

how connection string works in VFP.

Regards

Hemangi





Re: How to use sql server database

Markish


Hi,
You can use either remote views or DSN or connection string from VFP to connect to SQL server.

With connectionstring you can use,

Code Block
LOCAL mconn
mconn = SQLStringConnect("Driver=SQL Server; Server=; UID=sa; PWD=pass; Database=Northwind")

For DSN you can use

Code Block
mconn = SQLStringConnect("DSN=SQL_NW;UID=sa;PWD=;Database=Northwind")
or

mconn = SQLStringConnect("DSN=SQL_NW; Database=Northwind; Trusted_Connection=Yes")

Once you have the connection, you can use "SQLexec" for accessing the database

Code Block

sqlexec(mconn, "use pubs")

sqlexec(mconn, 'select * from authors', 'curauthors')
browse

Moreover you can define remotevies and use them as if they are local to VFP.

Hope this helps.

Best regards,
Markish







Re: How to use sql server database

Vladimir Zografski

Code Block

** Save the Connection String to a variable

local ConnectionString

ConnectionString = "Driver=SQL Server; Server= YourServer; UID=sa; PWD=yourpass; Database=YourDataBase"

** Create a variable to save the Connection Handle

Local ConnectionHandle

** Connect to the Sql Server

ConnectionHandle = SQLSTRINGCONNECT(ConnectionString)

** Check the value of ConnectionHandle to ensure a proper connection

IF Connectionhandle < 0 && Value of -1 is a failed connection

aerror(laError)

=messagebox('Failed Connection ' + laError[2])

RETURN

ENDIF

Local SqlResult

SqlResult = SQLEXEC(ConnectionHandle, 'SELECT * from yourSqlTable', 'TempCursor')

** Check the value of SqlResult, > 0 is success

IF Sqlresult < 0

aerror(laError)

=messagebox("Error occurred " + laError[2])

RETURN

ENDIF

** Now disconnect from the server

SQLDISCONNECT(ConnectionHandle)

** Browse your results

SELECT TempCursor

BROWSE

This is a very basic example but you get the idea.

Also, make sure you store your connection strings externally, never hard code them.







Re: How to use sql server database

Hemangi

Hi Vladimir Zografski ,

Thanks for help, its really helpfull. can you suggest me where will i find sample VFP project so that i can learn in better way!

Regards

Hemangi






Re: How to use sql server database

Darren5559

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





Re: How to use sql server database

Darren5559

hi Craig Berntson,

with your BIG HELP, i finally found the answer on this issue. I always believe good man like u deserve a good luck, without your help, i think i need to spend more days to test my program.

so, again, wish you good luck !!!