DUAL Table - PowerBuilder Conversion - Oracle to SQL Server Migration

A PowerBuilder application for Oracle can contain an embedded SQL statement referencing the dual table that is used very often when you need to get the result of a built-in function, expression, or the next value from a sequence:

PowerBuilder for Oracle:

  DATE ld_date;
  LONG ll_id;
 
  // Get the current datetime from the database
  SELECT SYSDATE INTO :ld_date FROM dual USING sqlca;
 
  // Get the next value from a sequence
  SELECT seq_countries.NEXTVAL INTO :ll_id FROM dual USING sqlca;

In Oracle, when you need to retrieve a single row based on an expression without accessing a table, you can use dual table:

Oracle:

  -- Get the current datetime in format "Month Day, Year" (returns: Jul 16, 2012)
  SELECT TO_CHAR(SYSDATE, 'Mon DD, YYYY') FROM dual;

In SQL Server, you can use SELECT statement without specifying the FROM clause:

SQL Server:

  -- Get the current datetime in format "Month Day, Year" (returns: Jul 16, 2012)
  SELECT CONVERT(VARCHAR, GETDATE(), 107);

Although you can omit the FROM clause in a SQL Server query, PowerBuilder does not allow you to specify an embedded SQL SELECT statement without FROM.

The following code produce a syntax error in PowerBuilder:

PowerBuilder for SQL Server:

  // Raises a syntax error (FROM clause must be specified)
  SELECT GETDATE() INTO :ld_date USING sqlca;

To get around this problem, you either create a dual (or any single row table) in SQL Server, or use dynamic SQL:

PowerBuilder for SQL Server:

   // Declare a cursor
   DECLARE cur DYNAMIC CURSOR FOR sqlca;
   PREPARE sqlca FROM "SELECT GETDATE()";
 
   // Run the query and fetch the result
   OPEN DYNAMIC cur;
   FETCH cur INTO :ld_date; 
 
   CLOSE cur;

• PowerBuilder Conversion from Oracle to SQL Server
• Oracle to SQL Server Migration Knowledge Base

SQLines offers services to convert PowerBuilder applications from Oracle to Microsoft SQL Server. For more information, please Contact Us.