Hi, Mario -
Chris has provided an excellent suggestion on how you might wish to proceed and it is probably what I would do, depending on the requirements of the task at hand.
I would venture a guess that not many PB developers have ever used the DynamicStagingArea and DynamicDescriptorArea objects, and these could also be used. Just as PB provides a default Transaction object, SQLCA, it also provides default instances of these other two objects; SQLSA and SQLDA, respectively.
If Chris' suggestion is not viable for you, I'm including below some sample code that illustrates how you might utilize these lesser-known objects to determine at execution type the PB datatype for each column returned by a SQL SELECT statement:
// Dynamically determines the number of columns returned by a SQL SELECT statement
// and the datatype of each column.
Integer li_num_columns, li_index
Long ll_rc
String ls_sql_select, ls_value, ls_name, ls_msg
// Define the syntax of the SQL SELECT statement...
ls_sql_select = "SELECT TOP 1 * FROM sys.objects"
// Execute the SQL, fetch the first row of the result
// set and populate the DynamicDescriptorArea object.
Prepare SQLSA From :ls_sql_select Using SQLCA;
Describe SQLSA Into SQLDA;
Declare c_values_cursor Dynamic Cursor For SQLSA;
Open Dynamic c_values_cursor Using Descriptor SQLDA;
Fetch c_values_cursor Using Descriptor SQLDA;
ll_rc = SQLCA.SQLCode
// How many columns are in the result set?
li_num_columns = SQLDA.NumOutputs
ls_msg = "SQL: " + ls_sql_select + "~r~n~r~nNumber of columns: " + &
String(li_num_columns) + "~r~n"
For li_index = 1 To li_num_columns
ls_msg += "~r~n " + String(li_index) + ":~t"
Choose Case SQLDA.OutParmType[li_index]
Case TypeBoolean!
ls_msg += "Boolean"
Case TypeByte!
ls_msg += "Byte"
Case TypeDate!
ls_msg += "Date"
Case TypeDateTime!
ls_msg += "DateTime"
Case TypeDecimal!
ls_msg += "Decimal"
Case TypeDouble!
ls_msg += "Double"
Case TypeInteger!
ls_msg += "Integer"
Case TypeLong!
ls_msg += "Long"
Case TypeLongLong!
ls_msg += "LongLong"
Case TypeReal!
ls_msg += "Real"
Case TypeString!
ls_msg += "String"
Case TypeTime!
ls_msg += "Time"
Case TypeUInt!
ls_msg += "UnsignedInt"
Case TypeULong!
ls_msg += "UnsignedLong"
Case Else
ls_msg += "*Unknown*"
End Choose
Next
// The following code illustrates how you might obtain the data values
// for the first column of each row of the result set as a String.
//Do While SQLCA.SQLCode = 0
// Choose Case SQLDA.OutParmType[1]
// Case TypeString!
// ls_value = GetDynamicString(SQLDA,1)
// Case TypeDate!
// ls_value = String(GetDynamicDate(SQLDA,1))
// Case TypeTime!
// ls_value = String(GetDynamicTime(SQLDA,1))
// Case TypeDateTime!
// ls_value = String(GetDynamicDateTime(SQLDA,1))
// Case Else
// ls_value = String(GetDynamicNumber(SQLDA,1))
// End Choose
//
// ... Do whatever is needed with the String value here...
//
// Obtain the next row of the result set.
// Fetch c_values_cursor Using Descriptor SQLDA;
// ll_rc = SQLCA.SQLCode
//Loop
Close c_values_cursor;
MessageBox("Result Set Column Information",ls_msg)
Return 0
The SELECT statement in this example is applicable for SQL Server and it only retrieves one row. I've included commented code that shows how you could fetch all of the rows in the result set. Your mileage, of course, may vary.
A little more coding work, admittedly, but you have more flexibility in what you can do, depending on your needs.
Good luck with your project!
Best regards, John
