How to Avoid Data Corruption

[sudip]

Hi All,

I am facing problems regarding data corruption in .dbf files, both in multi-user and single-user mode. The corruption mainly occurs in my old VFP apps (here I am not asking help for another software tool). Is it due to .dbf files or the software tool I used.

I am asking for help because, I should know it before creating my future apps on .dbf files.

If I use MySql, will the data corruption be reduced? (I saw numerous databases with SQL Server have data corruption)

If the answer is Yes, then can my app easily create tables, indexes etc., and ability to change their structure on the fly (as they currently do with .dbf)?

With best regards.

Sudip

[Rathinagiri]

Data corruption, by saying, you mean because of power shutdown, program crash or what?

In my few years experience, MySQL is doing well. We can activate auto backup of critical data that would be stored in a separate place in regular intervals.

[sudip]

Hi Rathi,

I just expected your message Thanks a lot!

Data corruption, by saying, you mean because of power shutdown, program crash or what?

I really don't know from where those evils come. But, I see some junk characters inside my clients tables !!! ;( And they ruin the database temporarily! Whole system is halted and required "my" "VALUED" presence at client's site!!!

In my few years experience, MySQL is doing well. We can activate auto backup of critical data that would be stored in a separate place in regular intervals.

Very good I know very little about MySql. I tried 3 examples given with HMG 2.6.7. I liked 2nd one (1st example is thorough, but I don't know Spanish!!!). Now, I must have a serious try on MySql.

With best regards.

Sudip

[Rathinagiri]

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.

Code: Select all

dbo:destroy()

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))

[sudip]

Hi Rathi,

I don't know, how can I show my gratitude to you?

Thank you very much. Your code will be extremely helpful to me.

So, I am starting my MySql "mission" right now!!! (you know this is the busiest time for Indian programmers - end of the fiscal year and beginning of a new fiscal year!!!)

With my best regards.

Sudip

[Rathinagiri]

With pleasure Sudip.

[sudip]

Hi,

I found an error during adding record in \Samples\Mysql\Demo_2

It shows following error message during insert.

error.jpg (21.21 KiB) Viewed 5512 times

With best regards.

Sudip

[sudip]

Hi,

I solved the problem. The "code" column of Names table is AUTO_INCREMENT. So we should not pass value of "code" during insertion.

Previously code was:

Code: Select all

If status == 1
  cQuery := "INSERT INTO NAMES  VALUES ( '"+cCode+"' , '"+ AllTrim(cName)+"' , '"+cEmail+ "' ) "
Else
  cQuery := "UPDATE NAMES SET  Name = '"+cName+"' , eMail = '"+cEMail+"'  WHERE CODE = " + AllTrim(gCode)
Endif

I modified:

Code: Select all

If status == 1
  cQuery := "INSERT INTO NAMES (Name, eMail)  VALUES ( '"+AllTrim(cName)+"' , '"+cEmail+ "' ) "
Else
  cQuery := "UPDATE NAMES SET  Name = '"+cName+"' , eMail = '"+cEMail+"'  WHERE CODE = " + AllTrim(gCode)
Endif

With best regards.

Sudip

[Rathinagiri]

Yes. And you can get the automatically incremented number by "select last_insert_id()" query for further processing.

[esgici]

Hi Rathi,

I don't know, how can I show my gratitude to you?

Thank you very much. Your code will be extremely helpful to me.

I am sharing Sudip's sentiments, me too am very thankful to all your valuable contributions, guidances and helps.

This your MySQL guidance will be a good starting way for me too.

Thanks a lot.

Regards

--

Esgici