Help silly girl
lazarus / 16-Databases

----------------------------------------------------------------------
Elena · 2011-07-07 14:28

ODBC and MSSQL
Returns an incorrect number of rows is always 1
In this example should return 8 rows
What am I wrong??
Code: [Select]SQLQueryMain.SQL.Text: = UTF8ToAnsi ('execute
jms_address_get le');
SQLQueryMain.Open;
Memo1.Lines.Add (inttostr (SQLQueryMain.RecordCount)); <--- 1??
SQLQueryMain.Close;
[/ code]

----------------------------------------------------------------------
Dick, from the internet · 2011-07-07 15:25

Assuming MSSQL is similar to MYSQL,  you are attempting to execute a
prepared statement which has been defined within your database passing
a parameter of 'le'.  Without knowing more about the prepared
statement, there really is no way to know what it is supposed to
return, but I am guessing it may just "return all addresses starting
with 'le'"
First try :

SQLQueryMain.Last;  //move to the last record in the recordset
SQLQueryMain.First; //move back to the first record
Memo1.Lines.Add (inttostr (SQLQueryMain.RecordCount));   //now try it

if you are trying to list all rows in your database, just as a quick
check:

SQLQueryMain.SQL.Text: = UTF8ToAnsi ('select * FROM your_table_name');
SQLQueryMain.Last;  //move to the last record in the recordset
SQLQueryMain.First; //move back to the first record
Memo1.Lines.Add (inttostr (SQLQueryMain.RecordCount));   //now try it

hope this helps..

----------------------------------------------------------------------
Elena · 2011-07-07 16:28

'execute jms_address_get le' is a stored function on the server which
returns upon request.

I tried to Next:

Code: [Select]SQLQueryMain.SQL.Text: = UTF8ToAnsi ('execute
jms_address_get le');
SQLQueryMain.Open;
SQLQueryMain.Last;
SQLQueryMain.First;
Memo1.Lines.Add (inttostr (SQLQueryMain.RecordCount)); <--- 1  :(((
SQLQueryMain.Close;
Queries 'select * FROM your_table_name' works correctly.

I can not call a stored function correctly MSSQL?

In MS SQL Server Management Studio query 'execute jms_address_get le'
is performed correctly.

----------------------------------------------------------------------
Dick, from the internet · 2011-07-07 18:12

I have been doing some reading/experimenting with the prepared
statements.  One of the issues I found is in the scope of visibility
of the statements.  From the MySQL 5.1 reference manual
http://dev.mysql.com/doc/refman/5.1/en/prepare.html, chapter 12.6.1
"The scope of a prepared statement is the session within which it is
created. Other sessions cannot see it. "

To test this, I logged into mysql and created a simple prepared
statement and executed it.  It returned as expected.  I then created a
simple app in lazarus to execute the prepared statement and received
an error "Unknown prepared statement handler given to EXECUTE"

I then used the .execSQL function to create a prepared statement from
my app:

query.SQL.Text := 'prepare get_more_names From ''select AlbumName from
Albums''';
query.ExecSQL;

The statement executes, (however I am unable to return a result as
expected - I need to do more testing); but the prepared statement was
found and executed as should be.  I then went back to the mysql
command prompt to execute get_more_names and received 'Unknown
prepared  statement handler' error.

I am not sure if I am on the right track - if this is totally off base
from your issue, but as I see it, this could be a part of the
problem.  I know there are some members out there with way more smarts
on this than I -- anybody else care to help out??

----------------------------------------------------------------------
Elena · 2011-07-07 20:40

None of this helps (((

----------------------------------------------------------------------
Lacak2 · 2011-07-08 04:44

Elena, there is bug or missing feature in FPC.
Only 1st row from stored procedures is always returned.
See http://bugs.freepascal.org/view.php?id=16236
Bug report is related to MySQL, but same applies to other SQL DB
connectors (so also to TODBCConnection).
Fix for your problem is very easy, see sqldb_call.diff in bug report.
(so you can apply this fix and test if it helps)

----------------------------------------------------------------------
Elena · 2011-07-08 10:28

Explain please how to fix?
I corrected the file sqldb.pp
But as it compiled?

----------------------------------------------------------------------
Lacak2 · 2011-07-08 10:37

Either put in your project Options in Search Path path to sqldb.pp or
copy sqldb.pp into your project directory.
May be, that compiler will prompt also other files as checksum od
sqldb.pp changed ... so you must pont to all files

----------------------------------------------------------------------
Elena · 2011-07-08 12:17

Thank you very much! Everything worked as it should. To me, without
you doing boys?)))

----------------------------------------------------------------------
Dick, from the internet · 2011-07-08 15:02

Thanks Lacak2 - seems I was wandering around out in left field
somewhere.  I just  never think  to look for bugs in the sources, I
always expect that I'm the one creating the bugs

I'm glad that he got you back on track Elena - I knew there was
someone with a fix for ya'.  Good luck and happy coding!!

geno

----------------------------------------------------------------------
Elena · 2011-07-09 12:33

I would like to add that to compile in Linux should be a directory
with the project file to add odbccon.pas.