MSSQL SQL Server library

[agraham]

This library, a tidied up version of http://www.basic4ppc.com/forum/addit...l-library.html allows access to SQL Server databases.

A rudimentary help file is included that describes the available methods and properties but it is expected that you already know what do do in order to connect to a SQL Server instance because I don't! However you do need System.Data.SqlClient.Dll from post #8 of this thread on your device.

EDIT :- Version 1.7 posted. See post #8 for details.

EDIT :- Version 1.8 posted. See post #10 for details.

EDIT :- Version 1.9 posted. See post #13 for details.

EDIT :- Version 2.0 posted. See post #32 for details.

[georgea]

Great job Andrew! and nice documentation.

Thank you.

[georgea]

Hi, i am back just to ask something.

Is there any chance to add a property (String or Number) e.g. "RowCount", to know the rows that the query returned before reading the data ?

I don't know if function ReadRemainingRows is for this use, but i can't work with it.

Thanks.

[agraham]

Originally Posted by georgea

Is there any chance to add a property (String or Number) e.g. "RowCount", to know the rows that the query returned before reading the data ?

I looked to do that but there doesn't seem to be any way of finding that out without reading to the end of the table. I think it is the same with SQLite.

I don't know if function ReadRemainingRows is for this use, but i can't work with it.

It's a convenience to read the entire table at once and the length of the returned array will tell you how many rows. Why can't you use it?

[georgea]

Sometimes you may need to know how many rows the query returns, before you read the query. So, if you read all the data till the end of the table just to count the rows then if you re-read the Mssql.Advance it returns False and you can't read it.

As for the ReadRemainingRows, i tryied to test it but it always returns ArrayLen = 1 and when i try to read it, it seems to be empty or null.

One (dump) solution is before the main query you plan to execute, to execute a "Select Count({field}) As Rowcount From {table}" and then ReadField("Rowcount") to know how many rows you 'll get. I know that you have to execute 2 queries everytime but i see it as the only way.

[agraham]

Originally Posted by georgea

As for the ReadRemainingRows, i tryied to test it but it always returns ArrayLen = 1 and when i try to read it, it seems to be empty or null.

There's probably a bug in it then . What does LastError return after you have executed ReadRemainingRows? The array should also contain the error string at index (0,0).

[georgea]

The following is the test code which returns me normally 8 rows:

mssql1.ExecuteQuery("Select nickname From Employees")
array2() = Mssql1.ReadRemainingRows
Msgbox(ArrayLen(array2()))
Msgbox(array2(0,0))

The first MsgBox returns 1 and the second MsgBox i get System.IndexOutOfRangeException: Index Out Of Bounds.MSSQL.MSSQL.ReadRemainingRows()

Also, after ReadRemaingRows the Mssql.Advance returns always False and i can't use ReadField().

[agraham]

Version 1.7 now posted which (hopefully) fixes the the bug in ReadRemainingRows.

[georgea]

Goodmorning.

I tested Version 1.7 and i noticed that RemainingRows now return as ArrayLen the number of rows retrieved + 1. However the main issue is that after executing RemainingRows, the Advance method returns false and you can't read the data. Also, Array(0,0) returns the same error but this is not an issue.

Thank you.

[agraham]

Originally Posted by georgea

RemainingRows now return as ArrayLen the number of rows retrieved + 1.

There was another bug that I missed in ReadRemainingRows which was using a wrong property to size the array. Corrected in version 1.8.

However the main issue is that after executing ReadRemainingRows, the Advance method returns false and you can't read the data.

That is correct behaviour. It is the same as you doing multiple ReadRows. The data is in the two dimensional array that is returned by ReadRemainingRows. You can use ReadRemainingRows immediately after ExecuteQuery to get the whole table or use Advance and ReadRow to get the data one row at a time. Using ReadRemainingRows after using Advance and ReadRow one or more times will return only the data in the unread part of the table. It seems to be common to SQL databases that tables returned from queries can only be read once in a forward direction.

Also, Array(0,0) returns the same error but this is not an issue.

I don't understand this unless you are trying to execute ReadRemainingRows again in which case it is expected behaviour as the table has been read to the end.