Creating desktop or client-server database solutions?
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
-
richh
- Posts: 41
- Joined: Tue Jan 25, 2011 8:48 pm
Post
by richh » Wed Jan 26, 2011 2:06 am
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?
- Rich
LiveCode 4.6.4
Dell Latitude E6400 running Windows XP SP3 / Mac Pro, Macbook Pro & Mac Mini running OS X 10.6.4
-
richh
- Posts: 41
- Joined: Tue Jan 25, 2011 8:48 pm
Post
by richh » Wed Jan 26, 2011 2:53 am
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.
- Rich
LiveCode 4.6.4
Dell Latitude E6400 running Windows XP SP3 / Mac Pro, Macbook Pro & Mac Mini running OS X 10.6.4
-
bangkok
- VIP Livecode Opensource Backer

- Posts: 937
- Joined: Fri Aug 15, 2008 7:15 am
Post
by bangkok » Wed Jan 26, 2011 6:50 am
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
- Posts: 41
- Joined: Tue Jan 25, 2011 8:48 pm
Post
by richh » Wed Jan 26, 2011 1:51 pm
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?
- Rich
LiveCode 4.6.4
Dell Latitude E6400 running Windows XP SP3 / Mac Pro, Macbook Pro & Mac Mini running OS X 10.6.4
-
Klaus
- Posts: 14206
- Joined: Sat Apr 08, 2006 8:41 am
-
Contact:
Post
by Klaus » Wed Jan 26, 2011 2:13 pm
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
-
richh
- Posts: 41
- Joined: Tue Jan 25, 2011 8:48 pm
Post
by richh » Wed Jan 26, 2011 2:21 pm
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.
- Rich
LiveCode 4.6.4
Dell Latitude E6400 running Windows XP SP3 / Mac Pro, Macbook Pro & Mac Mini running OS X 10.6.4
-
Klaus
- Posts: 14206
- Joined: Sat Apr 08, 2006 8:41 am
-
Contact:
Post
by Klaus » Wed Jan 26, 2011 2:44 pm
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
- Posts: 41
- Joined: Tue Jan 25, 2011 8:48 pm
Post
by richh » Wed Jan 26, 2011 5:20 pm
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

- Rich
LiveCode 4.6.4
Dell Latitude E6400 running Windows XP SP3 / Mac Pro, Macbook Pro & Mac Mini running OS X 10.6.4
-
Klaus
- Posts: 14206
- Joined: Sat Apr 08, 2006 8:41 am
-
Contact:
Post
by Klaus » Wed Jan 26, 2011 5:48 pm
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
- Posts: 41
- Joined: Tue Jan 25, 2011 8:48 pm
Post
by richh » Thu Jan 27, 2011 3:41 am
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

- Rich
LiveCode 4.6.4
Dell Latitude E6400 running Windows XP SP3 / Mac Pro, Macbook Pro & Mac Mini running OS X 10.6.4