SAP SQL Anywhere

Question

Long time no post!

Our application has two parts, a server application that runs centrally & uses SQL Server as its database backend, and the client part, uses SQL Anywhere as an embedded database. As such, the only tool installed with our product from which we can execute ad-hoc scripts for correcting & diagnosing customer issues is DBISQLC.EXE. The product uses our proprietary technology for replicating & synchronizing data between the server & the client.

We recently came across a problem where data that is on the client was out of sync with the server. In order to correct the problem, I had to determine which record on the client was the problem one, which involved opening remote sessions with the server & the client, running a query in SSMS on the server, and running another query in DBISQLC.EXE on the client and painstakingly comparing the results. This was a very time consuming and error prone process. There has to be a better way.

I have been able to write a T-SQL script that runs in SSMS, whose output is a script that can run in DBISQLC on the client & which identifies the problem row(s). What I really need the client script to do is go one step further and output either an entire T-SQL script that can be run in SSMS on the server to correct the problem, or, as an acceptable but less desirable alternative, to output data that can be copy & pasted into a T-SQL script in SSMS.

In the interests of making sure enough information is available, what the script I've written does is execute a sequence of PRINT statements. These statements output the text of a script that creates a stored procedure in SQL Anywhere, executes it, and then drops it.

The stored procedure creates a temporary table with two columns & loads data into it. The data is loaded into the temporary table by SQL like this:

PRINT 'INSERT INTO TempTable'
PRINT '    ( Column1, Column2 )'
PRINT 'VALUES'

DECLARE @noOutput int;
DECLARE GET_DATA CURSOR FOR
    SELECT Column1, Column2
    FROM TableName

OPEN GET_DATA

FETCH NEXT FROM GET_DATA INTO @Column1, @Column2

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Line = '        ( ' + '''' + @Column1 + ''', ' + '''' + @Column2 + ''' )'
    SET @noOutput += 1
    IF ( @noOutput = @noRows )
        PRINT @Line + ';'
ELSE
        PRINT @Line + ','

    FETCH NEXT FROM GET_Data INTO @Column1, @Column2
END

CLOSE GET_DATA
DEALLOCATE GET_DATA

The stored procedure that's built by this process compares the contents of the temporary table to the contents of the table with the problem and determines how many problem rows are in that table. What I'd like it to do is use code similar to that above to output statements for loading data into a temporary table on the SQL Server database that can be used by a script to actually fix the problem. The problems are that:

You can't copy the results of a query to the clipboard in dbisqlc.exe
Even if I use MESSAGE statements to output the desired script and run it in the DBISQL tool, none of my output shows up anywhere.

So how can I get the results I'm looking for?

Accepted Answer

FWIW, here's a different approach to generate a SQL DML statement based on a SELECT UNION over sample data (here an excerpt from SYSTAB). As the complete query is build by one SELECT statement, you don't have to mess around with several PRINT/MESSAGE statements - you just have to export the query (with according delimiters, apparently).

It uses

a first query block to build the statement header (INSERT INTO ... VALUES ...)
a second query block to list the actual rows
within that block, a row_number() call to number the rows and to test whether the last row is fetched to append ',' vs. ';'
a derived query to omit the line number in the query's output.

select txt from
(select 0 as line_no, 'Insert into MyTable ( Column1, Column2 ) values ' as txt
 union all
 select row_number() over (order by table_name) as row_nr,
    '('''  || table_id || ''', ''' || table_name || ''')'
       || if row_nr = count(*) over () then ';' else ',' end if
 from systab
 where table_name like 'ix_%'
 order by 1) dt
order by line_no;

The result can be written to a file via

output to 'c:\\MyExport.sql' format text quote '';

and returns a file with the following contents:

Insert into MyTable ( Column1, Column2 ) values 
('669', 'ix_consultant_affected_columns'),
('667', 'ix_consultant_index'),
('668', 'ix_consultant_ixcol'),
('670', 'ix_consultant_log'),
('663', 'ix_consultant_master'),
('666', 'ix_consultant_query_index'),
('665', 'ix_consultant_query_phase'),
('664', 'ix_consultant_query_text');

With some more delimiters/blanks, that may create the SQL statement of your choice... Even adding comments via separate UNIONed query blocks is quite easy.

SAP SQL Anywhere

How to export a script from DBISQLC

Follow this question

Related questions