REM convaster.bas
REM
REM This script converts a selected asterix in a SQL statement into
REM a field list.
REM
REM Usage:
REM Select the asterix in a SQL statement and start the script.

'---------------------------------------------------------------
' (C) 2003 Alligator Company Soft:ware GmbH
' http://www.alligatorsql.com
' Author : Manfred Peter
'---------------------------------------------------------------

DIM nViewIndex AS INTEGER
DIM nIndex     AS INTEGER
DIM sOut       AS STRING
DIM nCount     AS INTEGER
DIM nCol       AS INTEGER
DIM nRow       AS INTEGER
DIM nMaxRow    AS INTEGER

'----------------------------------------------------
' check if a view is open and the asterix is selected
nCount = GetViewCount()

IF nCount = 0 THEN
   OUTPUT("Please select the asterix in the SQL statement")
   END
END IF

nViewIndex = GetActualViewIndex()
sSelect = View(nViewIndex).GetSelection()
IF sSelect = "" THEN
   OUTPUT("Please select the asterix in the SQL statement")
   END
END IF

'-----------------------
' scan for the word FROM
nCol = View(nViewIndex).GetCursorCol()
nRow = View(nViewIndex).GetCursorRow()
nMaxRow = View(nViewIndex).GetLineCount()

DIM sString  AS STRING
DIM sLine    AS STRING
DIM nColFind AS INTEGER
DIM sChar    AS STRING

DIM nWordStart AS INTEGER
DIM nWordEnd   AS INTEGER

DIM sTable AS STRING
DIM nMaxCol AS INTEGER

DIM nIndexMain AS INTEGER

View(nIndex).ReplaceSelection(" ")

FOR nIndexMain = nRow TO nMaxRow STEP 1
    sLine = View(nViewIndex).ReadLine(nRow)
    nMaxCol = LEN(sLine)

    ' Make it upper for the find command
    sLine = UCASE(sLine)
    View(nIndex).FindString("FROM")
    nColFind = View(nViewIndex).GetCursorCol()

    IF nColFind <> nCol THEN
       ' String has been found .... now read first table
       FOR nIndex1 = nRow TO nMaxRow STEP 1
           FOR nIndex2 = nColFind+4 TO nMaxCol STEP 1
               sChar = MID(sLine, nIndex2, 1)
               IF sChar <> " " THEN
                  nWordStart = nIndex2
                  FOR nIndex3 = nIndex2+1 TO nMaxCol STEP 1
                      sChar = MID(sLine, nIndex3, 1)
                      IF sChar = " " OR sChar = "," OR sChar = "" THEN
                         nWordEnd = nIndex3
                         nIndex3 = nMaxCol + 1
                      END IF
                  NEXT

                  sTable = Mid(sLine, nWordStart, nWordEnd-nWordStart)

                  ' Leave the loop
                  nIndex2 = nMaxCol + 1
                  nIndex1 = nMaxRow + 1
               END IF
           NEXT
       NEXT

       ' Leave the loop
       nIndex = nMaxRow + 1
    END IF
    
NEXT


sTable = TRIM(sTable)
IF sTable = "" THEN
   OUTPUT("No table found ...")
   END
END IF

DIM sConnection AS STRING
sConnection = GetConnectionFromTree()
IF sConnection = "" THEN
   PRINT "Please connect to an Oracle database and select a tree element"
   END
END IF

DIM sCursorHandle AS STRING
DIM sSQL AS STRING
sTable = UCASE(sTable)

sSQL = "select column_name FROM sys.all_tab_columns WHERE table_name = '"
sSQL = sSQL + sTable
sSQL = sSQL + "'"

sCursorHandle = OpenCursor(ORACLE, sConnection, sSQL)  
IF sCursorHandle = "0" THEN  
   OUTPUT("Error in SQL statement")
   END  
END IF  

DIM sReturn AS STRING
sReturn = FetchCursor(sCursorHandle)  
DIM nCount AS INTEGER  
nCount = GetFieldCountCursor(sConnection, sCursorHandle)  
sOut = ""  

WHILE sReturn = "0"  
 
    sField = GetVarByPositionCursor(0)  
    sOut = sOut + sField
    sOut = sOut + ","  
    sReturn = FetchCursor(sCursorHandle)  
WEND

sOut = LEFT(sOut, LEN(sOut)-1)

View(nViewIndex).InsertString(nRow, nCol, sOut)
 
CloseCursor(sCursorHandle)