Data Access Worldwide Knowledge Base
Article Text
QUESTION:
Can I feed a cWinReport2 with results from a Embedded SQL operation?
ANSWER:
Yes, this is possible. What you need to do is take care of the record search operation yourself and, for each found record, send the message Handle_Report_Line to the report object. In the case of Embedded SQL, the record search operation will be retrieving the next information from the result-set.
IMPLEMENTATION:
If you take a normal WinPrint2 customer list report view add a property named phStatement to the object in a way like:
Object oReport Is A cWinReport2
Set Report_Title to "CustomerListing"
Property Handle phStatement
Then augment the method Start_Report to:
Function Start_Report returns integer
Handle hoSQL hDatabase hStatement
Integer iReportStatus
String sSQLStatement
Get Create U_cSQLHandleManager To hoSQL
If (hoSQL > 0) Begin
Get SQLFileConnect Of hoSQL Customer.File_Number To hDatabase
If (hDatabase > 0) Begin
Get SQLOpen Of hDatabase To hStatement
If (hStatement > 0) Begin
Set phStatement To hStatement
Move "Select * from Customer" To sSQLStatement
Send SQLExecDirect Of hStatement sSQLStatement
End
Get Setup_Report To iReportStatus // ret: 0-OK 1-Abort
If (iReportStatus <> RPT_OK) Begin
Function_Return iReportStatus
End
Repeat
Get Handle_Report_Line To iReportStatus
Until ((iReportStatus <> RPT_OK) And (iReportStatus <> RPT_NOT_SELECT))
Get End_Report iReportStatus To iReportStatus
Send SQLClose Of hStatement
Set phStatement To 0
Send SQLDisconnect Of hDatabase
End
Send Destroy Of hoSQL
End
Function_Return iReportStatus
End_Function // Start_Report
The above routine opens a SQL connection based on an already opened file (this means that with PSQL you need to use a slightly different operation since SQLFileConnect is not possible there). A statement (in this case a simple select) is executed. The rest of the code is responsible for setup of the report, handle the results and disconnect from the SQL connection.
The SQL statement here is a very simple SELECT statement but it is quite easy to change this to something like:
Select * from customer where customer.credit_limit > 1500 and customer.credit_limit < 2000 And Customer.City = 'San Diego'
Or
Select * from customer where customer.credit_limit > 1500 and customer.credit_limit < 2000 Or Customer.City = 'San Diego'
The next routine to implement is Handle_Report_Line:
Function Handle_Report_Line Returns Integer
Handle hStatement
Integer iFetchResult iReportStatus
Boolean bCancelled
Move RPT_OK To iReportStatus
Get phStatement To hStatement
If (hStatement > 0) Begin
Get SQLFetch Of hStatement To iFetchResult
If (iFetchResult <> 0) Begin
Send SQLBindFile Of hStatement 0
Send Body
End
Else Begin
Move RPT_END To iReportStatus
End
End
Get Cancelled_State To bCancelled
If (bCancelled) Begin
Move RPT_CANCEL to iReportStatus
End
If (iReportStatus = RPT_OK Or iReportStatus = RPT_NOT_SELECT) Begin
Get Handle_KeyPressed To iReportStatus
End
Function_Return iReportStatus
End_Function // Handle_Report_Line
In this routine we transfer a record from the result set to the global record buffer (also updated when we would have done a "simple" FIND statement). The report itself uses the record buffer to print from. The net result is a report that uses the result of your operation. If you cannot use SQLBindFile the whole issue will probably be a bit more difficult but I don't see why it shouldn't work.
The enclosed view shows the full result of this test to use ESQL with WinPrint.
Note: It is a VDF12.0 view but with some work it can be used in earlier versions.
Contributed By:
Vincent Oorsprong
Company: Data Access Worldwide
email: vincent.oorsprong@dataaccess.eu
Web Site: http://www.dataaccess.eu
Web Links Related to this Article
File CustomerListing.rv
URL=http://www.dataaccess.com/KBasePublic/Files/2228.CustomerListing.rv
Email this Article
Email this Article to a Colleague
Send Feedback on this Article to Data Access Worldwide
Copyright ©2010 Data Access Corporation. All rights reserved.
| The information provided in the Data Access Technical Knowledge Base is provided "as is" without warranty of any kind. Data Access Corporation disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. In no event shall Data Access Corporation or its suppliers be liable for any damages whatsoever including direct, indirect, incidental, consequential, loss of business profits or special damages, even if Data Access Corporation or its suppliers have been advised of the possibility of such damages. Some states do not allow the exclusion or limitation of liability for consequential or incidental damages so the foregoing limitation may not apply. |