Retail POS

www.pcland.com.pk

Monday 21 April 2014

VFP connect to Any DataBase Server

Visual Fox pro is having great feature to connect to All Other Database Servers  for  Back-end Data.
There are many features for using Database Server with VFP but a few major reasons for using Other Databases are as follows:-

1.Due to some limitation in VFP DBC/table goes corrupted at some point when accidentally shutdown occurs of PC/OS due to failure of Electricity of some Hardware Problem.

2. In Multi user Environment, Programmer has to manage much more with many precautions when using  VFP native DBC/tables. i.e, Record Locking, File Locking, Exclusive use of tables, etc.

I seen many Question for searching method/procedure How to Connect VFp to Database server  (i.e. SQLserver,Oracle,MYSQL, Postgress,Maria DB etc.).

Here is bellow code which will help you for your own coding & connecting to Any of above mentioned server.

NOTE: Mostly Connect Strings are listed here, you can choose or UN-comment line which you required.

*********************************************
* Author :     witten by Zia Mughal
* CopyRight :  Pcland Software Inc. Pakistan
* http://www.pcland.com.pk
**********************************
public MB_OKBUTTON,MB_STOPSIGNICON, gnconnhandle
LOCAL lcserver,lcusername,lcpassword,lcdatabase , lcok

store 0 to MB_OKBUTTON
store 16 to MB_STOPSIGNICON
STORE .f. TO lcok

lcserver = 'localhost'
lcusername = 'abc'
lcpassword = 'abc12345'
lcdatabase = 'data1'

lcok=CONNECTSERVER(lcserver,lcusername,lcpassword,lcdatabase)
******gnconnhandle= SQLCONNECT("ospos")   &&& 
*** for odbc DNS direct connection use your own odbc name
IF lcok = .t.
MESSAGEBOX('Connect Successfully to Server',0+64,'http://www.Pclandpk.Blogspot.com')
=disconnectserver()
ENDIF
RETURN

**********************************
PROCEDURE CONNECTSERVER 
param mserver,muid,mpass,mdatabase
***********************************
LOCAL tmpresult
*gnConnHandle =  && define it Public from calling routine
tmpresult = SQLSetProp(0, 'DISPLOGIN', 3)

********** Sql Server Connection
***gnConnHandle= SQLStringConnect("Driver=SQL Server;Server=&mserver;
    UID=&muid;PWD=&mpass;Database=&mdatabase")  
**  On Tcp/IP:
*gnConnHandle = SQLStringConnect("Driver=SQL Server;Server=192.168.0.205;
   UID=&logname;PWD=&mypass;Database=global")

************ Dsn Connect string
*STORE SQLSTRINGCONNECT("DSN=FOXSQL;uid=&muid;pwd=&mpass;database=&mdatabase")
   TO gnConnHandle
*STORE SQLSTRINGCONNECT("DSN=LocalServer;UID=&muid;PWD=&mpass;Database=&mdatabase") 
  TO gnConnHandle

**************Oracle string
 **gnConnHandle= SQLStringConnect( "Driver={Microsoft ODBC for Oracle};Server=
 &mserverr;Uid=&muide;Pwd=&mpass;")
 **gnConnHandle= SQLStringConnect("DRIVER=SQL Server Native Client 11.0;
Trusted_Connection=No;DATABASE=&mdatabase;SERVER=&mserver;UID=&muid;PWD=&mpass") 
 ************MYSQL String
** gnConnHandle= SQLStringConnect("DRIVER=MySQL ODBC 3.51 Driver;Server=&mserver;
UID=&muid;PWD=&mpass;Database=&mdatabase;Port=3306;Option=16899;")
** gnConnHandle= SQLStringConnect("DRIVER=MySQL ODBC 5.1 Driver;Server=&mserver;
UID=&muid;PWD=&mpass;Database=&mdatabase;OPTION=11;")

***Postgress String
* gnConnHandle= SQLStringConnect("DRIVER={PostgreSQL odbc driver(unicode)};server=&mserver
;Port=5432;Database=&mdatabase; Uid=&muid;Pwd=&mpass;" )
 gnConnHandle= SQLStringConnect("DRIVER={PostgreSQL odbc driver(ANSI)};server=&mserver;
Port=5432;Database=&mdatabase; Uid=&muid;Pwd=&mpass;" )


***PSQL SERVER 2012 & SQLlocaldb string
*gnConnHandle= SQLStringConnect("Driver={SQL Server Native Client 11.0}; 
Server=&mserver\SQLEXPRESS;Database=&mDataBase;UID=&muid;
PWD=&mpass;Trusted_Connection=yes;")

***Ms Access string
*gnConnHandle=  sqlstringconnect("Driver={Microsoft Access Driver ;(*.mdb)};Dbq=&mDatabase;Uid=&muid;Pwd=&mpass")

***SQLLITE3 ***

**** Specify location in Database parameter called i.e c:\my.db
*gnConnHandle =SQLStringConnect("DRIVER=SQLite3 ODBCDriver; Database=&mDatabase
LongNames=0; Timeout=1000;NoTXN=0; SyncPragma=NORMAL;StepAPI=0;")


IF gnConnHandle < 0
LOCAL ARRAY laError[1]
AERROR(laError)
************ Display output of error
MESSAGEBOX( laError[2],MB_OKBUTTON + MB_STOPSIGNICON,"Error " ;
+ TRANSFORM(laError[5]))
   RETURN .F.
ENDIF
RETURN  .T.
endproc
***---------------------------- end of Connect server

PROCEDURE alive 
parameter handle
if handle < 0
return .f.
endif
return .t.
endproc

PROCEDURE RUNSQL
PARAMETERS pcSQL, Viewname
LOCAL lnRetVal

IF LEN(ALLTRIM(viewname)) = 0
 lnRetVal = SQLEXEC(gnConnHandle, pcSQL)
ELSE
  lnRetVal = SQLEXEC(gnConnHandle, pcSQL, viewname) 
  ****lnResult = SQLExec(gnConnHandle, lcSQL, View_Name)
ENDIF

IF lnRetVal < 0
LOCAL ARRAY laError[1]
AERROR(laError)
MESSAGEBOX( ;
laError[2], ;
MB_OKBUTTON + MB_STOPSIGNICON, ;
"Error " + TRANSFORM(laError[5]))
RETURN .F.
ELSE
RETURN .t.
ENDIF
RETURN 
endproc
********

**********************
procedure disconnectserver
**********************
if gnConnHandle >= 0
= SQLDISCONNECT(gnConnHandle)
close database
return .t.
endif
return .f.
endproc
******************************

Enjoy the life!
My Respects!

5 comments:

  1. Wow! Zia Mughal ! I like your spirit of sharing!

    Again, thanks for this piece. I certain many a foxite guy will find the knowledge you shared both appealing and good to have.

    ReplyDelete
  2. Thanks Respected Elijah lcs!
    My pleasure

    ReplyDelete
  3. How are you doing Zia
    This is Ernesto, my best Rgds to you and your family, hope you are doing well
    Thanks for being so a humble person

    ReplyDelete
  4. Thank Respected Ernesto,
    My Pleasure

    ReplyDelete
  5. Hi There

    is it possible to connect an oracle DB without ODBC ?

    This string seems to be wrong:
    **gnConnHandle= SQLStringConnect("DRIVER=SQL Server Native Client 11.0;
    Trusted_Connection=No;DATABASE=&mdatabase;SERVER=&mserver;UID=&muid;PWD=&mpass")

    "SQL Server Native Client"...

    Thanks in advance
    Roman

    ReplyDelete