MySql - retrieving calculated field

[GaryVass]

Hi,

I was looking over the example for the mysql usage posted and was trying to determine how to get a field that is calculated on the fly... for example, my sql string is:
strSql= "SELECT sum(amount) As gstDeposit FROM p50380009 where TRANSACT = 'DEPOSIT' or TRANSACT = 'CASH RECEIPTS';"
' rslt = Query.Run(strSql)
sRecordsId = Records.Open(gsDatabase, strSql)

when the rslt line is un-commented, it returns TRUE, so I know the sql string works. But, I never get a sRecordsId.

NOTE: gstDeposit does not exist in the table, that is, there is no field named gstDeposit, mysql calculates it when the query is run.


When I run the query directly in mysql (via ssh), I do get a result for gstDeposit. How do I get that value to be passed back through kbasic?

[Henning]

Run doesn't create a recordset. Use only for NoRecReturned queries.

If sDatabase Then
strSQL = "SELECT sum(amount) As gstDeposit FROM p50380009 where TRANSACT = 'DEPOSIT' or TRANSACT = 'CASH RECEIPTS';"
sRecordsId = Records.Open(sDatabase, strSQL)
If sRecordsId Then
If Records.First(sRecordsId) Then
' recCount = Records.Length(sRecordsId)
Somevar = Records.Value(sRecordsId, "gstDeposit")

/Henning

[GaryVass]

Thank Henning,

I only put the query statement in to see if it was a valid sequel statement. With the query statement commented out, I received not data. There is no sRecordsId returned....

I copied your code and the result is the same, however, I do get a "true" result when I try the sql statement with the query command.... any other ideas?

[Henning]

Q:

You *did* change sDatabase and sRecordsId to *youre* names?
You do use the correct code to connect to and open the db?

A MsgBox here and there might help to see where it fails.

/Henning

[GaryVass]

Yep, I caught the slight spelling differences and did change them.

I believe I am using the correct code to connect (I copied it from my main program that does connect to the same database).... I am using (PW and user name have been changed):
Public Sub DBConnect()


Driver = "QMYSQL"
Host = "192.168.123.201"
Port = "3306"
gsDatabase ="data"
'dbName ="buylist"
User = "XXXXX"
Password = "XXXX"
Options = ""

If Database.Open(Driver, gsDatabase, User, Password, Host, Options, Port) Then
strSql = "SELECT * FROM MANAGER;"
sRecordsId = Records.Open(gsDatabase, strSQL)
If gsDatabase Then
If sRecordsId Then
If Records.First(sRecordsId) Then
gsDataDir = Records.Value(sRecordsId, "datadir")
Else
MsgBox "Records Error " & Database.LastSQLError(gsDatabase)
End If
Else
MsgBox Database.LastSQLError(gsDatabase)
End If
Records.Close(sRecordsId)
End If
Else
MsgBox "Open=False"
End If
End Sub

[Henning]

Snippet tested in my app

Code: Select all

      strSQL = "SELECT SUM(antut) AS sumantut FROM ArtReg WHERE ArtStat = " & anr
      sRecordsId = Records.Open(sDatabase,strSQL)
      If Records.First(sRecordsId) Then
        MsgBox("SUM AntUt = " & Records.Value(sRecordsId,"sumantut"))


/Henning

[GaryVass]

Thanks Henning... I finally got it to work.