|
-
Sep 26th, 2007, 01:18 AM
#1
Thread Starter
Junior Member
Data export to text file problem
i'm having problem exporting my data. my last item data went mising and not display out in the text file after the program has been ran. but i've run the query in sql everything works well.what is the problem, anyone please help me to check why did the data went missing?is there any problem with my query or my looping?
---results from program---
**************************************** BOF ****************************************
Production reported from : 24/09/2007 23:00 to : 25/09/2007 23:00
Report run at : Wednesday 26/09/2007 11:53
=====================================================================================
WKSFCL: BE1PE| WKSPRD: SFH6156-4-ASY| WKSROU: DIP4-MAIN| FT1OPR: 7310|
WKSLOT| OUTDTE| INPUT_| OUTPUT| REJECT| YIELD|
WKSFCL: BE1PE| WKSPRD: IL205AM-ASY| WKSROU: SO8AMATRIX| FT1OPR: 7310|
WKSLOT| OUTDTE| INPUT_| OUTPUT| REJECT| YIELD|
1|M1738468| 1|9/25/2007 4:04:18 AM| 1|9506| 1|9258| 1|248| 1|0.9739|
2|M1738470| 2|9/25/2007 4:05:52 AM| 2|9702| 2|9582| 2|120| 2|0.9876|
WKSFCL: BE1PE| WKSPRD: LH1510-ASY| WKSROU: SSR-MAIN| FT1OPR: 7320|
WKSLOT| OUTDTE| INPUT_| OUTPUT| REJECT| YIELD|
1|M1737704| 1|9/25/2007 4:23:32 AM| 1|3345| 1|3217| 1|128| 1|0.9617|
---results retrieve directly from db---
WKSFCL| WKSLOT| WKSPRD| OUTDTE| INPUT_| OUTPUT| REJECT| YIELD| TIMESTAMP|
BE1PE M1738468 IL205AM-ASY 9/25/2007 4:04:18 AM 9506 9258 248 0.9739 9/26/2007 8:20:37 AM
BE1PE M1738470 IL205AM-ASY 9/25/2007 4:05:52 AM 9702 9582 120 0.9876 9/26/2007 8:20:37 AM
BE1PE M1737704 LH1510-ASY 9/25/2007 4:23:32 AM 3345 3217 128 0.9617 9/26/2007 8:20:37 AM
BE1PE M1737B92 SFH6156-4-ASY 9/25/2007 7:23:16 AM 45852 44004 1848 0.9597 9/26/2007 8:20:37 AM
BE1PE M1738978 SFH6156-4-ASY 9/25/2007 3:49:08 PM 84306 82131 2175 0.9742 9/26/2007 8:20:37 AM
---missing data from the program results---
BE1PE M1737B92 SFH6156-4-ASY 9/25/2007 7:23:16 AM 45852 44004 1848 0.9597 9/26/2007 8:20:37 AM
BE1PE M1738978 SFH6156-4-ASY 9/25/2007 3:49:08 PM 84306 82131 2175 0.9742 9/26/2007 8:20:37 AM
-
Sep 26th, 2007, 01:20 AM
#2
Thread Starter
Junior Member
Re: Data export to text file problem
code part1
vb Code:
Private Sub OpenDatabaseConnection()
On Error GoTo ErrorHandler
Set m_objConn = New ADODB.Connection
m_objConn.Open "Provider=OraOLEDB.Oracle;Data Source=acsp.world;User Id=acsrpt;Password=acsrpt;"
Debug.Print "Open Database connection..."
Call LogEvent("Open Database connection...", "OpenDatabaseConnection")
Exit Sub
ErrorHandler:
If m_objConn.State = adStateClosed Then
MsgBox "No Connection to The Server. Please try again later."
End If
Call LogError(Name, "OpenDatabaseConnection", Err.Number, Err.Description)
End Sub
Private Sub DelTbl()
On Error GoTo ErrorHandler
Dim stmt As String
stmt = "delete from ACSRPT.FT1YLD_LOTLIST"
Debug.Print "Truncating the data source file..."
' Truncate the lotlist table if it already exists
'' m_objConn.Execute "truncate table ACSRPT.FT1YLD_LOTLIST"
'' m_objConn.Execute "truncate table ACSRPT.FT1YLD_LOTLIST reuse storage"
'' m_objConn.Execute "delete from ACSRPT.FT1YLD_LOTLIST"
m_objConn.Execute stmt, , adCmdText
Exit Sub
ErrorHandler:
Call LogError(Name, "DelTbl", Err.Number, Err.Description)
End Sub
Private Sub InsTbl()
On Error GoTo ErrorHandler
Dim sqlQuery As String
sqlQuery = "insert into ACSRPT.FT1YLD_LOTLIST " & _
" (WKSFCL, WKSLOT, WKSPRD, OUTDTE, INPUT_, OUTPUT, REJECT, YIELD) " & _
"select WKSFCL, WKSLOT, WKSPRD, OUTDTE, INPUT_, OUTPUT, REJECT, " & _
"decode(INPUT_, NULL, 'In Qty is Null', " & _
" decode(sign(INPUT_), 1, to_char(round(OUTPUT/INPUT_, 4)), 0, 'In Qty is 0', -1, 'In Qty is negative'))" & _
"from ACS.WKSLOT " & _
" where (WKSPRD, WKSOPR) in " & _
" ( select WKSPRD, FT1OPR from ACSRPT.FT1YLD_PRDOPR) " & _
" and OUTDTE >= (select to_date(max(PERIOD_END), 'YYYYMMDDHH24MISS') - 1 + 1/86400 " & _
" from ACS.TMPSEQ) " & _
" and OUTDTE <= (select to_date(max(PERIOD_END), 'YYYYMMDDHH24MISS') " & _
" from ACS.TMPSEQ)"
'' "insert into ACSRPT.FT1YLD_LOTLIST " & _
" (WKSFCL, WKSLOT, WKSPRD, OUTDTE, INPUT_, OUTPUT, REJECT, YIELD) " & _
"select a.WKSFCL, a.WKSLOT, a.WKSPRD, a.OUTDTE, a.INPUT_, a.OUTPUT, " & _
" a.REJECT, decode(a.INPUT_, NULL, 'In Qty is Null', " & _
" decode(sign(a.INPUT_), 1, round(a.OUTPUT/a.INPUT_, 4), 0, 'In Qty is 0', -1, 'In Qty is negative')) " & _
"from " & _
"( select * from ACS.WKSLOT " & _
" where WKSFCL = 'BE1PE' " & _
" and OUTDTE >= (select to_date(max(PERIOD_END), 'YYYYMMDDHH24MISS') - 1 + 1/86400 " & _
" from ACS.TMPSEQ) " & _
" and OUTDTE <= (select to_date(max(PERIOD_END), 'YYYYMMDDHH24MISS') " & _
" from ACS.TMPSEQ)) a, " & _
"( select WKSPRD, FT1OPR from ACSRPT.FT1YLD_PRDOPR) b " & _
"where a.WKSPRD = b.WKSPRD And a.WKSOPR = b.FT1OPR"
Debug.Print "Populating the data source file..."
' Populate the table.
m_objConn.Execute sqlQuery, , adCmdText
Debug.Print "Data source file populated."
'' m_objConn.Execute "commit write immediate NOWAIT"
'' m_objConn.Execute "commit"
Exit Sub
ErrorHandler:
Call LogError(Name, "InsTbl", Err.Number, Err.Description)
End Sub
-
Sep 26th, 2007, 01:20 AM
#3
Thread Starter
Junior Member
Re: Data export to text file problem
vb Code:
Private Sub ExtractDataToText()
On Error GoTo ErrorHandler
'Declare the ADO objects variables
Dim objRsParent As ADODB.Recordset
Dim objRsChild As ADODB.Recordset
'Other variables
Dim strSQLQueryParent As String
Dim strSQLQueryChild As String
'' Dim g_strExportLogFileName As String
'Define the Log filename
g_strExportLogFileName = App.Path & "\" & Trim(GetINISetting(m_strINIFile, "ExecutionControl", "ExportDataFilename", "?"))
' Open the forward-only, read-only recordset
strSQLQueryParent = "select * from ACSRPT.FT1YLD_PRDOPR b " & _
"where exists(" & _
"select 1 from ACSRPT.FT1YLD_LOTLIST a " & _
"where a.WKSPRD = b.WKSPRD)"
Set objRsParent = New ADODB.Recordset
objRsParent.Open strSQLQueryParent, m_objConn, adOpenStatic, adLockReadOnly
strSQLQueryChild = "select WKSPRD, WKSLOT, OUTDTE, INPUT_, OUTPUT, REJECT, YIELD " & _
"from ACSRPT.FT1YLD_LOTLIST"
Set objRsChild = New ADODB.Recordset
objRsChild.Open strSQLQueryChild, m_objConn, adOpenStatic, adLockReadOnly
If Not objRsChild.BOF Then
'' MsgBox "Active Recordsets opened. Start writing data to text..."
Debug.Print "Active Recordsets opened. Start writing data to text..."
Open g_strExportLogFileName For Output As #1
'Write the header
Print #1, "**************************************** BOF ****************************************"
Print #1, "Production reported from : " & Format(Now - 2, "dd/mm/yyyy 23:00") & _
" to : " & Format(Now - 1, "dd/mm/yyyy 23:00")
Print #1, "Report run at : " & Format(Now, "dddd") & " " & Format(Now, "dd/mm/yyyy hh:mm")
Print #1, "====================================================================================="
'Write a blank line to the log file.
Print #1, ""
Debug.Print "Connected to the data source file."
Call LogEvent("Connected to the data source file.", "ExtractDataToText")
'' MsgBox "Writing file to " & g_strExportLogFileName
Debug.Print "Retrieving data from recordset..."
Call LogEvent("Retrieving data from recordset...", "ExtractDataToText")
Dim strParentRow As String
Dim strChildRow As String
Dim sDelimiter As String
sDelimiter = "| "
Dim i As Long, j As Integer ' i = row counter, j = column counter
Dim strProductFlag As String
i = 0
''MsgBox "Get product list..."
Debug.Print "Retrieving product list from recordset..."
' Write the data from recordset
Do While Not objRsParent.EOF
'' MsgBox objRsParent(0).Name
strParentRow = ""
For j = 0 To 3
strParentRow = strParentRow & objRsParent(j).Name & ": " & objRsParent(j).Value & sDelimiter
Next j
'Write new line
Print #1, strParentRow
strProductFlag = objRsParent(1)
objRsParent.MoveNext
'' Do While Not objRsChild.EOF
''MsgBox objRsChild(0) & " +++ " & strProductFlag
Print #1, "" 'write a new blank line
'' MsgBox "Get product details..."
Debug.Print "Retrieving product details from recordset..."
For j = 1 To 6
Print #1, objRsChild(j).Name & sDelimiter;
Next j
Print #1, "" 'write a new blank line
i = 0
'' Do While Not objRsChild.EOF ' Write the child data
Do While Not objRsChild.BOF ' Write the child data
If objRsChild(0) = strProductFlag Then
i = i + 1
For j = 1 To 6
Print #1, i & "|" & objRsChild(j).Value & sDelimiter & " "; ;
'' strChildRow = strChildRow & objRsChild(j).Value & sDelimiter
Next j
'' Print #1, strChildRow & " "; ;
Else
Print #1, vbCrLf
Exit Do
End If
objRsChild.MoveNext
Print #1, 'Write new line
Loop
i = i + 1
'' Exit Do
Print #1, "" 'write a new blank line
Loop
'write a new blank line
Print #1, ""
'write to new line
Print #1,
Close #1 ' Close the log file.
Else
MsgBox "Recordset has no data. Automation will not proceed. ", vbExclamation, "Automation Not Run"
End If
'' MsgBox "Complete loop"
Debug.Print "Report generation completed and the Text file was saved to disk."
Call LogEvent("Report generation completed and the Text file was saved to disk.", "ExtractDataToText")
'Close the objects
objRsParent.Close
objRsChild.Close
'' m_objConn.Close
'Release the memory associated with the objects
Set objRsParent = Nothing
Set objRsChild = Nothing
'' Set m_objConn = Nothing
Call LogEvent("Close the data source connection and release ADO objects.", "ExtractDataToText")
Exit Sub
ErrorHandler:
''Resume Next
Call LogError(Me.Name, "ExtractDataToText", Err.Number, Err.Description)
End Sub
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote