Sayed explains how one could connect from Visual Foxpro to a SQL Server, and the problems related to making this connection.
In Microsoft public newsgroups, I’ve noticed a recent increase in the number of questions that deal with how to connect from Visual Foxpro to SQL Server, and the problems related to making this connection. So I’ve decided to write this article to cover such an important topic.
There are two functions that can be used to establish a connection with the a remote SQL Server from Visual FoxPro:
SQLConnect()
SQLStringConnect()
The SQLConnect() Function
There are two ways to use the SQLConnect() function to connect to a remote data source, such as SQL Server. The first requires that you supply the name of a data source as defined in the ODBC Data Source Administrator applet of the Control Panel.
The following example creates a connection to a remote server using the ODBCNorthwind DSN:
LOCAL hConn
hConn = SQLConnect(“ODBCNorthwind”, “sa”, “”)
The second way to use SQLConnect() is to supply the name of a Visual FoxPro connection that was created using the create connection command. The CREATE CONNECTION command stores the metadata that Visual FoxPro needs to connect to a remote data source.
The following example creates a Visual FoxPro connection named Northwind and then connects to the database described by the connection:
The other function that can be used to establish a connection to a remote data source, such as SQL Server, is SQLStringConnect(). Unlike SQLConnect(), SQLStringConnect() requires a single parameter, a string of semicolon-delimited options that describes the remote data source and optional connections settings.
The valid options are determined by the requirements of the ODBC driver. Specific requirements for each ODBC driver can be found in that ODBC driver’s documentation.
The following table lists some commonly used connection string options for SQL Server:
Option
Description
DSN
References an ODBC DSN.
Driver
Specifies the name of the ODBC driver to use.
Server
Specifies the name of the SQL Server to connect to.
UID
Specifies the login ID or username.
PWD
Specifies the password for the given login ID or username.
Database
Specifies the initial database to connect to.
APP
Specifies the name of the application making the connection.
WSID
The name of the workstation making the connection.
Trusted_Connection
Specifies whether the login is being validated by the Windows NT Domain.
Not all of the options listed in the above table have to be used for each connection.
For instance, if you specify the Trusted_Connection option and connect to SQL Server using NT Authentication, there is no reason to use the UID and PWD options since SQL Server would invariably ignore them. The following code demonstrates some examples of using SQLStringConnect().
Note: You can use the name of your server instead of the string.
Both the SQLConnect() and SQLStringConnect() functions return a connection handle. If
the connection is established successfully, the handle will be a positive integer. If Visual FoxPro failed to make the connection, the handle will contain a negative integer. A simple call to the AERROR() function can be used to retrieve the error number and message. The following example traps for a failed connection and displays the error number and message using the Visual FoxPro MESSAGEBOX() function.
Visual FoxPro returns error 1526 for all errors against a remote data source. The fifth element of the array returned by AERROR() contains the remote data source-specific error.
It is very important that a connection be released when it is no longer needed by the application because connections consume valuable resources on the server, and the number of connections may be limited by licensing constraints.
You break the connection to the remote data source using the SQLDisconnect() function. SQLDisconnect() takes one parameter, the connection handle created by a call to either SQLConnect() or SQLStringConnect(). SQLDisconnect() returns a 1 if the connection was correctly terminated and a negative value if an error occurred.
The following example establishes a connection to SQL Server, and then drops the connection:
LOCAL hConn,lnResult
*hConn = SQLStringConnect(“Driver=SQL Server;Server=<SQL2000>;”+ ;
UID=sa;PWD=;Database=Northwind”)
hConn = SQLConnect(“ODBCNorthwind”, “sa”, “”)
IF (hConn > 0)
MESSAGEBOX(“Connection has done”)
lnResult = SQLDisconnect(hConn)
IF lnResult < 0
MESSAGEBOX(“Disconnect failed”)
ENDIF && lnResult < 0
ENDIF && hConn > 0
If the parameter supplied to SQLDisconnect() is not a valid connection handle, Visual FoxPro will return a run-time error (#1466). Currently there is no way to determine whether a connection handle is valid without attempting to use it.
To disconnect all SQL pass through connections, you can pass a value of zero to SQLDisconnect().
To establish the connection with the remote server you can use one of two functions SQLConnect() OR SQLStringConnect() and there are two ways to use the SQLConnect() function to connect to a remote data source. The first requires that you supply the name of a data source as defined in the ODBC Data Source Administrator applet of the control panel and the second way to use SQLConnect() is to supply the name of a Visual FoxPro connection that was created using the create connection command.
The other function is SQLStringConnect() requires a single parameter, a string of semicolon-delimited options that describes the remote data source and optional connections settings. We have to know that both the SQLConnect() and SQLStringConnect() functions return a connection handle. To break the connection to the remote data source is by using the SQLDisconnect() function.
Visual FoxPro can run on Linux by using an open source project called Wine, which is basicallya substitute API. Wine is still in alpha stage, so Visual FoxPro support on Linux is not yetcomplete. This session offers a practical overview of the current state of affairs of running VisualFoxPro on Wine – what works, what does not, and how to set up a reliable install of VisualFoxPro or a runtime version of an application built with VFP on your Linux workstation. Thiswhitepaper is VFP8-centric, but should apply to other versions as well. Companies all over the world, and of all different sizes and makeups, have internal applicationsbuilt with Visual FoxPro. Their investments in these applications are safe for the next 5 years atleast, provided they stick with Microsoft Windows on the desktop. Since 1998, when Linux really hit the mainstream in the server arena, it has been steadily gaining new users, mostly developers, until we come to early 2003 when normal users startedmigrating to the Linux desktop as well. No one really knows how much of Microsoft’scommanding market share of the desktop will fall to Linux, but we already know of a few states,countries, and municipalities that have mandated it, and of companies large and small that havecommitted resources to switching their desktops from Windows to Linux.
Untuk mengkoneksikan data ke tabel mysql dapat dilakukan dengan source code di bawah ini :
*DO dbkoneksi
public KoneksiMysql
KoneksiMysql=SQLCONNECT(“dsnakademik”,”akademik”,”warehouseakad2007″)
IF KoneksiMysql<0
MESSAGEBOX(“Koneksi masih gagal…!!!”)
RETURN
ENDIF
MySQLSelect=”SELECT Materi.idmateri, Materi.namamateri, Materi.sks;
FROM ;
materi Materi;
ORDER BY Materi.idmateri”
StatusExec=SQLEXEC(KoneksiMysql,(MySqlselect),”vmateri”)
IF StatusExec<0
MESSAGEBOX(“Eksekusi Script Select ada kesalahan…!”,0,”Konfirmasi”)
RETURN
ENDIF
Sebagai contoh di bawah ini untuk memfilter data dengan kemungkinan pemilihan 6 buah nilai combo box yang berbeda sebagai syarat untuk pemfilteran data. Combo box tersebut dapat di isi hanya satu combo box, dua combo box, tiga combo box sampai enam combo box atau variasi dari satu dan dua combo box saja dan seterusnya. Kasus ini jika diselesaikan dengan logika kondisi sangat sulit, karena memerlukan banyak kemungkinan, sehingga source code di bawah ini sangat membantu.
Untuk dapat memfilter data berdasarkan banyak kombinasi pilihan dapat di lakukan dengan source code di bawah ini :
*program filter
LOCAL a,b,c,d,e,f,g,h,i
SELECT absensi
IF ! EMPTY(ALLTRIM(thisform.hal.hal1.combo1.Value))
a=”.and.idmateri=ALLTRIM(thisform.hal.hal1.combo1.Value)”
ELSE
a=””
ENDIF
IF ! EMPTY(ALLTRIM(thisform.hal.hal1.combo2.Value))
b=”.and.iddosen=ALLTRIM(thisform.hal.hal1.combo2.Value)”
ELSE
b=””
ENDIF
IF ! EMPTY(ALLTRIM(thisform.hal.hal1.combo3.Value))
c=”.and.idkampus=ALLTRIM(thisform.hal.hal1.combo3.Value)”
ELSE
c=””
ENDIF
IF ! EMPTY(ALLTRIM(thisform.hal.hal1.combo4.Value))
d=”.and.idkelas=ALLTRIM(thisform.hal.hal1.combo4.Value)”
ELSE
d=””
ENDIF
IF ! EMPTY(ALLTRIM(thisform.hal.hal1.combo5.Value))
e=”.and.idjurusan=ALLTRIM(thisform.hal.hal1.combo5.Value)”
ELSE
e=””
ENDIF
IF ! EMPTY(ALLTRIM(thisform.hal.hal1.combo6.Value))
f=”.and.idperiode=ALLTRIM(thisform.hal.hal1.combo6.Value)”
ELSE
f=””
ENDIF
Untuk mengkonversikan tabel Mysql ke tabel DBF ( tabel visual foxpro ) dapat dilakukan dengan source code di bawah ini :
*nama file : konversi.prg
SET SAFETY OFF
FOR i=1 TO 21
IF i=1
namatabel=”materi” &&1
ELSE
IF i=2
namatabel=”dosen” &&2
ELSE
IF i=3
namatabel=”kampus” &&3
ELSE
IF i=4
namatabel=”kelas” &&4
ELSE
IF i=5
namatabel=”jurusan” &&5
ELSE
IF i=6
namatabel=”periode” &&6
ELSE
IF i=7
namatabel=”daerah” &&7
ELSE
IF i=8
namatabel=”sekolah” &&8
ELSE
IF i=9
namatabel=”status” &&9
ELSE
IF i=10
namatabel=”bulan” &&10
ELSE
IF i=11
namatabel=”semester” &&11
ELSE
IF i=12
namatabel=”tahun” &&12
ELSE
IF i=13
namatabel=”lunas” &&13
ELSE
IF i=14
namatabel=”deposito” &&14
ELSE
IF i=15
namatabel=”kategori” &&15
ELSE
IF i=16
namatabel=”bahasa” &&16
ELSE
IF i=17
namatabel=”nilai” &&17
ELSE
IF i=18
namatabel=”jumlah” &&18
ELSE
IF i=19
namatabel=”spp” &&19
ELSE
IF i=20
namatabel=”absensi” &&20
ELSE
IF i=21
namatabel=”pinjam” &&21
ENDIF
ENDIF
ENDIF
ENDIF
ENDIF
ENDIF
ENDIF
ENDIF
ENDIF
ENDIF
ENDIF
ENDIF
ENDIF
ENDIF
ENDIF
ENDIF
ENDIF
ENDIF
ENDIF
ENDIF
ENDIF
WAIT WINDOW “Proses Konversi Tabel “+namatabel+” ( “+STR(i,2)+” ) ” nowait
sqlkedbf(namatabel)
NEXT i
MESSAGEBOX(“Proses Selesai…!!!”,0,”Konfirmasi”)
*nama file : dbkoneksi.prg
public KoneksiMysql
KoneksiMysql=SQLCONNECT(“dsnakademik”,”akademik”,”warehouseakad2007″)
IF KoneksiMysql<0
MESSAGEBOX(“Koneksi masih gagal…!!!”)
RETURN
ENDIF
*nama file : sqltodbf.prg
PARAMETERS namatabel
store “\warehouse\datawarehouse\”+namatabel to namakonversi
DO dbkoneksi
MySQLSelect=”SELECT * FROM ”
StatusExec=SQLEXEC(KoneksiMysql, MySqlselect+namatabel, “vsqldbf”)
IF StatusExec<0
MESSAGEBOX(“Eksekusi Script Select ada kesalahan…!”,0,”Konfirmasi”)
RETURN
ENDIF
SELECT vsqldbf
COPY TO &namakonversi
Untuk mengkonversikan tabel DBF ( tabel visual foxpro ) ke Tabel MySql dapat dilakukan dengan source code di bawah ini :
NamaTabelDbf=ALLTRIM(thisform.text1.Value)
USE (NamaTabelDbf) ALIAS AliasTabel SHARED
NamaTabelMysql=RIGHT(NamaTabelDbf,LEN(NamaTabelDbf)-RAT(“\”,NamaTabeldbf))
NamaTabelMysql=LEFT(NamaTabelMysql,AT(“.”,NamaTabelMySql)-1)
JumFieldSumber=AFIELDS(FieldSumber,”AliasTabel”)
DIMENSION TypeSumber(JumFieldSumber)
MySqlCreate=”CREATE TABLE “+NamaTabelMysql+”(”
**********Konversi Tipe Data DBF ke MySQL*********************
FOR i=1 TO JumFieldSumber
LebarField=ALLTRIM(STR(fsize(FIELD(i))))
FieldSumber(i)=LOWER(FIELD(i))
DO CASE
CASE TYPE(FIELD(i))=”N” && Numeric
TypeSumber(i)=”INT(“+LebarField+”)”
CASE TYPE(FIELD(i))=”C” && Character
TypeSumber(i)=”VARCHAR(“+LebarField+”)”
CASE TYPE(FIELD(i))=”D” && Date
TypeSumber(i)=”DATE”
CASE TYPE(FIELD(i))=”L” && Logical
TypeSumber(i)=”CHAR (1)”
OTHERWISE
MESSAGEBOX(“Maaf…tipe data tidak kompatibel hanya tipe N,C,D,L”)
RETURN
ENDCASE
MySqlCreate=MysqlCreate+FieldSumber(i)+” “+TypeSumber(i)+” NOT NULL,”
ENDFOR
MySqlCreate=LEFT(MySqlCreate,LEN(MySqlCreate)-1)+”)”
StatusExec=SQLEXEC(KoneksiMySql,(MySqlCreate))
IF StatusExec <0
MESSAGEBOX(“Eksekusi Script Create Table ada kesalahan”)
RETURN
ENDIF
JumRekAlias=RECCOUNT(“AliasTabel”) && Hitung Jumlah Rekord DBF Sumber
DO WHILE ! EOF(“AliasTabel”)
MySQLInsert=”INSERT INTO “+NamaTabelMysql+”(”
**************************Membuat Perintah “INSERT INTO nama_tabel(Field1..FieldN)”
FOR j=1 TO JumFieldSumber
MySQLInsert=MySQLInsert+FieldSumber(j)+”,”
ENDFOR
MySqlInsert=LEFT(MySqlInsert,LEN(MySqlInsert)-1)+”) VALUES(”
FOR j=1 TO JumFieldSumber
IsiFieldDbf=&FieldSumber(j)
DO CASE
CASE VARTYPE(IsiFieldDbf)=”N”
IsiFieldDbf=ALLTRIM(STR(IsiFieldDbf))
CASE VARTYPE(IsiFieldDbf)=”C”
IsiFieldDbf=IsiFieldDbf
CASE VARTYPE(IsiFieldDbf)=”D”
IsiFieldDbf=TRANSFORM(DTOS(IsiFieldDbf),”;@r 9999-99-99″)
CASE VARTYPE(IsiFieldDbf)=”L”
IsiFieldDBF=IIF(IsiFieldDbf=.F.,”F”,”T”)
OTHERWISE
MESSAGEBOX(“Maaf…tipe data tidak kompatibel hanya tipe N,C,D,L”)
CLOSE DATABASES
RETURN
ENDCASE
MySQLInsert=MySQLInsert+'”‘+IsiFieldDbf+'”‘+”,”
ENDFOR
MySqlInsert=LEFT(MySqlInsert,LEN(MySqlInsert)-1)+”)”
thisform.edit1.Value=mysqlinsert
StatusExec=SQLEXEC(KoneksiMysql,(MySqlInsert)) && Eksekusi Perintah MySQL
IF StatusExec <0
MESSAGEBOX(“Eksekusi Script Insert ada kesalahan”)
CLOSE DATABASES
RETURN
ENDIF
SKIP IN (“AliasTabel”)
****************Progress Bar ********************************************
thisform.xpprogressbar1.curval=ROUND(recno(“AliasTabel”)/JumRekAlias,2)*100
ThisForm.lblpersen.caption=ALLTRIM(STR(ROUND(recno;
(“AliasTabel”)/JumRekAlias,2)*100))+”%”
ENDDO
CLOSE DATABASES
"AULIA SOFT" CONSULTING (ONLINE MESIN EDC DAN ANDROID) 