1. Gary Herrault
  2. .NET DataStore
  3. Thursday, 22 September 2022 19:49 PM UTC

Hi.  I'm looking for a way in my web api to obtain the column data types from a sql select statement. I am currently trying to use  datastore.Describe( colName.ColType ) which returns "System.String" and "System.Decimal" without the length of the string and precision of the decimal. Is there some other datastore property I can use with the Describe method in order to obtain these values?

using PowerScript.Bridge;

   var dwMeta = DwMeta.CreateFromSql(sqlSelect, _dataContext);
   IDataStore ds = DataStore.Create(dwMeta, _dataContext);

   int columnCount = int.Parse(ds.Describe("Datawindow.Column.Count"));
   for (int colNo = 1; colNo <= columnCount ; colNo++)
   {
      string colName = ds.Describe("#" + (colNo - 1) + ".Name");
      string colType = ds.Describe(colName + ".ColType");
   }

Thanks.

 

Gary Herrault Accepted Answer Pending Moderation
  1. Friday, 23 September 2022 21:01 PM UTC
  2. .NET DataStore
  3. # 1

Happy to say I did find a solution for a SQL Anywhere back end which is what I am working with.  I found a couple of build in procedures that provide the needed information as follows:

In SQL Anywhere:

CREATE PROCEDURE p_sql_info(procedure_ind CHAR, sql_select LONG VARCHAR )
BEGIN
   DECLARE curTest CURSOR USING sql_select;
   --
   IF procedure_ind = 'Y' THEN
      OPEN curTest;
      SELECT * INTO #describe FROM dbo.sa_describe_cursor('curTest');
      CLOSE curTest;
   ELSE
      SELECT * INTO #describe FROM dbo.sa_describe_query(sql_select);
   END IF;
   DEALLOCATE curTest;
   --
   SELECT isnull(name, base_column_name) name,
               domain_name_with_size coltype,
               isnull(user_type_name, domain_name) datatype,
              width,
              scale
     FROM #describe
    ORDER BY column_number;
END;

In Web API:

        public IDataStore RetrieveInfo(Char procedureInd, String sqlSelect)
        {
            sqlSelect = "Call p_sql_info('" + procedureInd + "','" + sqlSelect + "')";
            var dwMeta = DwMeta.CreateFromSql( sqlSelect, _dataContext);
            IDataStore ds = DataStore.Create(dwMeta, _dataContext);
            ds.Retrieve();
            int rowCount = ds.RowCount;
            
            _d_sql_info = new DataStore<D_SQL_Info>(_dataContext);

            for (short rowNo = 1; rowNo <= rowCount ; rowNo++)
            {
                _d_sql_info.Add(new D_SQL_Info());
                _d_sql_info.SetItem(nRow - 1, "Column_No", rowNo);
                _d_sql_info.SetItem(nRow - 1, "Column_Name", ds.GetItemString(rowNo - 1, 0));
                _d_sql_info.SetItem(nRow - 1, "Column_Type", ds.GetItemString(rowNo - 1, 1));
                _d_sql_info.SetItem(nRow - 1, "Column_Datatype", ds.GetItemString(rowNo - 1, 2));
                _d_sql_info.SetItem(nRow - 1, "Column_Width", (short) ds.GetItemNumber(rowNo - 1, 3));
                _d_sql_info.SetItem(nRow - 1, "Column_Scale", (short) ds.GetItemNumber(rowNo - 1, 4));
            }    

            return (DataStore)_d_sql_info;
        }

 

Again, Thank you for your responses.  I'm sure I'll back with more questions as I slowly progress.

 

 

 

Comment
There are no comments made yet.
Gary Herrault Accepted Answer Pending Moderation
  1. Friday, 23 September 2022 00:40 AM UTC
  2. .NET DataStore
  3. # 2

Hi Chris,

Let me explain the real issue behind my question in case there is another way reach my goal. But first let me preface that by saying I am new to C# and .net and I am at the beginning stages of creating a Web API for an existing PowerBuilder application.

In my application I make extensive use of dynamically created datastores. I have found an easy enough way to perform the data retrieval for these datastores. But as I am using the sqlca.SyntaxFromSQL method to create the datastore I cannot decouple the application from the database. I would like to find a way around this.

In PoweerBuilder:

	global type uo_datastore from datastore
	
	public function boolean build(string as_sqlselect)
                is_SqlSelect = as_sqlselect
		ls_Syntax = sqlca.SyntaxFromSQL( as_SqlSelect, '', ls_error )
		this.Create( ls_Syntax, ls_error )
	end function

	public funtion long retrieve()
		ls_url = inv_HttpClient.of_get_url('D_Datastore','Retrive') + is_SqlSelect
		li_return = inv_HttpClient.sendrequest("Get", ls_url)
   		inv_HttpClient.getresponsebody(ls_json)
                this.importjson(ls_json)
        end function

In Web API:

	public class D_Datastore
	{
 		public IDataStore Retrieve(String sqlSelect)
		{
			var dwMeta = DwMeta.CreateFromSql(sqlSelect, _dataContext);
			var ds = DataStore.Create(dwMeta, _dataContext);
			ds.Retrieve()
			return ds;
		}
	}

And this works if my application has a connection to the database in order to get the datawindow syntax. But as mentioned I would like to decouple the application from the database entirely.

My first idea, which prompted my question, was to obtain the column names and data types from the web api:

	public IDataStore RetrieveInfo(String sqlSelect)
	{
        	var dwMeta = DwMeta.CreateFromSql(sqlSelect, _dataContext);
	        IDataStore ds = DataStore.Create(dwMeta, _dataContext);
	        _d_sql_info = new DataStore<D_SQL_Info>(_dataContext);

	        int columnCount = int.Parse(ds.Describe("Datawindow.Column.Count"));
        	for (int colNo = 1; colNo <= columnCount ; colNo++)
	        {
        		_d_sql_info.Add(new D_SQL_Info()) ;
	                _d_sql_info.SetItem(colNo - 1, "Column_No", colNo);
        	        string colName = ds.Describe("#" + (colNo - 1) + ".Name");
                	string colType = ds.Describe(colName + ".ColType");
	                _d_sql_info.SetItem(colNo - 1, "Column_Name", colName);
        	        _d_sql_info.SetItem(colNo - 1, "Column_Type", colType);
	         }    

	         return (DataStore)_d_sql_info;
	}

	[DataWindow("d_sql_info", DwStyle.Grid)]
	public class D_SQL_Info
	{
        	[DwColumn("column_no")]
	         public int Column_No { get; set; }

	        [DwColumn("column_name")]
        	public string Column_Name { get; set; }

	        [DwColumn("column_type")]
        	public string Column_Type { get; set; }

	        public static implicit operator D_SQL_Info(DataStore<D_SQL_Info> v)
        	{
	            throw new NotImplementedException();
        	}
	}

...and create the Datawindow syntax from the column info returned. But since this doesn't yield the string length or the decimal precision, is there any other method of creating a datawindow in PowerBuilder with only a Select statement; such that the data types will match what would have been returned by the database and now the web api?  

Thanks again.

 

 

 

 

 

 

Comment
  1. René Ullrich
  2. Friday, 23 September 2022 04:55 AM UTC
You could build the datawindows syntax yourself (that what Syntaxfromsql usual does).

You can find an example in PFC statusbar service.

https://github.com/OpenSourcePFCLibraries/2021/blob/master/ws_objects/pfcwnsrv/pfcwnsrv.pbl.src/pfc_n_cst_winsrv_statusbar.sru (look for function of_createdwobject; line 4529 ff.)
  1. Helpful
  1. Chris Pollach @Appeon
  2. Friday, 23 September 2022 14:08 PM UTC
Hi Gary;

Thank you that great explanation. I can now envisage what you are trying to do. Unfortunately (AFAIK), this would require an enhancement to the Appeon products to allow your scripts to get that level of DWO detail that you are looking for. I would suggest creating a Support Ticket as an enhancement request for the Describe() command to meet your App design needs..

Regards ... Chris
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 22 September 2022 21:40 PM UTC
  2. .NET DataStore
  3. # 3

Hi Gary;

  Unfortunately, the Describe() command does not allow you to get that level of detail.

  The only alternative that I can think of is to read the DWO source (via the LibraryImport command) by your PB App and then parse the entire DWO source code for the column's low level details. Bit of a PIA though.  :-(

Regards ... Chris

Comment
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.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.