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
Code: Select all
function sql(dbo1,qstr)
local table := nil
local currow := nil
local tablearr := {}
local rowarr := {}
local curdateformat := set(_SET_DATEFORMAT)
local i := 0
local j := 0
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
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.
Code: Select all
dbo:destroy()
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
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)+")")
Code: Select all
tablearray := sql(dbo,"select * from table1 where city = "+c2sql(form1.city.value))
Regarding concurrent issue:
Every connection to the MySQL server is handled in a separate thread. The connection is exclusive and last_insert_id() would give different auto increment numbers to different persons in the same network according to their increment id.
So, in a Point on Sale terminal, if two persons push the save invoice button simultaneously, the server would give invoice number (if it is auto incremented) first come first serve basis and till now FYI, I had not got any problem in this.
If you want to be sure, it is safe if we have Begin Transaction ... End Transaction (if our transaction has multiple queries).
No. It is the id which can be received only after the insertion. If you want to know that id for any further processing, you can get. That's all.So, can I store "select last_insert_id()" into temporary code and insert that temporary code into table?
MySQL had virtually removed the multi-user and concurrency problems in my programs. And also, indexing.
Transaction processing is nothing to do with HMG. It is the work of MySQL.
We can send each statement as a SQL query like select all in one line, separated by ;.
This is the syntax for begin... end transaction. From MySQL manual:
Code: Select all
START TRANSACTION, COMMIT, and ROLLBACK Syntax
START TRANSACTION | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET AUTOCOMMIT = {0 | 1}
Code: Select all
START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;
Code: Select all
qsuccess := miscsql(dbo,"START TRANSACTION;SELECT @A:=SUM(salary) FROM table1 WHERE type=1;UPDATE table2 SET summary=@A WHERE type=1;COMMIT;")