SQL result question

[eblaschka]

Hi,
new to SQL with Alpha:

I am doing an "easy by the book" query:

dim conn as SQL::Connection
dim rs as SQL::ResultSet
dim connString as C
dim select_exp as C
dim arr as P
dim Result_Flag as L

connString = "{A5API='SQLServer',A5DBVersion=2005,Server='ERIBLA-3D\PDATA_SQLEXPRESS',A5ANSINullPadWarn=Default,UserName='sx',Password='sna',Database='PDATA_SQLEXPRESS'}"
select_exp = "SELECT tCfgSSet from TConfigRaw WHERE tCfgSKey = 'MasterDataPath'"

if .not. conn.open(connString)
ui_msg_box("Error", conn.CallResult.text)
end

end if

if .not. conn.execute(select_exp)
ui_msg_box("Error", conn.CallResult.text)
conn.close()
end
end if

rs = conn.ResultSet

Result_Flag=rs.ToPropertyArray(arr)

conn.close()

ui_msg_box("Results", arr.dump_properties("Result"))


Everything runs great, ResultFlag is .T. , but the message box falls on it's face: "Array Dump_Properties not found"

I'm not sure if this is because I do not have a result (I believe):

rs.RowCount =-1


On the other hand if I run the identical query on the server I do get one returned result.


I'm not sure what I am doing wrong.

Thanks for any help

[eblaschka]

Re: SQL result question

Clarification:
If I run the query on the server with my Microsoft SQL tools (not Alpha)
I get a result.

[eblaschka]

Re: SQL result question

I am a bit further down the long road:

I actually do have results from my Query. (tested with var=rs.Data(1)).

rs.RowCount though is always "-1" - who knows why..

The issue is the "Dump_Properties" function.



Still would like to get the results into a grid, browse, array, table...

[Peter.Wayne]

Re: SQL result question

Is there a column in the table named "Result"? You are attempting to print the value of a column "result" from your table.

[eblaschka]

Re: SQL result question

Hi Peter,

Thanks, I changed this, but no difference.


I found a different method though and this works fine:

conn.ToPropertyArray(query, args, array, 100, 0)



I am still puzzled that rs.rowcount is always -1, even if there are results.


Thanks again

[Peter.Wayne]

Re: SQL result question

The rowCount doesn't always work for all databases, and I don't use it (I use MySQL as a backend and I don't think it works for MySQL either).
I usually code with this approach:

rs=conn.resultSet
while rs.NextRow()
  result=rs.data("tCfgSset")
  ' now do whatever you want to do with this result
end while

If you expect only one row to match your query, you can do

rs=conn.resultSet
if rs.nextRow() then
  result=rs.data("tCfgSSet")
  ' do something with result
else
  ui_msg_box("Error","No matches for your query.")
end if

[eblaschka]

Re: SQL result question

Thanks Peter

[kyleim]

Re: SQL result question

This thread saved my life.... I was using a for loop and bugging out when the result set had 0 records. Couldn't figure out the right condition to use for the while loop. Now, I smile, while using a while loop, all the while getting no errors. Well Done Peter.Wayne!