SQL Help
Moderator: Rathinagiri
SQL Help
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
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
- dhaine_adp
- Posts: 457
- Joined: Wed Aug 06, 2008 12:22 pm
- Location: Manila, Philippines
Re: SQL Help
Hello Mark,
If you add a semi-colon at the end of the statement like below:
Does it make any difference?
Good luck,
Danny
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;"
Good luck,
Danny
Regards,
Danny
Manila, Philippines
Danny
Manila, Philippines
Re: SQL Help
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
I would like to use my sql table in the grid,
at this time program crashes at mysql.
Thanks Franco
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
at this time program crashes at mysql.
Thanks Franco
All The Best,
Franco
Canada
Franco
Canada
Re: SQL Help
Where did you realised connection to mysql database?
Re: SQL Help
I do not know how to do this.
can you help ............ Franco
can you help ............ Franco
All The Best,
Franco
Canada
Franco
Canada
- Rathinagiri
- Posts: 5471
- Joined: Tue Jul 29, 2008 6:30 pm
- DBs Used: MariaDB, SQLite, SQLCipher and MySQL
- Location: Sivakasi, India
- Contact:
Re: SQL Help
Why can't you use HMGMySQL library to connect and query?
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
South or North HMG is worth.
...the possibilities are endless.
Re: SQL Help
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
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
All The Best,
Franco
Canada
Franco
Canada
- serge_girard
- Posts: 3173
- Joined: Sun Nov 25, 2012 2:44 pm
- DBs Used: 1 MySQL - MariaDB
2 DBF - Location: Belgium
- Contact:
Re: SQL Help
Franco,
Try something like this:
Then do :
if SQL_Connect() // connection OK
else
msgstop ..
return
endif
and make sure that all your_* vars are filled in correctly
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
if SQL_Connect() // connection OK
else
msgstop ..
return
endif
and make sure that all your_* vars are filled in correctly
There's nothing you can do that can't be done...
Re: SQL Help
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
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
All The Best,
Franco
Canada
Franco
Canada
Re: SQL Help
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?
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?
Andrés González López
Desde Guadalajara, Jalisco. México.
Desde Guadalajara, Jalisco. México.