If you want I can give some udfs used by me to run all the queries and get the resulting table as an array.
1.
connect2db(host,user,password,dbname) This is to connect to the database.
Code: Select all
//dbo is a public variable holding the database object
FUNCTION connect2db(host,user,password,dbname)
dbo := tmysqlserver():new(AllTrim(host),AllTrim(user),AllTrim(password))
IF dbo:NetErr()
msginfo(dbo:ERROR())
RETURN nil
ENDIF
dbo:selectdb(dbname)
IF dbo:NetErr()
msginfo(dbo:ERROR())
RETURN nil
ENDIF
//msginfo("Successfully Connected to the MySQL Server")
RETURN nil
2.
sql(dbo1,qstr) While running a SQL query. In this, dbo1 is the database object for connecting to the current database, qstr is the query string. This can be used to get data from the database via 'select' query. The table is returned as an array.
Code: Select all
function sql(dbo1,qstr)
local table := nil
local currow := nil
local tablearr := {}
local rowarr := {}
local curdateformat := set(_SET_DATEFORMAT)
set date ansi
table := dbo1:query(qstr)
set(SET_DATEFORMAT,curdateformat)
if table:neterr()
msgstop(table:error())
table:destroy()
return tablearr
else
if table:lastrec() > 0
asize(tablearr,0)
for i := 1 to table:lastrec()
asize(rowarr,0)
currow := table:getrow(i)
for j := 1 to table:fcount()
aadd(rowarr,currow:fieldget(j))
next j
aadd(tablearr,aclone(rowarr))
next i
endif
table:destroy()
return tablearr
endif
return tablearr
3. miscsql(dbo,qstr) For insert, delete, update commands. Returns .t. or .f.
Code: Select all
function miscsql(dbo,qstr)
local curdateformat := set( _SET_DATEFORMAT)
set date ansi
table := dbo:query(qstr)
set( _SET_DATEFORMAT,curdateformat)
if table:NetErr()
msgstop(table:ERROR())
table:destroy()
return .f.
endif
table:destroy()
return .t.
3. While closing the db.
Note: We can not use all the types of variables as such inside a MySQL query. We have to parse the harbour value to MySQL type, by using a function called c2sql(value) (clipper to sql). This is already in the sql sample. Let this also been added to the user defined functions.
Code: Select all
function C2SQL(Value)
local cValue := ""
local cFormatoDaData := set(4)
do case
case Valtype(Value) == "N"
cValue := AllTrim(Str(Value))
case Valtype(Value) == "D"
if !Empty(Value)
// MySQL dates are like YYYY-MM-DD
if cFormatoDaData = 'mm-dd-yyyy' // USA
cValue := "'"+PadL(Month(Value), 2, "0") + '-'+ PadL(Day(Value), 2, "0") + "-" + Str(Year(Value), 4) + "'"
elseif cFormatoDaData = 'dd/mm/yyyy' // BRITISH ou FRENCH
cValue := "'"+PadL(Day(Value), 2, "0") + "/" + PadL(Month(Value), 2, "0") + "/" + Str(Year(Value), 4) + "'"
elseif cFormatoDaData = 'yyyy.mm.dd' // ANSI
cValue := "'"+Str(Year(Value), 4) + "." + PadL(Month(Value), 2, "0") + "." + PadL(Day(Value), 2, "0") + "'"
elseif cFormatoDaData = 'dd.mm.yyyy' //GERMAN
cValue := "'"+PadL(Day(Value), 2, "0") + "." + PadL(Month(Value), 2, "0") + "." + Str(Year(Value), 4) + "'"
elseif cFormatoDaData = 'dd-mm-yyyy' //ITALIAN
cValue := "'"+PadL(Day(Value), 2, "0") + "-" + PadL(Month(Value), 2, "0") + "-" + Str(Year(Value), 4) + "'"
elseif cFormatoDaData = 'yyyy/mm/dd' //JAPAN
cValue := "'"+Str(Year(Value), 4) + "/" + PadL(Month(Value), 2, "0") + "/" + PadL(Day(Value), 2, "0") + "'"
elseif cFormatoDaData = 'mm/dd/yyyy' // AMERICAN
cValue := "'"+Str(Year(Value), 4) + "/" + PadL(Month(Value), 2, "0") + "/" + PadL(Day(Value), 2, "0") + "'"
endif
else
cValue := "''"
endif
case Valtype(Value) $ "CM"
IF Empty( Value)
cValue="''"
ELSE
cValue := "'"
Value:=DATATOSQL(value)
cValue+= value+ "'"
ENDIF
case Valtype(Value) == "L"
cValue := AllTrim(Str(iif(Value == .F., 0, 1)))
otherwise
cValue := "''" // NOTE: Here we lose values we cannot convert
endcase
return cValue
So, our insert query may be like this.
Code: Select all
qsuccess := miscsql(dbo,"insert into table1 (name, address1,city) values ("+c2sql(form1.name1.value)+","+c2sql(form1.address1.value)+","+c2sql(form1.city.value)+")")
Select query may be like this.
Code: Select all
tablearray := sql(dbo,"select * from table1 where city = "+c2sql(form1.city.value))
Thanks a lot!