Re: Running a procedural query ?

The issue is that CLSQL currently only supports getting the first result 
set (of the first statement). I'm not sure whether this is an issue with 
the ODBC layer or all the layers (probably all of them though).  While 
problematic, this can usually be worked around by declaring and calling 
a stored procedure instead.

Similarly the following returns 2 only
(clsql:query "SELECT 2; SELECT 3;") => ((2))

My limited understanding is that it would take significant effort to 
support multiple result sets (or even selecting the last result set 
instead of the first).  I hope this helps explain your results, and 
points you to a solution.

Cheers,
Russ Tyndall
Software Developer
Acceleration.net


On 3/6/2013 12:45 PM, Brian Sorg wrote:
> I am trying to run a query through ODBC on a Mssql Server 2008 
> database. The query runs on the database as expected, however it is 
> support to return a single row of results which vary depending on the 
> results of the query. I have tested the sql query directly against the 
> database and it runs as expected, however with running it using 
> clsql:query it only returns the number 1 instead of the expected list 
> of results. Is there a step I am missing with my call? The following 
> is an example of what I am trying to run:
>
> ---------------------------------------------------------------
> (clsql:with-database (*default-database* *mssql-connect-writer-spec* 
> :database-type :odbc
>                                          :if-exists :new :make-default 
> nil)
>   (clsql:set-autocommit nil)
>   (clsql:query "
> DECLARE @batchID INT
> DECLARE @batchIDNext INT
> DECLARE @result INT
> DECLARE @errorMessage VARCHAR(255)
>
>
> -- Start the transaction process here
> BEGIN TRANSACTION
>
> -- Try attempts to run the code inside of the try, if it fails it is 
> caught by the CATCH, the CATCH must immediately follow the TRY
> BEGIN TRY
>         SELECT @batchID = next_value from autoincrement a where 
> a.company_id = 'TMS' and a.tablename = 'cash_batch' and a.fieldname = 'id'
>
>         SELECT @batchIDNext = (@batchID + 1)
>
>         update autoincrement set next_value=@batchIDNext where 
> company_id='TMS' and tablename='cash_batch' and fieldname='id' and 
> next_value=@batchID
>
>         INSERT INTO cash_batch(company_id, amount, amount_c, amount_d, 
> amount_n, amount_r, glid, id, receipt_date)
>                        values 
> ('TMS','hi','USD',null,50.00,1.0000,'2015000000',@batchID,'2013-03-05 
> 12:31:45')
>
> END TRY
> BEGIN CATCH
>         -- TRANCOUNT checks how many transaction steps are currently 
> uncommitted, so if there was an error roll them back which sets 
> @@TRANCOUNT to zero
>         IF @@TRANCOUNT > 0
>            BEGIN
>               ROLLBACK TRANSACTION;
>               SELECT @result = -1;
>               SELECT @errorMessage = ERROR_MESSAGE();
>            END
>
> END CATCH
>
> -- If there were errors @@TRANCOUNT will be rolled back to zero, 
> Otherwise commit and return the batch id
> IF @@TRANCOUNT > 0
>     BEGIN
>         COMMIT TRANSACTION;
>         SELECT @result = @batchID;
>         SELECT @errorMessage = 'ok';
>     END
>
> SELECT @result as resultid, @errorMessage as message;"
>    :result-types '(:int :string)))
>
> -------------------------------------------------------------
>
> Thanks,
>
> -- 
> Brian Sorg
> Director and Founder Liberating Insight LLC
> [email protected] <mailto:[email protected]>
> Office: 260.918.0490
> Mobile: 260.602.1086
> www.liberatinginsight.com <http://www.liberatinginsight.com>
>
>
> _______________________________________________
> CLSQL mailing list
> [email protected]
> http://lists.b9.com/cgi-bin/mailman/listinfo/clsql

_______________________________________________
CLSQL mailing list
[email protected]
http://lists.b9.com/cgi-bin/mailman/listinfo/clsql