178 lines
6.4 KiB
Plaintext
178 lines
6.4 KiB
Plaintext
LPARAMETERS tcConnection, tcTable, tlNull, tcFormat, tnMaxCharFormat, tcPrefix, tcSufix
|
|
*!* EX:
|
|
*!* Set Procedure To GET_SCHEMA.PRG Additive
|
|
*!* LOSCHEMA = GET_SCHEMA("JCSSERVER;MARIUSM_AUTO;123","VACT",.F.,"FOX", 100, "['", " '] + ;")
|
|
*!* DO GET_SCHEMA - PUNE IN _CLIPBOARD COLOANELE SI SCHEMA
|
|
#DEFINE CRLF CHR(13) + CHR(10)
|
|
|
|
LOCAL lcTable, lcConnection, loReturn
|
|
LOCAL lcSelect, lcSchema, i, lcGrupat, lcGrupa
|
|
lcTable = ''
|
|
lcConnection = ''
|
|
|
|
loReturn = CREATEOBJECT("empty")
|
|
ADDPROPERTY(loReturn,"cSelect", "")
|
|
ADDPROPERTY(loReturn,"cSchema", "")
|
|
|
|
|
|
IF !EMPTY(tcConnection)
|
|
lcConnection = tcConnection
|
|
ELSE
|
|
lcConnection = 'JCSSERVER;MARIUSM_AUTO;123'
|
|
lcConnection = INPUTBOX('Conexiune', 'Conexiune', lcConnection)
|
|
ENDIF
|
|
IF !(EMPTY(lcConnection) OR OCCURS(';', lcConnection) < 2)
|
|
IF !EMPTY(tcTable)
|
|
lcTable = tcTable
|
|
ELSE
|
|
lcTable = INPUTBOX('Tabel', 'Tabel', lcTable)
|
|
ENDIF
|
|
IF !EMPTY(lcTable)
|
|
lcTable = UPPER(lcTable)
|
|
IF PCOUNT() = 0 && am apelat functia fara parametri
|
|
tcFormat = INPUTBOX("Formatare","FOX","FOX")
|
|
ENDIF
|
|
IF PCOUNT() = 0
|
|
tlNull = .T.
|
|
ENDIF
|
|
|
|
lcCursor = 'cResult'
|
|
lcSelect = [select COLUMN_NAME, DATA_TYPE, DATA_PRECISION, DATA_SCALE, DATA_LENGTH, NULLABLE ] + ;
|
|
[ FROM user_tab_columns where table_name = '] + lcTable + [' order by column_id]
|
|
|
|
*!* lcSelect = [SELECT stringagg(COLUMN_NAME) CSELECT,] + ;
|
|
*!* [ stringagg(COLUMN_NAME || ' ' ||] + ;
|
|
*!* [ DECODE(DATA_TYPE,] + ;
|
|
*!* [ 'NUMBER',] + ;
|
|
*!* [ 'N(' || LEAST(NVL(DATA_PRECISION,DATA_LENGTH), 20) ||] + ;
|
|
*!* [ (CASE NVL(DATA_SCALE,0)] + ;
|
|
*!* [ WHEN 0 THEN] + ;
|
|
*!* [ ')'] + ;
|
|
*!* [ ELSE] + ;
|
|
*!* [ ',' || DATA_SCALE || ')'] + ;
|
|
*!* [ END) || (CASE NULLABLE WHEN 'N' THEN ' NOT NULL' ELSE ' NULL' END),] + ;
|
|
*!* [ 'DATE',] + ;
|
|
*!* [ (CASE] + ;
|
|
*!* [ WHEN COLUMN_NAME LIKE 'DATAORA%' THEN] + ;
|
|
*!* [ 'T'] + ;
|
|
*!* [ ELSE] + ;
|
|
*!* [ 'D'] + ;
|
|
*!* [ END) || (CASE NULLABLE WHEN 'N' THEN ' NOT NULL' ELSE ' NULL' END),] + ;
|
|
*!* [ 'VARCHAR2',] + ;
|
|
*!* [ (CASE WHEN NVL(DATA_LENGTH,0) > 254 THEN 'M' ELSE ] + ;
|
|
*!* [ 'V(' || DATA_LENGTH || ')' END)|| (CASE NULLABLE WHEN 'N' THEN ' NOT NULL' ELSE ' NULL' END),] + ;
|
|
*!* [ 'CHAR',] + ;
|
|
*!* [ (CASE WHEN NVL(DATA_LENGTH,0) > 254 THEN 'M' ELSE ] + ;
|
|
*!* [ 'V(' || DATA_LENGTH || ')' END) || (CASE NULLABLE WHEN 'N' THEN ' NOT NULL' ELSE ' NULL' END),] + ;
|
|
*!* [ 'CLOB',] + ;
|
|
*!* [ 'M' || (CASE NULLABLE WHEN 'N' THEN ' NOT NULL' ELSE ' NULL' END),] + ;
|
|
*!* [ 'M' || (CASE NULLABLE WHEN 'N' THEN ' NOT NULL' ELSE ' NULL' END))) CSCHEMA] + ;
|
|
*!* [ FROM (select COLUMN_NAME,] + ;
|
|
*!* [ DATA_TYPE,] + ;
|
|
*!* [ DATA_PRECISION,] + ;
|
|
*!* [ DATA_SCALE,] + ;
|
|
*!* [ DATA_LENGTH,] + ;
|
|
*!* [ NULLABLE] + ;
|
|
*!* [ FROM user_tab_columns] + ;
|
|
*!* [ where table_name = '] + lcTable + ['] + ;
|
|
*!* [ order by column_id)]
|
|
|
|
lcHost = GETWORDNUM(lcConnection,1,';')
|
|
lcUser = GETWORDNUM(lcConnection,2,';')
|
|
lcPassword = GETWORDNUM(lcConnection,3,';')
|
|
|
|
X= SQLCONNECT(lcHost, lcUser, lcPassword)
|
|
IF X < 0
|
|
AERROR(laErr)
|
|
MESSAGEBOX(laErr(3))
|
|
RETURN
|
|
ENDIF
|
|
|
|
lnSucces = SQLEXEC(X, lcSelect, lcCursor)
|
|
IF lnSucces < 0
|
|
AERROR(laErr)
|
|
MESSAGEBOX(laErr(3))
|
|
ELSE
|
|
lcSchema = ""
|
|
lcSelect = ""
|
|
|
|
SELECT (lcCursor)
|
|
SCAN
|
|
DO CASE
|
|
CASE DATA_TYPE = 'NUMBER'
|
|
lcItemSchema = 'N(' + ALLTRIM(STR(MIN(NVL(DATA_PRECISION,DATA_LENGTH), 20))) + ;
|
|
IIF(NVL(DATA_SCALE,0) = 0, ')', ',' + ALLTRIM(STR(DATA_SCALE)) + ')')
|
|
CASE DATA_TYPE = 'DATE'
|
|
lcItemSchema = IIF('DATAORA'$COLUMN_NAME, 'T', 'D')
|
|
CASE DATA_TYPE = 'VARCHAR2'
|
|
lcItemSchema = IIF(NVL(DATA_LENGTH,0) > 254, 'M', 'V(' + ALLTRIM(STR(NVL(DATA_LENGTH,0))) + ')')
|
|
CASE DATA_TYPE = 'CHAR'
|
|
lcItemSchema = IIF(NVL(DATA_LENGTH,0) > 254, 'M', 'C(' + ALLTRIM(STR(NVL(DATA_LENGTH,0))) + ')')
|
|
OTHERWISE
|
|
lcItemSchema = 'M'
|
|
ENDCASE
|
|
lcSchema = lcSchema + ALLTRIM(COLUMN_NAME) + ' ' + lcItemSchema + IIF(tlNull, IIF(NULLABLE = 'N',' NOT NULL',' NULL'), '') + ','
|
|
lcSelect = lcSelect + ALLTRIM(COLUMN_NAME) + ','
|
|
ENDSCAN
|
|
IF !EMPTY(lcItemSchema)
|
|
lcSchema = LEFT(lcSchema , LEN(lcSchema )-1)
|
|
lcSelect = LEFT(lcSelect , LEN(lcSelect )-1)
|
|
ENDIF
|
|
|
|
USE IN SELECT(lcCursor)
|
|
|
|
&& FORMATARE TEXT - 250 CARACTERE PE RAND
|
|
IF UPPER(ALLTRIM(TRANSFORM(tcFormat))) = 'FOX'
|
|
lcSelect = format_fox(lcSelect, tnMaxCharFormat, tcPrefix, tcSufix)
|
|
lcSchema = format_fox(lcSchema, tnMaxCharFormat, tcPrefix, tcSufix)
|
|
ENDIF && UPPER(tcFormat) = 'FOX'
|
|
|
|
_CLIPTEXT = ALLTRIM(lcSelect) + CHR(13) + CHR(10) + ALLTRIM(lcSchema)
|
|
loReturn.cSelect = ALLTRIM(lcSelect)
|
|
loReturn.cSchema = ALLTRIM(lcSchema)
|
|
|
|
ENDIF
|
|
|
|
SQLDISCONNECT(X)
|
|
WAIT WINDOW 'Schema este in Clipboard' NOWAIT
|
|
ENDIF
|
|
ENDIF
|
|
|
|
RETURN loReturn
|
|
|
|
|
|
PROCEDURE format_fox
|
|
LPARAMETERS tcText, tnMaxCharFormat, tcPrefix, tcSufix
|
|
|
|
LOCAL lcText, lcGrupa, lcRezultat
|
|
lcRezultat = ""
|
|
lcText = tcText
|
|
|
|
IF PCOUNT() < 2 OR EMPTY(tnMaxCharFormat) OR TYPE('tnMaxCharFormat') <> 'N'
|
|
tnMaxCharFormat = 250
|
|
ENDIF
|
|
|
|
tnMaxCharFormat = MIN(tnMaxCharFormat, 250)
|
|
IF PCOUNT() < 3 OR TYPE('tcPrefix') <> 'C' OR EMPTY(tcPrefix)
|
|
tcPrefix = "["
|
|
ENDIF
|
|
|
|
IF PCOUNT() < 4 OR TYPE('tcSufix') <> 'C' OR EMPTY(tcSufix)
|
|
tcSufix = " ] + ;"
|
|
ENDIF
|
|
|
|
|
|
DO WHILE LEN(lcText) > tnMaxCharFormat
|
|
lcGrupa = LEFT(lcText, tnMaxCharFormat)
|
|
lnAt = RAT(',', lcGrupa)
|
|
lcGrupa = LEFT(lcGrupa, lnAt)
|
|
lcRezultat = lcRezultat + tcPrefix + lcGrupa + tcSufix + CRLF
|
|
lcText = SUBSTR(lcText, lnAt+1)
|
|
ENDDO
|
|
IF LEN(lcText) > 0
|
|
lcRezultat = lcRezultat + tcPrefix + lcText + tcSufix + CRLF
|
|
ENDIF
|
|
|
|
RETURN lcRezultat
|
|
ENDPROC && format_fox
|