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
I tried to do this;
lds_datastore = CREATE datastore
//lds_datastore.DataObject = "d_cust_list"
lds_datastore.create(Jireh.SyntaxFromSQL(str_query, 'Style(Type=Form)', ls_err))
lds_datastore.SetTransObject (Jireh)
lds_datastore.Retrieve()
messagebox ("Registros del DS ", string(lds_datastore.rowcount()))
For li_contador = 1 TO li_num_campos
ls_campo=lds_datastore.Describe("#"+String(li_contador) + ".name")
ls_tipo=mid(upper(lds_datastore.Describe("#"+String(li_contador) + ".ColType")),1,4)
choose case ls_tipo
case 'LONG','INTE'
// ll_valor=lds_datastore.getitemnumber(lds_datastore.getrow(),ls_campo)
case 'CHAR'
// ls_valor=lds_datastore.getitemstring(lds_datastore.getrow(),ls_campo)
case 'DATE'
// lda_valor=lds_datastore.getitemdate(lds_datastore.getrow(),ls_campo)
end choose
Next
I don't know if this is the correct way to recover the fields (the choose case ls_tipo)
Then; I'm don't know how to populate de cursor, and then if it's possible declare cursor syntax; and then do the fetch to read the cursor and retrieve the records and how to use the data type and name of fields.
Can you please help me.
Thanks and regards.
The DataStore.Retrieve () has already processed the DB cursor for you & now all the related datum is in the DWO's Primary Buffer.
So once you know the various columns & their data type, you just need to loop though the rows using the appropriate GetItemXxxxx() command. HTH
HTH