MySQL and HMG

General Help regarding HMG, Compilation, Linking, Samples

Moderator: Rathinagiri

User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

MySQL and HMG

Post by Rathinagiri »

In answering about data corruption in another thread, this topic had emerged. All those posts made by me, are now grouped in a single thread here.

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)
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
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))
We can get the automatically incremented number by "select last_insert_id()" query for further processing.

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).
So, can I store "select last_insert_id()" into temporary code and insert that temporary code into table?
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.

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}
example

Code: Select all

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;
In HMG we can send this like this.

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;")
With this, we can simultaneously save a transaction affecting two or more tables.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
sudip
Posts: 1454
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India

Re: MySQL and HMG

Post by sudip »

Hello Rathi,

Thanks a lot for this thread. :) I really need it.

Now, one thing which I can't decide - which one to learn TMySqlServer or HbSqlDD ?

Using HbSqlDD, we can write our program with standard xbase data manupulation syntax. But with TMySqlServer we have to do it for our own. Again TMySqlServer is more flexible (IMHO).

I had very bad experience with "RemoteView" in VFP. It's like HbSqlDD. I am sure HbSqlDD is far better than RemoteView in VFP.

Is there any performance degradation with HbSqlDD?

We should think about one question which one is better 1) Record based file system or 2) Set based database system.

Again, should I wait for this?

With best regards.

Sudip
With best regards,
Sudip
User avatar
sudip
Posts: 1454
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India

Re: MySQL and HMG

Post by sudip »

Hello Rathi,

I just ran the sample MysqlDD sent by Grigory Filatov with Minigui Extended. It's really excellent. :)

With best regards.

Sudip
With best regards,
Sudip
User avatar
sudip
Posts: 1454
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India

Re: MySQL and HMG

Post by sudip »

Hello,

I am going to develop a software for retailer of FMCG (Fast Moving Consumer Goods) or small Shopping Mall.

For this purpose I prefer MySql as back end database server. I have following thoughts about database. Please tell me whether I am correct or wrong :)

1. I want to store data for different financial year in different database for efficiency. It can be done (at least I think so ;) ).
2. I want to offer facility of backup and restore database to my client. I don't know how it can be done programatically.
3. For report purpose I want to use Rathi's Grid2Print utility.

TIA.

With best regards.

Sudip
With best regards,
Sudip
User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

Re: MySQL and HMG

Post by Rathinagiri »

Thanks for the info Sudip.

IMHO, yes, we can store data for different financial year in a different database and in addition, we can have a control database to handle which database is for which year like that.

Backup and restore, hmm, as of now, I am doing it separately using the system's task scheduling. In this way, the back up would be done in a regular interval, without any human interference. This will be in .sql format which is a huge text file of create and insert commands. However, we can call sql command too (BACKUP TABLE tbl_name [, tbl_name] ... TO '/path/to/backup/directory') so that the table files can be 'restored' back at any time programmatically.

Regarding Grid2Print, I am so happy to hear that. It is useful in printing out statements like stock report, purchase summary like that and not individual invoices :)
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
sudip
Posts: 1454
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India

Re: MySQL and HMG

Post by sudip »

Hi Rathi,

Thanks a lot! I knew you would answer it :)
Rathinagiri wrote: ... in addition, we can have a control database to handle which database is for which year like that.
Very good idea :D I shall apply it.
Rathinagiri wrote:Regarding Grid2Print, I am so happy to hear that. It is useful in printing out statements like stock report, purchase summary like that and not individual invoices :)
Excellent!!! I just need it :D For individual invoices, I shall directly work on array generated from query :)

BTW, can you help me from where I can download MySql help files. I don't have this in my MySql 5.1 installation :(

There is a very "funny" story behind my new project. I don't want to say it in detail in an open forum. But in a nutshell, I rejected an order to to create such software, where my would be client wanted some "illegal" features (to hide something from government for saving tax). After rejection, I planned to create a "good" software in this subject. I hope this software will be excellent and there are some "good" clients who will need it :)

Moreover, I want to publish some portion of this software in My HMG Projects forum also :)

Thank you again.

With best regards.

Sudip
With best regards,
Sudip
User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

Re: MySQL and HMG

Post by Rathinagiri »

Here you can find out the chm manual of MySQL.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

Re: MySQL and HMG

Post by Rathinagiri »

I'm eager to see your contribution in My HMG Projects.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
apais
Posts: 440
Joined: Fri Aug 01, 2008 6:03 pm
DBs Used: DBF
Location: uruguay
Contact:

Re: MySQL and HMG

Post by apais »

How and how much do your clients pay for their Mysql installations ?
Have your ever tried Postgresql ?
Why do you prefer one over the other ?

Thanks in advance
Angel
Angel Pais
Web Apps consultant/architect/developer.
HW_apache (webserver modules) co-developer.
HbTron (Html GUI for harbour desktop hybrid apps) co-developer.
https://www.hbtron.com
User avatar
sudip
Posts: 1454
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India

Re: MySQL and HMG

Post by sudip »

rathinagiri wrote:Here you can find out the chm manual of MySQL.
Thanks a lot !!! :) This will be very much helpful!!!
Regards.
Sudip
With best regards,
Sudip
Post Reply