Page 1 of 1
ODBC Connection to SQL Server 2005
Posted: Wed Jan 26, 2011 2:06 am
by richh
I am in the process of evaluating LiveCode ability to connect to SQL Server 2005 with ODBC.
below is my following code:
Code: Select all
on mouseUp
-- connect to the database
put revOpenDatabase("ODBC", "TESTDSN",, "testuser", "password") into tResult
-- check for connection.
if tResult is a number then
put "Connected to the database." & cr & "Connection ID = " & tResult into Field "outputField"
else
put "Connection failed." into Field "outputField"
end if
put revDataFromQuery (tab,cr,tResult, "select distinct patient_id, patient_name, study_date from T_DATA") into tData
revCloseDatabase tResult
end mouseUp
tResult does indeed return a number; however, when the app gets to my query, LiveCode hangs until I have to quit.
thoughts?
Re: ODBC Connection to SQL Server 2005
Posted: Wed Jan 26, 2011 2:53 am
by richh
I see in SQL profiler that the following is being sent over from LiveCode
Code: Select all
declare @p1 int
set @p1=1
exec sp_prepare @p1 output,NULL,N'select distinct patient_id, patient_name, study_date from T_DATA',1
select @p1
if I run that query is SQL Management Studio, I get
Code: Select all
Msg 8179, Level 16, State 2, Procedure sp_prepare, Line 1
Could not find prepared statement with handle 1.
Re: ODBC Connection to SQL Server 2005
Posted: Wed Jan 26, 2011 6:50 am
by bangkok
I use LiveCode (and before RunRev 3.5, 4) to query a MS SQL 2008 server, without any glitch since more than 1 year.
Since your SQL query gives you an error with SQL Management Studio, why don't you try something more simple ?
Like :
select patient_id from T_DATA
Re: ODBC Connection to SQL Server 2005
Posted: Wed Jan 26, 2011 1:51 pm
by richh
bangkok wrote:I use LiveCode (and before RunRev 3.5, 4) to query a MS SQL 2008 server, without any glitch since more than 1 year.
Since your SQL query gives you an error with SQL Management Studio, why don't you try something more simple ?
Like :
select patient_id from T_DATA
I gave that a go and that did work.. I did some more testing and found that nvarchar columns will query fine; however, if I attempt to query datetime then LiveCode freezes.
do I have to handle datetime fields differently when I query from LiveCode?
Re: ODBC Connection to SQL Server 2005
Posted: Wed Jan 26, 2011 2:13 pm
by Klaus
Hi Rich,
richh wrote:...do I have to handle datetime fields differently when I query from LiveCode?
I guess that depends on the format dates are stored on a SQL Server 2005.
E.g. MySQL store dates in this format: YYYY-MM-DD
If this is the case, you could write a little function that converts LiveCode dates to a compatible format before querying.
Best
Klaus
Re: ODBC Connection to SQL Server 2005
Posted: Wed Jan 26, 2011 2:21 pm
by richh
Klaus wrote:Hi Rich,
richh wrote:...do I have to handle datetime fields differently when I query from LiveCode?
I guess that depends on the format dates are stored on a SQL Server 2005.
E.g. MySQL store dates in this format: YYYY-MM-DD
If this is the case, you could write a little function that converts LiveCode dates to a compatible format before querying.
Best
Klaus
Thank you Klaus.
I did go ahead and convert datetime to a char.
this works now:
Code: Select all
put revDataFromQuery(tab,cr, gConnectionID, "select distinct convert(char,study_date,110) as date from T_DATA ") into tData
Its odd though, because by default MS SQL is displaying study_date as YYYY-MM-D HH:MM:SS in which you would think could be queried without the convert statement.
Re: ODBC Connection to SQL Server 2005
Posted: Wed Jan 26, 2011 2:44 pm
by Klaus
Hi Rich,
???
I presumed that you store dates in LiveCode in another format than the database does.
If you DON'T do this, then there is no need to convert unless you need ot add the time 00:00:00.
But maybe I just misunderstand something?
Best
Klaus
Re: ODBC Connection to SQL Server 2005
Posted: Wed Jan 26, 2011 5:20 pm
by richh
in the convert statement I used in my previous post, I was playing around with just converting datetime to a char. Since its a char, then it doesnt really matter what the string looks like since its a string.
in MS SQL, if I do this:
it should return:
if I run the same select statement in LiveCode it freezes which leads me to believe that LiveCode is not able to process a select statement with a datetime column.
do I need HH:MM:SS no, I was simply just trying to understand why LiveCode was not able to query datetime without the need for convert
Re: ODBC Connection to SQL Server 2005
Posted: Wed Jan 26, 2011 5:48 pm
by Klaus
Hi Rich,
ah, I see, looks I really misunderstood this.
But I don't think that LiveCode really freezes when querying DATE entries form any db!
Is is your original SQL syntax: "select study_date from T_DATA"?
All I can say is it SHOULD work
Best from germany
Klaus
Re: ODBC Connection to SQL Server 2005
Posted: Thu Jan 27, 2011 3:41 am
by richh
So, I believe I stumbled upon something.
For the first time today, I decided to run the code on my windows box in which datetime does return fine were as on my mac it doesnt.
definitely odd; however thanks again for your help
