ODBC Connection to SQL Server 2005

[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?

[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.

[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

[richh]

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?

[Klaus]

Hi Rich,

...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

[richh]

Hi Rich,

...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.

[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

[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:

Code: Select all

select study_date from T_DATA

it should return:

Code: Select all

YYYY-MM-DD HH:MM:SS

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

[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

[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