Recent

Author Topic: Help silly girl  (Read 11073 times)

Elena

  • New member
  • *
  • Posts: 8
Help silly girl
« on: July 07, 2011, 04:28:57 pm »
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]

geno

  • Full Member
  • ***
  • Posts: 198
Re: Help silly girl
« Reply #1 on: July 07, 2011, 05:25:46 pm »
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..
version:      Lazarus 1.9   FPC 3.0.4
   
widget set:  x-86_64-linux-gtk 2
OS:             ArchLinux/ Xfce 4.12

Elena

  • New member
  • *
  • Posts: 8
Re: Help silly girl
« Reply #2 on: July 07, 2011, 06:28:29 pm »
'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.

geno

  • Full Member
  • ***
  • Posts: 198
Re: Help silly girl
« Reply #3 on: July 07, 2011, 08:12:57 pm »
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??
version:      Lazarus 1.9   FPC 3.0.4
   
widget set:  x-86_64-linux-gtk 2
OS:             ArchLinux/ Xfce 4.12

Elena

  • New member
  • *
  • Posts: 8
Re: Help silly girl
« Reply #4 on: July 07, 2011, 10:40:16 pm »
None of this helps (((

Lacak2

  • Guest
Re: Help silly girl
« Reply #5 on: July 08, 2011, 06:44:32 am »
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

  • New member
  • *
  • Posts: 8
Re: Help silly girl
« Reply #6 on: July 08, 2011, 12:28:43 pm »
Explain please how to fix?
I corrected the file sqldb.pp
But as it compiled?

Lacak2

  • Guest
Re: Help silly girl
« Reply #7 on: July 08, 2011, 12:37:50 pm »
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  :D

Elena

  • New member
  • *
  • Posts: 8
Re: Help silly girl
« Reply #8 on: July 08, 2011, 02:17:00 pm »
Thank you very much! Everything worked as it should. To me, without you doing boys?)))
« Last Edit: July 08, 2011, 02:34:03 pm by Elena »

geno

  • Full Member
  • ***
  • Posts: 198
Re: Help silly girl
« Reply #9 on: July 08, 2011, 05:02:03 pm »
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 :D

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
version:      Lazarus 1.9   FPC 3.0.4
   
widget set:  x-86_64-linux-gtk 2
OS:             ArchLinux/ Xfce 4.12

Elena

  • New member
  • *
  • Posts: 8
Re: Help silly girl
« Reply #10 on: July 09, 2011, 02:33:06 pm »
I would like to add that to compile in Linux should be a directory with the project file to add odbccon.pas.