SQL Help

Moderator: Rathinagiri

Post Reply
MGOLDFARB
Posts: 71
Joined: Thu Jun 21, 2012 7:46 am

SQL Help

Post 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
User avatar
dhaine_adp
Posts: 457
Joined: Wed Aug 06, 2008 12:22 pm
Location: Manila, Philippines

Re: SQL Help

Post 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
Regards,

Danny
Manila, Philippines
franco
Posts: 816
Joined: Sat Nov 02, 2013 5:42 am
DBs Used: DBF
Location: Canada

Re: SQL Help

Post 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 ;)
All The Best,
Franco
Canada
User avatar
mol
Posts: 3718
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Contact:

Re: SQL Help

Post by mol »

Where did you realised connection to mysql database?
franco
Posts: 816
Joined: Sat Nov 02, 2013 5:42 am
DBs Used: DBF
Location: Canada

Re: SQL Help

Post by franco »

I do not know how to do this.
can you help ............ Franco
All The Best,
Franco
Canada
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: SQL Help

Post by Rathinagiri »

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.
franco
Posts: 816
Joined: Sat Nov 02, 2013 5:42 am
DBs Used: DBF
Location: Canada

Re: SQL Help

Post 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
All The Best,
Franco
Canada
User avatar
serge_girard
Posts: 3158
Joined: Sun Nov 25, 2012 2:44 pm
DBs Used: 1 MySQL - MariaDB
2 DBF
Location: Belgium
Contact:

Re: SQL Help

Post 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
There's nothing you can do that can't be done...
franco
Posts: 816
Joined: Sat Nov 02, 2013 5:42 am
DBs Used: DBF
Location: Canada

Re: SQL Help

Post 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
All The Best,
Franco
Canada
User avatar
andyglezl
Posts: 1461
Joined: Fri Oct 26, 2012 7:58 pm
Location: Guadalajara Jalisco, MX
Contact:

Re: SQL Help

Post 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?
Andrés González López
Desde Guadalajara, Jalisco. México.
Post Reply