quarantiane-software compiled with HMG

Discuss anything else that does not suite other forums.

Moderator: Rathinagiri

Post Reply
User avatar
serge_girard
Posts: 3167
Joined: Sun Nov 25, 2012 2:44 pm
DBs Used: 1 MySQL - MariaDB
2 DBF
Location: Belgium
Contact:
Contact serge_girard

quarantiane-software compiled with HMG

Post by serge_girard »

Here we go:

As I use MySQL tables I need to do insert-update-deletes. Therefor I need to write the necessary statements, which is always the same and thus becoming boring and possible mistakes can happen quickly. Two sort of statements: Harbour/PRG and PHP.

I wrote this little prg in order to create these statements. Of course you will need a MySQL database in order to have advantage of this program.
Fill in databasename, host, username and password + tablename and there you go!

Have some fun in these dark and uncertain times :mrgreen: ! Serge

Code: Select all

#include "hmg.ch"

#DEFINE TRUE                        .T.
#DEFINE FALSE                       .F.  
#DEFINE true                        .T.
#DEFINE false                       .F.  

#DEFINE TRUE                        .T.
#DEFINE FALSE                       .F.
#DEFINE pON                         .T.
#DEFINE pOFF                        .F.
#DEFINE pNULL                       ""
#DEFINE pMEMO                       "M"
#DEFINE pDATE                       "D"
#DEFINE pLOGICAL                    "L"
#DEFINE pNUMERIC                    "N"
#DEFINE pCHARACTER                  "C"


#TRANSLATE VALID_PARM( <X>, <Y>, <Z>)  => IF( <X> = NIL, <Z>, IF(VALTYPE(<X>) != <Y>, <Z>, <X>))




FUNCTION MAIN()
/****************/
PUBLIC dbo
PUBLIC qDBN
PUBLIC qHST
PUBLIC qUSR
PUBLIC qPAW  

PUBLIC cTABLE
PUBLIC aFLD        := {}
PUBLIC aFLD_ORDER  := {}
PUBLIC aINDEXES    := {}


SET PRINTER TO C:\TEST\DEBUG_CREATE_STATS.TXT  
SET PRINTER ON 
SET CONSOLE OFF


qDBN     := 'DATABASE_NAME'
qHST     := 'DATABASE_HOST'
qUSR     := 'DATABASE_USER'
qPAW     := 'DATABASE_PASSWORD'
cTABLE   := 'aTABLE_NAME'

Create_INSERT ( cTABLE) 
Create_UPDATE ( cTABLE) 
Create_DELETE ( cTABLE) 

Create_PHP_INSERT( cTABLE)  
Create_PHP_UPDATE( cTABLE)  
Create_PHP_DELETE( cTABLE)  

SET PRINTER TO  
SET PRINTER OFF
SET CONSOLE ON



RETURN






FUNCTION Get_Information_Schema(cTABLE)
/**************************************/
LOCAL cQuery2,  cSQL2
cQuery2      := "SELECT COLUMN_NAME, COLUMN_TYPE, COLUMN_DEFAULT, EXTRA, COLUMN_KEY " + CRLF
cQuery2      += "FROM information_schema.COLUMNS  " + CRLF
cQuery2      += "WHERE TABLE_SCHEMA = '" + qDBN + "' " + CRLF      
cQuery2      += "AND TABLE_NAME = '" + cTABLE + "' " + CRLF
cQuery2      += "ORDER BY ORDINAL_POSITION " + CRLF
cSQL2        := cQuery2
cQuery2      := dbo:Query( cQuery2 )
IF cQuery2:NetErr()                 
   ? PROCNAME() + '2 ' + cQuery2:Error(), cSQL2
   MSGINFO(cQuery2:Error(), 'NOK' )    
   RETURN
ENDIF
 

aFLD        := {}
aFLD_ORDER  := {}
? 'TABLE: ', cTABLE
? REPLICATE('*', LEN(cTABLE) + 8 )
FOR j := 1 To cQuery2:LastRec() 
   aCurRowj          := cQuery2:GetRow(j)
   cCOLUMN_NAME      := ALLTRIM(aCurRowj:fieldGet(1))
   cCOLUMN_TYPE      := ALLTRIM(aCurRowj:fieldGet(2))
   cCOLUMN_DEFAULT   := ALLTRIM(aCurRowj:fieldGet(3))
   cEXTRA            := ALLTRIM(aCurRowj:fieldGet(4))
   cCOLUMN_KEY       := ALLTRIM(aCurRowj:fieldGet(5))
   ?  cCOLUMN_NAME, cCOLUMN_TYPE, cCOLUMN_DEFAULT,cEXTRA, cCOLUMN_KEY

   AADD(aFLD, {cCOLUMN_NAME, cCOLUMN_TYPE, cCOLUMN_DEFAULT,cEXTRA, cCOLUMN_KEY } )
   AADD(aFLD_ORDER, {cCOLUMN_NAME} )
   
NEXT j


cQuery3     := "SHOW INDEX FROM "   + cTABLE + " " 
cSQL3       := cQuery3
cQuery3     := dbo:Query( cQuery3 )
IF cQuery3:NetErr()                
   ? PROCNAME() + '2 ' + cQuery3:Error(), cSQL3
   MSGINFO('SHOW INDEX_ERROR', 'NOK' )    
   RETURN
ENDIF
 
aINDEXES := {}
FOR j := 1 To cQuery3:LastRec() 
   aCurRowj       := cQuery3:GetRow(j)
   cNON_UNIQUE    := ALLTRIM(Strvalue(aCurRowj:fieldGet(2)))
   cKEY_NAME      := ALLTRIM(aCurRowj:fieldGet(3))
   cCOLUMN_NAME   := ALLTRIM(aCurRowj:fieldGet(5))
   cINDEX_TYPE    := STRVALUE(aCurRowj:fieldGet(11))
   cNON_UNIQUE    := IIF(cNON_UNIQUE == '0', 'Unique','Non unique')
    
   IF cNON_UNIQUE == 'Unique'
      AADD (aINDEXES, cCOLUMN_NAME)
   ELSE
      IF cINDEX_TYPE == 'Unique'
      //   AADD (aINDEXES, cINDEX_TYPE)
      ENDIF
   ENDIF
NEXT j
?
? 'Indexes'
? '*******'
FOR a = 1 to len(aINDEXES)
   FOR b =  1 to len(aFLD)
      IF aINDEXES [a] == aFLD [b] [1]
         aFLD [b] [5] :=  'Unique'
         EXIT
      ENDIF
   NEXT
   ? aFLD [b] [1], aFLD [b] [2],aFLD [b] [3],aFLD [b] [4], aFLD [b] [5]
NEXT
? REPLICATE('=', LEN(cTABLE) + 8 )
?
RETURN
 






FUNCTION Create_INSERT ( cKEY1)  
/******************************/
LOCAL cQuery2, cTXT, cQT1, cQT2, cQT3, a, cVAR 

 
IF EMPTY(qHST) .OR. EMPTY(qUSR) .OR. EMPTY(qPAW) .OR. EMPTY(qDBN) .OR. EMPTY(cKEY1)
   MSGINFO('DB EMPTY' ,'NOK' )	
   RETURN
ENDIF

IF SQL_Connect(qHST, qUSR, qPAW, qDBN)  == Nil 
   MSGINFO('SQL_CONNECT_ERROR' ,'NOK' )		 
   RETURN 
ENDIF

Get_Information_Schema(cKEY1)
dbo:Destroy()

cTXT := "cQuery2 := 'INSERT INTO " + cKEY1 + " (' " + CRLF
cTXT += "cQuery2 += '"
FOR a = 1 TO LEN(aFLD)-1
    cTXT += aFLD [A,1] + ', '
NEXT   
cTXT += " " +aFLD [A,1] + ")' " + CRLF
 

cQT1 := CHR(34) + CHR(32) + CHR(39) + CHR(34)         // " '"                    
cQT2 := CHR(34) + CHR(39) + ',' + CHR(32) + CHR(34)   // "', "    
cQT3 := CHR(34) + ')' + CHR(32) + CHR(34)             // "') "

 
cTXT += "cQuery2 += " + "'VALUES ('" + CRLF
FOR a = 1 TO LEN(aFLD) - 1
   cVAR := 'c' +  aFLD [A,1]
   IF UPPER(ALLTRIM( aFLD [A,2] )) == 'DATE'
      cTXT += 'cQuery2 += ' + cQT1 + ' + DATE2SQL(' + cVAR + ') + ' + cQT2 + CRLF
   ELSE
      cTXT += 'cQuery2 += ' + cQT1 + ' + STRVALUE(' + cVAR + ') + ' + cQT2 + CRLF
   ENDIF
NEXT   
cVAR := 'c' +  aFLD [A,1]

IF UPPER(ALLTRIM( aFLD [A,2] )) == 'DATE'
   cTXT += 'cQuery2 += ' + cQT1 + ' + DATE2SQL(' + cVAR + ') + ' + cQT3 + CRLF
ELSE
   cTXT += 'cQuery2 += ' + cQT1 + ' + STRVALUE(' + cVAR + ') + ' + cQT3 + CRLF
ENDIF

? cTXT
 
/* OUTPUT WILL LOOK LIKE THIS * /
cQuery2 := 'INSERT INTO aTABLE_NAME (' 
cQuery2 += 'field1, field2, field3, field4, field5, field6, field7, field8, field9, field10, field11, field12,  field13)' 
cQuery2 += 'VALUES ('
cQuery2 += " '" + STRVALUE(cfield1) + "', "
cQuery2 += " '" + STRVALUE(cfield2) + "', "
cQuery2 += " '" + STRVALUE(cfield3) + "', "
cQuery2 += " '" + STRVALUE(cfield4) + "', "
cQuery2 += " '" + STRVALUE(cfield5) + "', "
cQuery2 += " '" + STRVALUE(cfield6) + "', "
cQuery2 += " '" + STRVALUE(cfield7) + "', "
cQuery2 += " '" + STRVALUE(cfield8) + "', "
cQuery2 += " '" + STRVALUE(cfield9) + "', "
cQuery2 += " '" + STRVALUE(cfield10) + "', "
cQuery2 += " '" + STRVALUE(cfield11) + "', "
cQuery2 += " '" + STRVALUE(cfield12) + "', "
cQuery2 += " '" + STRVALUE(cfield13) + ") "
*/


// System.Clipboard := cTXT
// MSGINFO('Copied to clipboard')
RETURN

 






FUNCTION Create_UPDATE ( cKEY1)        
/******************************/
LOCAL cQuery2, cTXT, cQT1, cQT2, cQT3, a, cVAR 

 
IF EMPTY(qHST) .OR. EMPTY(qUSR) .OR. EMPTY(qPAW) .OR. EMPTY(qDBN) .OR. EMPTY(cKEY1)
   MSGINFO(qMSG_5 ,'NOK' )	
   RETURN
ENDIF

IF SQL_Connect(qHST, qUSR, qPAW, qDBN)  == Nil 
   MSGINFO('SQL_CONNECT_ERROR' ,'NOK' )		 
   RETURN 
ENDIF

Get_Information_Schema(cKEY1)
dbo:Destroy()

aWHERE := {}
aSET   := {}
FOR a = 1 to len(aINDEXES)
   FOR b =  1 to len(aFLD)
      IF aINDEXES [a] == aFLD [b] [1]
         aFLD [b] [5] :=  'Unique'
         EXIT
      ENDIF
   NEXT
   AADD(aWHERE, {aFLD [b] [1], aFLD [b] [2]})
NEXT

FOR b =  1 to len(aFLD)
   GEV := FALSE
   FOR a = 1 to len(aWHERE)
      IF aWHERE [a] [1] == aFLD [b] [1]
         GEV := TRUE
         EXIT
      ENDIF
   NEXT
   IF !GEV  
      AADD(aSET, {aFLD [b] [1], aFLD [b] [2]})
   ENDIF
NEXT
 
cQT1 := CHR(34) + CHR(32) + CHR(39) + CHR(34) // " '"                    
cQT2 := CHR(34) + CHR(39) + ',' + CHR(32) + CHR(34) // "', "    
cQT3 := CHR(34) + ')' + CHR(32) + CHR(34)  // "') "
cQT4 := CHR(39) + CHR(34) + CHR(32) + CHR(39) // '" '                    

 
cTXT := "cQuery2 := 'UPDATE " + cKEY1 + " ' " + CRLF
cVAR := 'c' +  aSET [1,1]

IF UPPER(ALLTRIM( aFLD [1,2] )) == 'DATE'
   cTXT += "cQuery2 += 'SET " + aSET [1,1] + ' = "' +  "' +" + ' DATE2SQL(' + cVAR + ") + " + cQT4 + CRLF  // SET NON-KEYS 
ELSE
   cTXT += "cQuery2 += 'SET " + aSET [1,1] + ' = "' +  "' +" + ' STRVALUE(' + cVAR + ") + " + cQT4 + CRLF  // SET NON-KEYS 
ENDIF


FOR a := 2 to len(aSET)  
   cVAR := 'c' + aSET [a,1]
   IF UPPER(ALLTRIM( aSET [A,2] )) == 'DATE'
      cTXT += "cQuery2 += ' , " + aSET [a,1] + ' = "' +  "' +" + ' DATE2SQL(' + cVAR + ") + " + cQT4 + CRLF  // SET NON-KEYS 
   ELSE
      cTXT += "cQuery2 += ' , " + aSET [a,1] + ' = "' +  "' +" + ' STRVALUE(' + cVAR + ") + " + cQT4 + CRLF  // SET NON-KEYS 
   ENDIF
NEXT

cVAR := 'c' +  aWHERE [1,1]
IF UPPER(ALLTRIM( aWHERE [1,2] )) == 'DATE'
   cTXT += "cQuery2 += 'WHERE " + aWHERE [1,1] + ' = "' +  "' +" + ' DATE2SQL(' + cVAR + ") + " + cQT4 + CRLF // SET NON-KEYS 
ELSE
   cTXT += "cQuery2 += 'WHERE " + aWHERE [1,1] + ' = "' +  "' +" + ' STRVALUE(' + cVAR + ") + " + cQT4 + CRLF // SET NON-KEYS 
ENDIF

for a := 2 to len(aWHERE)
   cVAR := 'c' +  aWHERE [a,1]
   IF UPPER(ALLTRIM( aFLD [A,2] )) == 'DATE'
      cTXT += "cQuery2 += ' AND  " + aWHERE [a,1] + ' = "' +  "' +" + ' DATE2SQL(' + cVAR + ") + " + cQT4 + CRLF  // SET NON-KEYS 
   ELSE
      cTXT += "cQuery2 += ' AND  " + aWHERE [a,1] + ' = "' +  "' +" + ' STRVALUE(' + cVAR + ") + " + cQT4 + CRLF  // SET NON-KEYS 
   ENDIF
next
? cTXT


 
/* SOMETHING LIKE THIS * /
cQuery2 := 'UPDATE aTABLE_NAME ' 
cQuery2 += 'SET field6 = "' + STRVALUE(cfield6) + '" '
cQuery2 += ' , field6 = "' + STRVALUE(cfield7) + '" '
cQuery2 += ' , field6 = "' + STRVALUE(cfield8) + '" '
cQuery2 += ' , field6 = "' + STRVALUE(cfield9) + '" '
cQuery2 += ' , field6 = "' + STRVALUE(cfield10) + '" '
cQuery2 += ' , field6 = "' + STRVALUE(cfield11) + '" '
cQuery2 += ' , field6 = "' + STRVALUE(cfield12) + '" '
cQuery2 += ' , field6 = "' + STRVALUE(cfield13) + '" '
cQuery2 += 'WHERE field1 = "' + STRVALUE(cfield1) + '" '
cQuery2 += ' AND  field2 = "' + STRVALUE(cfield2) + '" '
cQuery2 += ' AND  field3 = "' + STRVALUE(cfield3) + '" '
cQuery2 += ' AND  field4 = "' + STRVALUE(cfield4) + '" '
cQuery2 += ' AND  field5 = "' + STRVALUE(cfield5) + '" '
*/

//System.Clipboard := cTXT  
//MSGINFO('Copied to clipboard')
RETURN







FUNCTION Create_DELETE ( cKEY1)        
/******************************/
LOCAL cQuery2, cTXT, cQT1, cQT2, cQT3, a, cVAR 

 
IF EMPTY(qHST) .OR. EMPTY(qUSR) .OR. EMPTY(qPAW) .OR. EMPTY(qDBN) .OR. EMPTY(cKEY1)
   MSGINFO(qMSG_5 ,'NOK' )	
   RETURN
ENDIF

IF SQL_Connect(qHST, qUSR, qPAW, qDBN)  == Nil 
   MSGINFO('SQL_CONNECT_ERROR' ,'NOK' )		 
   RETURN 
ENDIF

Get_Information_Schema(cKEY1)
dbo:Destroy()


 
aWHERE := {}
aSET   := {}
FOR a = 1 to len(aINDEXES)
   FOR b =  1 to len(aFLD)
      IF aINDEXES [a] == aFLD [b] [1]
         aFLD [b] [5] :=  'Unique'
         EXIT
      ENDIF
   NEXT
   AADD(aWHERE, {aFLD [b] [1], aFLD [b] [2]})
NEXT

FOR b =  1 to len(aFLD)
   GEV := FALSE
   FOR a = 1 to len(aWHERE)
      IF aWHERE [a] [1] == aFLD [b] [1]
         GEV := TRUE
         EXIT
      ENDIF
   NEXT
   IF !GEV  
      AADD(aSET, {aFLD [b] [1], aFLD [b] [2]})
   ENDIF
NEXT
 
cQT1 := CHR(34) + CHR(32) + CHR(39) + CHR(34) // " '"                    
cQT2 := CHR(34) + CHR(39) + ',' + CHR(32) + CHR(34) // "', "    
cQT3 := CHR(34) + ')' + CHR(32) + CHR(34)  // "') "
cQT4 := CHR(39) + CHR(34) + CHR(32) + CHR(39) // '" '                    

 
cTXT := "cQuery2 := 'DELETE FROM " + cKEY1 + " ' " + CRLF
cVAR := 'c' +  aWHERE [1,1]
cTXT += "cQuery2 += 'WHERE " + aWHERE [1,1] + ' = "' +  "' +" + ' STRVALUE(' + cVAR + ") + " + cQT4 + CRLF // SET NON-KEYS 
for a := 2 to len(aWHERE)
   cVAR := 'c' +  aWHERE [a,1]
   IF UPPER(ALLTRIM( aWHERE [A,2] )) == 'DATE'
      cTXT += "cQuery2 += ' AND  " + aWHERE [a,1] + ' = "' +  "' +" + ' DATE2SQL(' + cVAR + ") + " + cQT4 + CRLF  // SET NON-KEYS 
   ELSE
      cTXT += "cQuery2 += ' AND  " + aWHERE [a,1] + ' = "' +  "' +" + ' STRVALUE(' + cVAR + ") + " + cQT4 + CRLF  // SET NON-KEYS 
   ENDIF
next
? cTXT

 
/* example * /
cQuery2 := 'DELETE FROM aTABLE_NAME ' 
cQuery2 += 'WHERE field1 = "' + STRVALUE(cfield1) + '" '
cQuery2 += ' AND  field2 = "' + STRVALUE(cfield2) + '" '
cQuery2 += ' AND  field3 = "' + STRVALUE(cfield3) + '" '
cQuery2 += ' AND  field4 = "' + STRVALUE(cfield4) + '" '
cQuery2 += ' AND  field5 = "' + STRVALUE(cfield5) + '" '
*/


// System.Clipboard := cTXT  
// MSGINFO('Copied to clipboard')
RETURN








FUNCTION Create_PHP_INSERT( cKEY1)        
/******************************/
LOCAL cQuery2, cTXT, cQT1, cQT2, cQT3, a, cVAR 

 
IF EMPTY(qHST) .OR. EMPTY(qUSR) .OR. EMPTY(qPAW) .OR. EMPTY(qDBN) .OR. EMPTY(cKEY1)
   MSGINFO(qMSG_5 ,'NOK' )	
   RETURN
ENDIF

IF SQL_Connect(qHST, qUSR, qPAW, qDBN)  == Nil 
   MSGINFO('SQL_CONNECT_ERROR' ,'NOK' )		 
   RETURN 
ENDIF

Get_Information_Schema(cKEY1)
dbo:Destroy()

cTXT := "$cQuery2  = 'INSERT INTO " + cKEY1 + " ('; " + CRLF
cTXT += "$cQuery2 .= '"
FOR a = 1 TO LEN(aFLD)-1
    cTXT += aFLD [A,1] + ', '
NEXT   
cTXT += " " +aFLD [A,1] + ")'; " + CRLF
 

cQT1 := CHR(34) + CHR(32) + CHR(39) + CHR(34)              // " '"                    
cQT2 := CHR(34) + CHR(39) + ',' + CHR(32) + CHR(34) + ';'  // "', "    
cQT3 := CHR(34) + ')' + CHR(32) + CHR(34) + ';'            // "') "

 
cTXT += "$cQuery2 .= " + "'VALUES ('; " + CRLF
FOR a = 1 TO LEN(aFLD) - 1
   cVAR := '$c' +  aFLD [A,1]
   cTXT += '$cQuery2 .= ' + cQT1 + ' . ' + cVAR + ' . ' + cQT2 + CRLF   
NEXT   
cVAR := '$c' +  aFLD [A,1]
cTXT += '$cQuery2 .= ' + cQT1 + ' . ' + cVAR + ' . ' + cQT3 + CRLF

? cTXT

 

//System.Clipboard := cTXT
//MSGINFO('Copied to clipboard')
RETURN
 








FUNCTION Create_PHP_UPDATE( cKEY1)        
/******************************/
LOCAL cQuery2, cTXT, cQT1, cQT2, cQT3, a, cVAR 

 
IF EMPTY(qHST) .OR. EMPTY(qUSR) .OR. EMPTY(qPAW) .OR. EMPTY(qDBN) .OR. EMPTY(cKEY1)
   MSGINFO(qMSG_5 ,'NOK' )	
   RETURN
ENDIF

IF SQL_Connect(qHST, qUSR, qPAW, qDBN)  == Nil 
   MSGINFO('SQL_CONNECT_ERROR' ,'NOK' )		 
   RETURN 
ENDIF

Get_Information_Schema(cKEY1)
dbo:Destroy()


 
aWHERE := {}
aSET   := {}
FOR a = 1 to len(aINDEXES)
   FOR b =  1 to len(aFLD)
      IF aINDEXES [a] == aFLD [b] [1]
         aFLD [b] [5] :=  'Unique'
         EXIT
      ENDIF
   NEXT
   AADD(aWHERE, {aFLD [b] [1], aFLD [b] [2]})
NEXT

FOR b =  1 to len(aFLD)
   GEV := FALSE
   FOR a = 1 to len(aWHERE)
      IF aWHERE [a] [1] == aFLD [b] [1]
         GEV := TRUE
         EXIT
      ENDIF
   NEXT
   IF !GEV  
      AADD(aSET, {aFLD [b] [1], aFLD [b] [2]})
   ENDIF
NEXT
 
cQT1 := CHR(34) + CHR(32) + CHR(39) + CHR(34) // " '"                    
cQT2 := CHR(34) + CHR(39) + ',' + CHR(32) + CHR(34) // "', "    
cQT3 := CHR(34) + ')' + CHR(32) + CHR(34)  // "') "
cQT4 := CHR(39) + CHR(34) + CHR(32) + CHR(39) + ';' // '" '                    

 
cTXT := "$cQuery2  = 'UPDATE " + cKEY1 + " '; " + CRLF
cVAR := '$c' +  aSET [1,1]
cTXT += "$cQuery2 .= 'SET " + aSET [1,1] + ' = "' +  "' . " + ' ' + cVAR + " . " + cQT4 + CRLF  // SET NON-KEYS 

FOR a := 2 to len(aSET)  
   cVAR := '$c' + aSET [a,1]
   cTXT += "$cQuery2 .= ' , " + aSET [a,1] + ' = "' +  "' . "  +  cVAR + " . " + cQT4 + CRLF  // SET NON-KEYS 
NEXT
 
cVAR := '$c' +  aWHERE [1,1]
cTXT += "$cQuery2 .= 'WHERE " + aWHERE [1,1] + ' = "' +  "' . " + ' ' + cVAR + " . " + cQT4 + CRLF // SET NON-KEYS 
for a := 2 to len(aWHERE)
   cVAR := '$c' +  aWHERE [a,1]
   cTXT += "$cQuery2 .= ' AND  " + aWHERE [a,1] + ' = "' +  "' . " + ' ' + cVAR + " . " + cQT4 + CRLF  // SET NON-KEYS 
next
? cTXT
 
 
/* something like this * /
 
$cQuery2  = 'UPDATE aTABLE_NAME ' ;
$cQuery2 .= 'SET field6 = "' . cfield6 . '" ';
$cQuery2 .= ' , field6 = "' . cfield7 . '" ';
$cQuery2 .= ' , field6 = "' . cfield8 . '" ';
$cQuery2 .= ' , field6 = "' . cfield9 . '" ';
$cQuery2 .= ' , field6 = "' . cfield10 . '" ';
$cQuery2 .= ' , field6 = "' . cfield11 . '" ';
$cQuery2 .= ' , field6 = "' . cfield12 . '" ';
$cQuery2 .= ' , field6 = "' . cfield13 . '" ';
$cQuery2 .= 'WHERE field1 = "' . cfield1 . '" ';
$cQuery2 .= ' AND  field2 = "' . cfield2 . '" ';
$cQuery2 .= ' AND  field3 = "' . cfield3 . '" ';
$cQuery2 .= ' AND  field4 = "' . cfield4 . '" ';
$cQuery2 .= ' AND  field5 = "' . cfield5 . '" '; 
*/


 //System.Clipboard := cTXT  
//MSGINFO('Copied to clipboard')
RETURN








FUNCTION Create_PHP_DELETE(cKEY1)
/********************************/
LOCAL cQuery2, cTXT, cQT1, cQT2, cQT3, a, cVAR 

 
IF EMPTY(qHST) .OR. EMPTY(qUSR) .OR. EMPTY(qPAW) .OR. EMPTY(qDBN) .OR. EMPTY(cKEY1)
   MSGINFO(qMSG_5 ,'NOK' )	
   RETURN
ENDIF

IF SQL_Connect(qHST, qUSR, qPAW, qDBN)  == Nil 
   MSGINFO('SQL_CONNECT_ERROR' ,'NOK' )		 
   RETURN 
ENDIF

Get_Information_Schema(cKEY1)
dbo:Destroy()

aWHERE := {}
aSET   := {}
FOR a = 1 to len(aINDEXES)
   FOR b =  1 to len(aFLD)
      IF aINDEXES [a] == aFLD [b] [1]
         aFLD [b] [5] :=  'Unique'
         EXIT
      ENDIF
   NEXT
   AADD(aWHERE, {aFLD [b] [1], aFLD [b] [2]})

NEXT

FOR b =  1 to len(aFLD)
   GEV := FALSE
   FOR a = 1 to len(aWHERE)
      IF aWHERE [a] [1] == aFLD [b] [1]
         GEV := TRUE
         EXIT
      ENDIF
   NEXT
   IF !GEV  
      AADD(aSET, {aFLD [b] [1], aFLD [b] [2]})
   ENDIF
   
NEXT
 
cQT1 := CHR(34) + CHR(32) + CHR(39) + CHR(34)         // " '"                    
cQT2 := CHR(34) + CHR(39) + ',' + CHR(32) + CHR(34)   // "', "    
cQT3 := CHR(34) + ')' + CHR(32) + CHR(34)             // "') "
cQT4 := CHR(39) + CHR(34) + CHR(32) + CHR(39)  + ';'  // '" '                    

 
cTXT := "$cQuery2  = 'DELETE FROM " + cKEY1 + " '; " + CRLF
cVAR := '$c' +  aWHERE [1,1]
cTXT += "$cQuery2 .= 'WHERE " + aWHERE [1,1] + ' = "' +  "' . " + cVAR + ' . ' + cQT4 + CRLF // SET NON-KEYS 
for a := 2 to len(aWHERE)
   cVAR := '$c' +  aWHERE [a,1]
   cTXT += "$cQuery2 .= ' AND  " + aWHERE [a,1] + ' = "' +  "' . " + cVAR + ' . '  + cQT4 + CRLF  // SET NON-KEYS 
next
? cTXT

 
/* something like this * /
$cQuery2  = 'DELETE FROM aTABLE_NAME ' ;
$cQuery2 .= 'WHERE field1 = "' . cfield1  . '" ';
$cQuery2 .= ' AND  field2 = "' . cfield2  . '" ';
$cQuery2 .= ' AND  field3 = "' . cfield3  . '" ';
$cQuery2 .= ' AND  field4 = "' . cfield4  . '" ';
$cQuery2 .= ' AND  field5 = "' . cfield5  . '" ';
*/


 //System.Clipboard := cTXT  
//MSGINFO('Copied to clipboard')
RETURN







FUNCTION SQL_Connect(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

RETURN dbo





FUNCTION DATE2SQL(xDATE)
/***********************/
IF VALTYPE(xDATE) == 'D' 
   xDATE := DTOC(xDATE)
ENDIF

xDATE := SUBSTR(xDATE,7,4) + '-' + SUBSTR(xDATE,4,2) + '-' + SUBSTR(xDATE,1,2)  

IF xDATE == '    -  -  '
	xDATE := 'NULL'
ENDIF
RETURN (xDATE)







FUNCTION Strvalue( string, onoff )
/*********************************/
LOCAL retval := pNULL

onoff := Valid_parm( onoff, pLOGICAL, FALSE )

DO CASE
CASE VALTYPE( string ) == pCHARACTER
	retval := ALLTRIM(string)

CASE VALTYPE( string ) == pNUMERIC
	retval := LTRIM( STR( string ) )

CASE VALTYPE( string ) == pMEMO
	retval := IF( (LEN(string) > (MEMORY(0) * 1024) * .80), ;
						SUBSTR(string,1, INT((MEMORY(0) * 1024) * .80)), ;
						string )

CASE VALTYPE( string ) == pDATE
	retval := DTOC( string )

CASE VALTYPE( string ) == pLOGICAL
	retval := IF( ( onoff ), IF(string, "On", "Off"), ;
				 IF(string, "True", "False") )
OTHERWISE
	retval := ''

ENDCASE

RETURN( retval )
There's nothing you can do that can't be done...
Top
User avatar
mustafa
Posts: 1160
Joined: Fri Mar 20, 2009 11:38 am
DBs Used: DBF
Location: Alicante - Spain
Contact:
Contact mustafa

Re: quarantiane-software compiled with HMG

Post by mustafa »

+1
merci
a bientot
Mustafa
Top
User avatar
Anand
Posts: 595
Joined: Tue May 24, 2016 4:36 pm
DBs Used: DBF

Re: quarantiane-software compiled with HMG

Post by Anand »

Ah..Ha..
You are creating the commands to be used in the program, very nice.

Regards,

Anand
Regards,

Anand

Image
Top
User avatar
dragancesu
Posts: 921
Joined: Mon Jun 24, 2013 11:53 am
DBs Used: DBF, MySQL, Oracle
Location: Subotica, Serbia

Re: quarantiane-software compiled with HMG

Post by dragancesu »

image_karantin.jpg
image_karantin.jpg (279.83 KiB) Viewed 2342 times
Top
User avatar
serge_girard
Posts: 3167
Joined: Sun Nov 25, 2012 2:44 pm
DBs Used: 1 MySQL - MariaDB
2 DBF
Location: Belgium
Contact:
Contact serge_girard

Re: quarantiane-software compiled with HMG

Post by serge_girard »

:D Ha ha !
There's nothing you can do that can't be done...
Top
User avatar
serge_girard
Posts: 3167
Joined: Sun Nov 25, 2012 2:44 pm
DBs Used: 1 MySQL - MariaDB
2 DBF
Location: Belgium
Contact:
Contact serge_girard

Re: quarantiane-software compiled with HMG

Post by serge_girard »

We now can buy Polish beer! Very tasty, so skol !

Serge
Attachments
IMG_20200411_164149.jpg
IMG_20200411_164149.jpg (68.2 KiB) Viewed 2303 times
There's nothing you can do that can't be done...
Top
User avatar
mol
Posts: 3723
Joined: Thu Sep 11, 2008 5:31 am
Location: Myszków, Poland
Contact:
Contact mol

Re: quarantiane-software compiled with HMG

Post by mol »

Two Żubr are better than one :lol: :lol: :lol: Cheers!

Top
User avatar
serge_girard
Posts: 3167
Joined: Sun Nov 25, 2012 2:44 pm
DBs Used: 1 MySQL - MariaDB
2 DBF
Location: Belgium
Contact:
Contact serge_girard

Re: quarantiane-software compiled with HMG

Post by serge_girard »

That's right!
There's nothing you can do that can't be done...
Top
Post Reply

Return to “General Discussions”