[Gambas-user] Problem with row count in mysql

[nando]

My two cents worth: I see a few problems:
1) There is an erroneous END just above PRINT iData near the end of your example or 
   something is out of order(?)
2) iData is a Result data type and not an INTEGER...and iData doesn't have the row counts
   but iData.count is an INTEGER
3) Internally MySQL has two different types of ROW counts depending on:
   [a] the number of rows resulting by a SELECT or SHOW command.
   [b] the number of rows affected  by a INSERT, UPDATE, REPLACE or DELETE command.

Programs must:
   First do the command (SELECT, INSERT..whatever...)
    - Then-
   Do another separate command to get the rows resulting or affected 
   by the type of command performed (type [a] or [b])

To help in understanding what is happening visit:
http://dev.mysql.com/doc/refman/5.1/en/mysql-affected-rows.html
http://dev.mysql.com/doc/refman/5.1/en/information-functions.html
   (look at 'found-rows' and 'row-count' functions)

Try this:

PRINT iData.count   'should report correctly for SELECT and SHOW

I don't recall right now about getting the affected rows
from INSERT, DELETE, REPLACE commands.

-Fernando



---------- Original Message -----------
From: ronald at ...2810...
To: gambas-user at lists.sourceforge.net
Sent: Thu, 5 Apr 2012 21:17:52 -0400
Subject: [Gambas-user] Problem with row count in mysql

> I am having trouble getting a row count using mysql.  Here is some sample
> code I have used.  All else works and I can connect to the database and
> table.
> There are 4 rows in the Table called Addresses.  Running this program
> shows iData and 'a' as having 0 rows.  Please advise.
> 
> Thank you
> Ron
> 
> Example code:
> 
> ' Gambas class file
> STATIC PUBLIC a AS Integer
> 
> PUBLIC SUB Form_Open()
> 
>   ME.Center
>   ME.Height = 315
>   $hConn = NEW Connection
> 
> END
> 
> PRIVATE $hConn AS Connection
> 
> PUBLIC SUB btnConnect_Click()
> 
> DIM sName AS String
> DIM hTable AS Table
> DIM iData AS Result
> 
>   TRY $hConn.Close
>   '$hConn = NEW Connection
> 
>   sName = "AddressBook"
> 
>   WITH $hConn
>     .Type = "mysql"
>     .Host = "localhost"
>     .Login = "root"
>     .Password = "charlie12"
>     .Name = ""
>   END WITH
> 
>   'IF chkCreate.Value THEN
> 
>     $hConn.Open
>     IF NOT $hConn.Databases.Exist(sName) THEN
>       $hConn.Databases.Add(sName)
>     ENDIF
>     $hConn.Close
> 
>   $hConn.Name = sName
>   $hConn.Open
>   TextLabel1.text = "AddressBook Opened for Addresses"
> 
>  IF NOT $hConn.Tables.Exist("Addresses") THEN
>   hTable = $hConn.Tables.Add("Addresses")
> 
>   hTable.Fields.Add("id", db.Long)
>   hTable.Fields.Add("First_Name", db.String)
>   'hTable.Fields.Add("Mi", gb.String)
>   'hTable.Fields.Add("Last_Name", gb.String)
>   'hTable.Fields.Add("Address1", gb.String)
>   'hTable.Fields.Add("Address2", gb.String)
>   'hTable.Fields.Add("City", gb.String)
>   'hTable.Fields.Add("State", gb.String)
>   'hTable.Fields.Add("Zip_Code", gb.String)
>   'hTable.Fields.Add("Home_Phone", gb.String)
>   'hTable.Fields.Add("Cell_Phone", gb.String)
>   'hTable.Fields.Add("Email_Address", gb.String)
> 
>   hTable.PrimaryKey = ["id"]
> 
>   hTable.Update
>   ELSE
> 
> ENDIF
> 
> $hConn.Exec("use AddressBook;")
>   iData = $hConn.Exec("select count(*) from Addresses;")
> 
> PRINT iData
> a = iData
> PRINT a
> 
> END
> 
> PUBLIC SUB btnClose_Click()
> 
>   $hConn.Close
>   TextLabel1.Text = "AddressBook Closed"
>   WAIT 4.0
>   ME.Close
> 
> END
> 
> PUBLIC SUB btnAddSddress_Click()
> 
>  ME.Height = 770
> 
> END
> 
> PUBLIC SUB btnSave_Click()
> DIM rdata AS Result
> txtRequest.Text = "insert into Addresses values('" & a & "','" &
> txtFirstName.Text & "');" '& "(" & "'" & a & "'," & "'" &
> txtFirstName.Text & "');"
> rdata = $hConn.Exec(txtRequest.Text)
>   INC a
>   PRINT a
> 
>   END
> 
> PRINT idata
> a = iData
> PRINT a
> 
> END
> 
> ------------------------------------------------------------------------------
> For Developers, A Lot Can Happen In A Second.
> Boundary is the first to Know...and Tell You.
> Monitor Your Applications in Ultra-Fine Resolution. Try it FREE!
> http://p.sf.net/sfu/Boundary-d2dvs2
> _______________________________________________
> Gambas-user mailing list
> Gambas-user at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/gambas-user
------- End of Original Message -------