1. Mario Francisco Celleri
  2. PowerBuilder
  3. Monday, 20 May 2024 04:24 AM UTC

Hi guys;

 

Thanks for your time. I need to know what type of data retrieve a query; when use a datawindow.

I put into context what require or intend to do.

I have several queries to make to a database; on several tables, with different types of data.

These queries will be assigned to a single data window (one query at a time); This information must be processed but I need to know the type of data that this query is recovering, to be able to assign it to a variable.
Likewise, the number of fields may vary and I do not know how many fields the query will recover.

In short, what I need to do is process several queries to a database, and process it with a single data window; read and process the data; according to the type of data returned by the query/datawindow.

 

Hope you understood me.

 

Thanks and regards.

William Hong Accepted Answer Pending Moderation
  1. Wednesday, 22 May 2024 03:56 AM UTC
  2. PowerBuilder
  3. # 1

Here is a piece of code for your reference.

		IF lower(dwo.type)= "column" THEN
			Boolean okset
			okset=false
			CHOOSE CASE lower(dw.describe(string(dwo.name)+ ".coltype" ))
				CASE "string"
					String tempstring
					setnull(tempstring)
					dw.setitem(row,string(dwo.name),tempstring)
					okset=true
				CASE "char" TO "chas"
					Char tempch
					setnull(tempch)
					dw.setitem(row,string(dwo.name),tempch)
					okset=true
				CASE "int" , "integer" , "number"
					Integer tempint
					setnull(tempint)
					dw.setitem(row,string(dwo.name),tempint)
					okset=true
				CASE "long"
					Long templong
					setnull(templong)
					dw.setitem(row,string(dwo.name),templong)
					okset=true
				CASE "decimal" TO "decimam"
					Decimal tempdec
					setnull(tempdec)
					dw.setitem(row,string(dwo.name),tempdec)
					okset=true
				CASE "date"
					Date tempdate
					setnull(tempdate)
					dw.setitem(row,string(dwo.name),tempdate)
					okset=true
				CASE "datetime" , "timestamp"
					DateTime tempdatetime
					setnull(tempdatetime)
					dw.setitem(row,string(dwo.name),tempdatetime)
					okset=true
				CASE "double"
					Double tempdou
					setnull(tempdou)
					dw.setitem(row,string(dwo.name),tempdou)
					okset=true
				CASE "boolean"
					Boolean tempbool
					setnull(tempbool)
					dw.setitem(row,string(dwo.name),tempbool)
					okset=true
				CASE "real"
					Real tempreal
					setnull(tempreal)
					dw.setitem(row,string(dwo.name),tempreal)
					okset=true
				CASE "time"
					Time temptime
					setnull(temptime)
					dw.setitem(row,string(dwo.name),temptime)
					okset=true
				CASE "character"
					Character tempcha
					setnull(tempcha)
					dw.setitem(row,string(dwo.name),tempcha)
					okset=true
				CASE "unsignedinteger" , "unsignedint" , "uint"
					UnsignedInteger tempuint
					setnull(tempuint)
					dw.setitem(row,string(dwo.name),tempuint)
					okset=true
				CASE "unsignedlong" , "ulong"
					UnsignedLong tempulong
					setnull(tempulong)
					dw.setitem(row,string(dwo.name),tempulong)
					okset=true
			END CHOOSE

			IF okset THEN
				RETURN 3
			END IF
		END IF //column
Comment
There are no comments made yet.
Mario Francisco Celleri Accepted Answer Pending Moderation
  1. Tuesday, 21 May 2024 01:29 AM UTC
  2. PowerBuilder
  3. # 2

Dear Jhon; thanks for your answer.

I'm new using PowerBuilder, many of the things that both you and Cris have raised with me; are seem complex to me.
However, I have several things to clarify; first, I am connecting to an informix database from which I plan to make queries.
Then I want to insert the data that I recover into another mysql database.

How could I use SQLSA and SQLDA, if I have already defined a transaction type object like Jireh; how I could proceed.

I tried running your example code; however it is not executed; since it gives me this error:

 

 

Can you please help me.

Comment
  1. John Fauss
  2. Tuesday, 21 May 2024 13:49 PM UTC
If the name of your Transaction object is not "SQLCA", then you would need to replace "SQLCA" in the code sample I supplied with the name of your transaction object ("Jireh"?), such as in the Prepare statement and the IF statements where the SQLCode property of the Transaction object is tested.

What I've outlined is NOT a simple topic, so it is probably best for you to use the process that Chris has outlined. Not knowing that you are relatively new to PB, I only wanted to make you (and anyone else reading this thread) that an alternative approach is possible.
  1. Helpful
There are no comments made yet.
John Fauss Accepted Answer Pending Moderation
  1. Monday, 20 May 2024 20:35 PM UTC
  2. PowerBuilder
  3. # 3

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

Comment
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Monday, 20 May 2024 12:28 PM UTC
  2. PowerBuilder
  3. # 4

Hi Mario;

  I'm taking a guess here that you are using the Dynamic Data window feature (ie: SyntaxFromSQL command). If that's the case, then after the DWO has been instantiated, then use the DW Control's Describe() command to get the Data Type of the column(s) being processed.  HTH 

Regards ... Chris 

Comment
  1. Mario Francisco Celleri
  2. Tuesday, 21 May 2024 01:41 AM UTC
Dear Cris;



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.



  1. Helpful
  1. Chris Pollach @Appeon
  2. Tuesday, 21 May 2024 03:40 AM UTC
That's perfect.

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
  1. Helpful 1
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.