SQL Questions

General Help regarding HMG, Compilation, Linking, Samples

Moderator: Rathinagiri

franco
Posts: 265
Joined: Sat Nov 02, 2013 5:42 am
DBs Used: DBF
Been thanked: 9 times

SQL Questions

Post by franco » Sun Sep 30, 2018 4:36 pm

I have tried to use SQL for many years and can not make it work. I try samples sqllite will work but it will not work with a single dbf table.
I try mysql. I copied the dll into the windows sys32 folder, but get error Application error 0xo000007b..... Close program.
I have 4 years of trying to use sql in hmg I know it works I surely need help.
When I used visual foxpro I use the command where I had a table p_inv invoices and a table pinv_it invoice items related to p_inv by inv_no to inv.
select p_inv->inv, p_inv->custid, pinv_it->inv_no, pinv_it->item_no, pinv_it->desc ;
from p_inv, pinv_it where pinv_it->inv_no = p_inv->inv order by pinv_it->item_no into table 'test'
browse
Which hmg sql would be the best to use for the above statement that searches 30,000 invoices and 100,000 items in 2 seconds.
I am not good at setting up hmg libs and dll`s added to computer.
Thanks in advance ..... Franco

martingz
Posts: 244
Joined: Wed Nov 18, 2009 11:14 pm
Location: Mexico
Has thanked: 12 times
Been thanked: 9 times

Post by martingz » Sun Sep 30, 2018 5:26 pm

Franco libmySQL.dll , must reside in the application directory
but I do not know how far you've come
you instal mysql server?
local o remote?
wich port?

I need more information to help you correctly

franco
Posts: 265
Joined: Sat Nov 02, 2013 5:42 am
DBs Used: DBF
Been thanked: 9 times

Post by franco » Sun Sep 30, 2018 5:51 pm

I use local independent tables which I open at start of procedure and close at end.
For reports I would like to select data from tables into a temp table or mem table.
I have only tried using hmg 3.44 mysql samples in win 10 and 3.2 samples in win 8.2 and get the above error.
I copied libmysql.dll into window\sys32 folder from mysql sample 1 in hmg3.44.
Im sqllite I can run the .db3 sample but when I replace the .db3 with one of my tables it will not work.
Thnaks Franco

martingz
Posts: 244
Joined: Wed Nov 18, 2009 11:14 pm
Location: Mexico
Has thanked: 12 times
Been thanked: 9 times

Post by martingz » Sun Sep 30, 2018 6:06 pm

Franco
then you do not use the mysql server, to use it first you have to install it, configure them according to your needs and then use it.
Question
What you want is to use mysql to make temporary tables and keep using dbf?
or do you want to pass everything from dbf to mysql?

martingz
Posts: 244
Joined: Wed Nov 18, 2009 11:14 pm
Location: Mexico
Has thanked: 12 times
Been thanked: 9 times

Post by martingz » Sun Sep 30, 2018 6:25 pm


franco
Posts: 265
Joined: Sat Nov 02, 2013 5:42 am
DBs Used: DBF
Been thanked: 9 times

Post by franco » Sun Sep 30, 2018 8:48 pm

I just want to do like I did in Visual Foxpro as above
I use dbf tables for working with and updating and changing.
I want to use sql select for reports. When I have big tables and want select from possibly 3 with when or for statements it was so much faster.
I just did some checking. foxpro select from for 10 years of history 4 seconds. using hmg and creating 1 temp table and relating it to other tables
it took 55 seconds to do the same thing and I have to have the tables open. With select, tables can be closed.
Maybe mysql or sqllite can not be used for this purpose ? . There must be a way I own foxpro and have the dll librarys. Is there a way to use
these library's with hmg . Is select from fast on big files in mysql or sqllite.
Franco

User avatar
dragancesu
Posts: 569
Joined: Mon Jun 24, 2013 11:53 am
DBs Used: DBF, MySQL, Oracle
Location: Subotica, Serbia
Has thanked: 14 times
Been thanked: 101 times

Post by dragancesu » Sun Sep 30, 2018 11:10 pm

I remember that foxpro was using sql but clipper was not

Sqlite and MySQL use SQL very well, but sqlite is local singleuser database, MySQL is server multiuser database

hmg has support sqlite and mysql
but dbf and sql do not work

martingz
Posts: 244
Joined: Wed Nov 18, 2009 11:14 pm
Location: Mexico
Has thanked: 12 times
Been thanked: 9 times

Post by martingz » Mon Oct 01, 2018 12:30 am

using mysql and dbf is almost impossible, to do this you would have to first insert the fields in mysql, which would take time and then make your queries
Using scopes improves the time of record selection, but you should have indexes for the field you want to search, I hope this helps you

User avatar
dragancesu
Posts: 569
Joined: Mon Jun 24, 2013 11:53 am
DBs Used: DBF, MySQL, Oracle
Location: Subotica, Serbia
Has thanked: 14 times
Been thanked: 101 times

Post by dragancesu » Mon Oct 01, 2018 1:44 pm

Franco, what do you want?

Make form for input/edit/delete invoices? or make report?

Is possible to send small demo data in dbf, 300 invoices and 1000 items?

franco
Posts: 265
Joined: Sat Nov 02, 2013 5:42 am
DBs Used: DBF
Been thanked: 9 times

Post by franco » Mon Oct 01, 2018 5:08 pm

I want to make report from 2 large tables.
but to start if I had 1 table called customer with a field name. I would like to say
select name from customer where name = 'TOM into table 'temp''

If I add 3 new fields to one of my tables I can accomplish a fast search.
I have never added fields to a table, can anybody show me how or should I create a new table and append from old and rename.
Thanks Franco

Post Reply