Page 1 of 1
SQL Help
Posted: Sun Sep 21, 2014 4:58 pm
by MGOLDFARB
The following query seems to have the desired result when I execute it from MS SQL Server Management Studio:
insert into epictest.dbo.prtcrd (cempno,clname,cfname,ctaxstate,cdeptno,ctcno,dtrs) select idno,lname,fname,taxstate,fdept,transid,'20140920' from epictest.dbo.payupdate
However, when I run from my application (text from application below) , it does not seem to execute. It seems to do nothing.
sSQL = "insert into epictest.dbo.prtcrd (cempno,clname,cfname,ctaxstate,cdeptno,ctcno,dtrs) select idno,lname,fname,taxstate,fdept, transid, '20140920' from epictest.dbo.payupdate "
lodp := RDDINFO(1003, ssql)
Anyone with any ideas?
Thanks!
Mark
Re: SQL Help
Posted: Thu Oct 09, 2014 3:53 pm
by dhaine_adp
Hello Mark,
If you add a semi-colon at the end of the statement like below:
Code: Select all
sSQL = "insert into epictest.dbo.prtcrd (cempno,clname,cfname,ctaxstate,cdeptno,ctcno,dtrs) select idno,lname,fname,taxstate,fdept, transid, '20140920' from epictest.dbo.payupdate;"
Does it make any difference?
Good luck,
Danny
Re: SQL Help
Posted: Mon Oct 13, 2014 9:07 pm
by franco
Can anyone tell me why this does not work.
I am trying to this type of sql in my program.
ot what do I need to do to make it work
Code: Select all
#include <hmg.ch>
#include <mysql.ch>
Function Main
tbls()
DEFINE WINDOW Form_1 ;
AT 0,0 ;
WIDTH 600 ;
HEIGHT 610 ;
Main;
on release {|| 'close all tables' } ;
TITLE 'SQL Test'
@95,10 BUTTON Button_2 CAPTION ' SEARCH' WIDTH 75 HEIGHT 75 ;
MULTILINE NOTABSTOP TOOLTIP 'Add New Item:' ACTION { || ADD()}
DEFINE GRID Grid_1
COL 0
ROW 190
WIDTH 1300
HEIGHT 200
HEADERS {"Item", "Description"}
WIDTHS {100,195 }
JUSTIFY {0,0}
ROWSOURCE "inv"
COLUMNFIELDS {"num1", "desc"}
END GRID
END WINDOW
form_1.Center
form_1.Activate
RETURN
function tbls
LOCAL CF
if ! file('inv.dbf')
CF := {}
aADD(CF,{'NUM1' ,'C' , 15,0})
aADD(CF,{'DESC' ,'C' , 25,0})
aAdd(CF, {'NUM2' , 'C' , 25,0 })
aAdd(CF, {'NUM3' , 'C' , 25,0 })
aAdd(CF, {'COST' , 'N' , 10,2 })
aAdd(CF, {'UNIT_PRICE ', 'N' , 10,2 })
if !hb_dbcreatetemp("inv", cf)
msgbox("Cannot create temporary table: Item")
RELEASE WINDOW ALL
return nil
endif
USE INV EXCLUSIVE NEW
INDEX ON NUM1 TO NUM1
do while recno()< 500
Inv->( DBAPPEND())
Inv->( FIELDPUT(1, 'P'+ALLTRIM(STR(20+RECNO())) ))
Inv->( FIELDPUT(2, 'DESC'+ALLTRIM(STR(20+RECNO())) ))
Inv->( FIELDPUT(3, 'N2'+ALLTRIM(STR(20+RECNO())) ))
Inv->( FIELDPUT(5, RECNO()))
Inv->( FIELDPUT(6, 20+RECNO()))
LOOP
enddo
endif
use
use inv new
set index to num1
return
procedure add //////////////// saved for review only
aTable := mySQL(dbo, "select * from inv")
return
I would like to use my sql table in the grid,
at this time program crashes at mysql.
Thanks Franco
Re: SQL Help
Posted: Tue Oct 14, 2014 5:20 am
by mol
Where did you realised connection to mysql database?
Re: SQL Help
Posted: Thu Oct 16, 2014 3:47 pm
by franco
I do not know how to do this.
can you help ............ Franco
Re: SQL Help
Posted: Thu Oct 16, 2014 4:45 pm
by Rathinagiri
Why can't you use HMGMySQL library to connect and query?
Re: SQL Help
Posted: Thu Oct 16, 2014 8:29 pm
by franco
not sure how.
I have 3 tables I need to select from.
like
select name from table1, custid from table2, inv_no from table3 where table1->custid = table2->custid .and.
table3->inv_no = table2->inv_no into table temp.
how can I set up to connect to these 3 tables first.
Thanks Franco
Re: SQL Help
Posted: Thu Oct 16, 2014 9:07 pm
by serge_girard
Franco,
Try something like this:
Code: Select all
FUNCTION SQL_Connect()
dbo := tmysqlserver():new(ALLTRIM(your_host),ALLTRIM(your_username),ALLTRIM(your_password))
IF dbo:NetErr()
RETURN nil
ENDIF
IF!EMPTY(your_dbname)
dbo:selectdb(your_dbname)
IF dbo:NetErr()
RETURN nil
ENDIF
ENDIF
RETURN dbo
Then do :
if SQL_Connect() // connection OK
else
msgstop ..
return
endif
and make sure that all your_* vars are filled in correctly
Re: SQL Help
Posted: Tue Nov 18, 2014 12:31 am
by franco
can you show me how to make this work.
thanks Franco
#include <hmg.ch>
#include <mysql.ch>
Function Main
private dbo := ' '
tbls()
SQL_Connect()
DEFINE WINDOW Form_1 ;
AT 0,0 ;
WIDTH 600 ;
HEIGHT 610 ;
Main;
on release closeta() ; // {|| 'close all tables', 'delete file inv.dbf' } ;
TITLE 'SQL Test'
@95,10 BUTTON Button_2 CAPTION ' SEARCH' WIDTH 75 HEIGHT 75 ;
MULTILINE NOTABSTOP TOOLTIP 'Add New Item:' ACTION { || ADD()}
DEFINE GRID Grid_1
COL 0
ROW 190
WIDTH 1300
HEIGHT 200
HEADERS {"Item", "Description"}
WIDTHS {100,195 }
JUSTIFY {0,0}
ROWSOURCE "inv"
COLUMNFIELDS {"num1", "desc"}
END GRID
END WINDOW
form_1.Center
form_1.Activate
RETURN
function closeta
close inv
delete file inv.dbf
delete file num1.ntx
return nil
function tbls
LOCAL CF
if ! file('inv.dbf')
CF := {}
aADD(CF,{'NUM1' ,'C' , 15,0})
aADD(CF,{'DESC' ,'C' , 25,0})
aAdd(CF, {'NUM2' , 'C' , 25,0 })
aAdd(CF, {'NUM3' , 'C' , 25,0 })
aAdd(CF, {'COST' , 'N' , 10,2 })
aAdd(CF, {'UNIT_PRICE ', 'N' , 10,2 })
if ! dbcreate("inv", cf)
msgbox("Cannot create temporary table: Item")
RELEASE WINDOW ALL
return nil
endif
USE INV NEW
INDEX ON NUM1 TO NUM1
do while recno()< 500
Inv->( DBAPPEND())
Inv->( FIELDPUT(1, 'P'+ALLTRIM(STR(20+RECNO())) ))
Inv->( FIELDPUT(2, 'DESC'+ALLTRIM(STR(20+RECNO())) ))
Inv->( FIELDPUT(3, 'N2'+ALLTRIM(STR(20+RECNO())) ))
Inv->( FIELDPUT(5, RECNO()))
Inv->( FIELDPUT(6, 20+RECNO()))
LOOP
enddo
endif
use
use inv new
index on num1 to num1
return
procedure add //////////////// saved for review only
*aTable := mySQL(dbo, "select * from inv")
return
FUNCTION SQL_Connect()
*dbo := ' ' // *tmysqlserver():new(ALLTRIM(your_host),ALLTRIM(your_username),ALLTRIM(your_password))
*IF dbo:NetErr()
* RETURN nil
*ENDIF
IF!EMPTY('inv.dbf')
dbo:selectdb('inv.dbf')
IF dbo:NetErr()
RETURN nil
ENDIF
ENDIF
RETURN dbo
Re: SQL Help
Posted: Tue Nov 18, 2014 1:48 am
by andyglezl
Hola Franco
Ya anteriormente habia preguntado esto, no te sirvio lo de la vez anterior ?
viewtopic.php?f=5&t=3939&p=36875#p36875
Creo que estas mezclando "naranjas" con "manzanas", estas utilizando MySql o archivos .DBF ?
---------------------------------------------------------------------------------------------------------------
Hi Franco
Previously had asked this, I not served you for the last time?
viewtopic.php?f=5&t=3939&p=36875#p36875
I think you're mixing "orange" to "apples", you are using MySql or .DBF files?