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!

Reset Bulk/specific VFP FORM Dataenvironment to NULL

VFP developers faces problem when  he had to make structure change in his source project regarding Data-environment. Then he had to make a lot  manual efforts to change every form or  any specific form.

Bellow is example code with function fmb_DenvNull which can help to remove Data environment cursors references in one one call.

*********************************************
*  Reset Form Dataenvironment to NULL
*  form_dataen.prg
*  Author:    Zia Mughal (2013)
*  Copy right (R) Pcland Software inc. Pakistan
*********************************************

SET SAFETY off
SET CONFIRM OFF
CLOSE DATABASES 

=adir(scxlist,"*.scx")
create table scxname ( fname c(30))
index on fname tag fname
USE
IF ! USED("scxname.dbf")
USE scxname ALIAS scxname again IN 11
ENDIF

SELECT scxname
append from array scxlist
IF ! BOF()
GO top
ENDIF

SCAN
SELECT scxname
if fmb_DenvNull(fname)= .f.
MESSAGEBOX('Error Restting Dataenvironment to NULL in Form: ' +;
scxname.fname,0+16,"Pcland.com.pk")
ENDIF
ENDSCAN
USE

MESSAGEBOX('All files Successfulyy cleared Dataenvironment: ',0+64,;
"Pcland.com.pk")
RETURN


**************************
FUNCTION fmb_DenvNull
PARAMETERS lcfile
***************************
LOCAL llresult, lcstring
STORE .f. TO llresult
lcstring = "Top = 0" + CHR(13)+ "Left = 0" + CHR(13) + "Width = 0"+ CHR(13)+;

"Height = 0" + CHR(13) + "DataSource = .NULL." + CHR(13) +;
"Name = 'Dataenvironment'"

IF !FILE(lcfile)
 RETURN .f.
ENDIF

USE &lcfile ALIAS prs IN 1

SELECT  prs
IF ! BOF()
GO top
ENDIF

LOCATE FOR objname='Dataenvironment'  AND class='dataenvironment'

IF FOUND()
replace [properties] WITH lcstring
replace [reserved2] WITH '1'
replace [reserved4] WITH '1'
DELETE ALL FOR class='cursor'
pack
STORE .t. TO llresult
ENDIF

USE
RETURN llresult
ENDFUNC
***********************

Cheers!

How to Hide Password of Vfp REMOTE VIEWS in DBC

VFP Remote data Views are accessed  via stored  name connection string  in DBC with "Create Connection" command in plain text format.By default VFP stored connection string  have no protection/native way to be secured by developer for security.

Any one can with a little bit knowledge of VFP programming can easily read 'password' from VFP Command window by opening database.

Programmatic Solution

Here is simple & easy way to protect your Connection string in external file Programmatically with two custom written functions encode_dbcon function, decode_dbcon function.

Description  & Usage


encode_dbcon function saves all your created connection from DBC to external text delimited file and deletes all connections from DBC permanently. After saving to file any external encrypting/decrypting utility/DLL (i.e cipher.exe, ) can be used  with your own 'Password' to more encrypt external created file to protect from reading.


decode_dbcon function: use this function  in your application main program before opening your DBC programmatically or accessing DBC from data environment. your Exe/App will call decode_dbcon function.
which will decrypt your saved file with your  password save in exe/app, then will import all connections from decrypted text file into DBC before opening it.
 

Code bellow is for your concept only not full utility, you can modify it according to your concept.

*********************************************
*  Hide/Protect DBC Remote connections/string 
*  Author:    Zia Mughal (2012)
*  Copy right (R) Pcland Software inc. Pakistan
*********************************************
SET SAFETY off
SET CONFIRM OFF
CLOSE DATABASES 

****example to Save existing All connections to file
IF encode_dbcon('prs.dbc') = .f.
 WAIT WINDOW "No Remote Connections/Error Saving"
RETURN 
ELSE
WAIT WINDOW "Successfulyy saved All Connections"
ENDIF

*** Retrieve connections from protected file, 
IF decode_dbcon('prs.dbc') = .f.
 WAIT WINDOW "Error Retriving Connections"
RETURN 
ELSE
WAIT WINDOW "Successfulyy Restored All Connections"
ENDIF


**************************
FUNCTION encode_dbcon
PARAMETERS lcfile
***************************
LOCAL llresult
STORE .f. TO llresult
IF !FILE(lcfile)
 RETURN .f.
ENDIF

 USE &lcfile ALIAS prs IN 1
 
 LOCATE FOR objecttype='Connection'  && .AND. ObjectName='Connect1'
IF found()
STORE .t. TO llresult
***** use filter for only one connection or All connection to save
COPY TO csave.txt FOR objecttype='Connection' && .AND. ObjectName='Connect1'
** here use any file protection utility to protect csave.txt
** cepher csave.txt mypassword
   DELETE 
 PACK
ELSE
STORE .f. TO llresult
ENDIF
USE
RETURN llresult
ENDFUNC

***************************
FUNCTION decode_dbcon
PARAMETERS lcfile
***************************
LOCAL llresult
STORE .f. TO llresult
IF !FILE(lcfile)
 RETURN llresult
ENDIF

 USE &lcfile ALIAS prs IN 1
IF ! EOF()
 GO bott
ENDIF
** if file is protected with any utility then, decode it

** first before append
** e.g. cepher -d '12345' csave.txt 
APPEND FROM csave.txt
    llresult = .t.
RETURN llresult
ENDFUNC

************************ 

Thanks for bearing me here! Hope it will help you.

Cheers & Regards

Clear Printer "Saved Environment" in VFP FRX Programatically

Visual Fox pro Report Designer 

Microsoft Visual Foxpro 9 Report Designer  has a new feature  to "Save Printer Environment" at report Design time. This feature was not available in previous versions of Visual Fox pro i.e VFP6,7,8

Application Implementation: 

Many of VFP developers face problem when they launch their developed Desktop application project on client machine due to difference of Printer model.

 There might be several reasons but most common/major reason are :

• Different Printer Model availability  at client premises which is not compatible with Printer driver selected  at report design time.
• By developers  mistake Wrong Selection of Printer model at Report Design time.

Regenrate Project & Generate New EXE
 :

Ultimately they have to regenerate their application  from source code & amend the FRX, save & re-compile the project to generate new app/exe. This makes wastage of time and today's era time is the money.

Solution:
Resting value of "Save Printer Environment" in  a few reports will be easy but in bulk number will be a quite difficult and time consuming. so how can we reduce this time by programmatically, i have written bellow code which we can use to reset to default (do not save printer environment) of all reports in few seconds.

***********************************************
**  Reset_frxenv.prg
**  clear flag of 'save Printer Environment' 
**  Used in FRX files
**  Programmed : Zia Mughal
**  Copy Right (R) Pcland Software Inc.Pakistan
**

************************************** 
CLOSE DATABASES
=adir(frxlist,"*.frx")
create table frxname ( fname c(30))
index on fname tag fname
USE
IF ! USED("frxname.dbf")
USE frxname ALIAS frxname again IN 11
ENDIF

SELECT frxname
append from array frxlist
IF ! BOF()
GO top
ENDIF

SCAN
SELECT frxname
if rest_prn_env(fname)= .f.
MESSAGEBOX('Error Reseting Environment in report file: ' +fname,0+16,;

"Pcland.com.pk")
ENDIF
ENDSCAN

MESSAGEBOX('All files converted Successfully: ',0+64,"Pcland.com.pk")

RETURN

**************************************
FUNCTION rest_prn_env

************************************** 

PARAMETERS lcfrxfile
LOCAL lcstatus, lCstring, lfile

STORE .f. TO lcstatus
lcstring = 'ORIENTATION=0' +CHR(13) + 'PAPERSIZE=9';

 + CHR(13) + 'COLOR=1'
IF ! FILE(lcfrxfile)
RETURN lcstatus
ENDIF

IF ! USED(lcfrxfile)
USE &lcfrxfile ALIAS rstfrx IN 1
ENDIF

SELECT rstfrx
IF ! BOF()
GO top
ENDIF

REPLACE [expr] WITH lcstring
replace [tag] WITH ''
replace [tag2] WITH ''
STORE .t. TO lcstatus

SELECT rstfrx
USE &lcfrxfile
RETURN lcstatus
ENDFUNC 

************************************************ 

Hope this will help Programmers to save their valuable time.
cheers!

Mr Jun Tangunan had briefly explained in his blog on the topic "when to save and when not to!"
http://sandstorm36.blogspot.com.au/2014/01/printer-environment-on-report-when-to.htm

How to Run Old FPD App/Exe in Windows 7/8

Technology Race:

Due to fastest revolution in  Software/Information Technology, inventions are coming much faster day after another and will remain in future,  even we can not deny this difference and can declare it as UNIVERSAL truth.
In-fact today's moderate era of  information Technology, many of the countries of world are still Back in  implementation of technology as well as today's moderate / modal counties of world.There are several reasons for this which may vary each other.

I have seen several searches & question on different sites i.e.( Foxite forum, Google etc.) regarding old Foxpro DOS developed  Applications to Run on New PC/LAPTOP Hardware coming with installed OS Windows 7/ Windows 8.


Microsoft has already expired customer support for Foxpro  DOS many years ago, even its moderate release Visual Fox Pro 9.0 published in 2007 support will end 2015. http://en.wikipedia.org/wiki/Visual_FoxPro

Re-writing /conversion of  existing FPD application is time taking process, so as immediate/emergency running old app's as it basis is solution. For long term basis it is recommended that ultimately re-designed in latest version of VFP 9.0.
On foxite forum Ernesto Paez putted  a similar question to run FPD Dos in windows 7 for Printing (USB port) Bar codes with a .com TSR . Thread gone so long long,  as well as email communication to give him a complete understanding.  For easy understanding i decided to write  immediate solutions here at my blog.
This is my first time to blog as it will be memorial for me forever.

Standard Solution:


Microsoft have given Backward Compatibility Option to Run backward OS compatible applications.
Setting & Running a application as backward compatibility does not effect the whole UAC (user Authentication Control) of OS. we can use Windows 7/8 compatibility mode for running FPD application.
There is limitation in Printing in this method on USB Port Printers.

1. Right click on your application then click ->Properties
2. click on "Compatibility" tab.
3. "Check mark"  both options and Click "Apply"
  i) "Run this Program in Compatible Mode"
     ->select Windows Xp (service Pack 2)
 ii) "Run this program as an administrator"

Figure 1.1

Advance Solution (Recommended):
We can use third party software emulator "DOSBOX"  (current release is  0.74) which support all old DOS software including any TSR program in .com format.Standard software is developed/released under Open Source license of sourceForge.net another link DOSBox. , Andriod http://androiddosbox.appspot.com/  DOS Software's can  run successfully under this standard release,  but  limitation in Printing. 

I recommend here for another release of "DOSBOX MEGABUILD" (http://home.arcor.de/h-a-l-9000/) which support the printing of documents on both USB and Parallel port.
By modifying config file of dosbox "DosBox.Conf" printing can be redirected to multiple/single page PNG Graphic format or can be redirected to file. Spooled file can be later used for printing by other third party Dos re-director printing software like DOSPRN (www.dosprn.com), DOS2USB (www.bsspl.com) Printfil (www.printfil.com), etc.

Tips for Configuring DosBox:
 After installation of Dosbox, Right click on shortcut of Dosbox then click on "Open File Location" to go in folder of dosbox where it is installed. Find there "DOSBox.conf " file. Open it in NotePad.exe editor. Jump to end of file, see [autoexec]
# Lines in this section will be run at startup.
in this section we can  map with "mount" command  any folder as drive letter which may exist  at local hard drive or shared network location on another PC.e.g
mount f:   d:\foxpro
mount h: \\server\d
For Printing :
Go to Section  [printer], as default printer is false in config enable it by changing value to
printer=true
printoutput=printer
if you are having Parallel port then GO to Section [parallel]
for direct output to print port change default value from 'disabled' to
parallel1=printer
to re-direct  printing to file e.g. location at C drive in TEMP folder.
parallel1= file append:c:\temp\prnspl

This blog is related to Running  FPD application under windows 7/ windows 8, so for more DOSBox options refer to DOSBox website DOSBox.

Hope it will give a way to Fox Pro DOS programmer's to run there application under windows 8.

Cheers & My respects!