MLDatabase command works on MLServer, not on client

MLDatabase allows MLServer3 to interact with any ODBC database that you create or attach to. Intended for advanced users only.
Droezel
Member
Member
Posts: 75
Joined: Fri Aug 03, 2007 2:35 am
Contact:

MLDatabase command works on MLServer, not on client

Postby Droezel » Tue Sep 02, 2008 4:01 am

Hello all,

I've got this strange problem. I have a command map that runs the following query:

Code: Select all

MLServeCmd.MLDatabase|Process_Command~MyMovies~SELECT Stuff((Select ', ' + A.nvcName FROM tblActors A LEFT JOIN tblTitleActor T ON A.intId = T.intActor WHERE intTitle = {{MLDB_MyMovies_{{clientname}}_ActiveID}} ORDER BY T.intSort FOR XML PATH('')),1,2,'') AS ActiveActors~MLDB_MyMovies_{{clientname}}


When I test this query with the "test command" button (off course replacing {{clientname}}) it works perfectly.

When I call this query from a client in a macro it gives me the error:

Error -2147217900([Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'AS'.) @ Line 1170 in procedure doCmd of frmMain


The query gets the actors for a selected movie out of the Windows Media Center plugin My Movies database in one field. Eg: MLDB_MyMovies_myclient1_ActiveActors = "Actor1, Actor2, Actor 3, ..., Actor N".

Any idea why i get this behaviour?

90CE
Developer
Developer
Posts: 573
Joined: Mon Nov 28, 2005 1:19 am
Location: Waukee, Iowa
Contact:

Postby 90CE » Tue Sep 02, 2008 4:38 pm

Can you turn on verbose logging in MLServer and MLDatabase and see what command is actually being passed to the plugin?

thanks
Tracy

Droezel
Member
Member
Posts: 75
Joined: Fri Aug 03, 2007 2:35 am
Contact:

Postby Droezel » Tue Sep 02, 2008 4:59 pm

When ran from a client:

Code: Select all

2/09/2008 23:45:52   Processing (1)   myClient2   MyMovies.GetActiveMovieActors
2/09/2008 23:45:52   Processing (2)   myClient2   MLServeCmd.MLDatabase|Process_Command~MyMovies~SELECT Stuff((Select ', ' + A.nvcName FROM tblActors A LEFT JOIN tblTitleActor T ON A.intId = T.intActor WHERE intTitle = {{MLDB_MyMovies_{{clientname}}_ActiveID}} ORDER BY T.intSort FOR XML PATH(')),1,2,') A
2/09/2008 23:45:52   Subst In   myClient2   Process_Command~MyMovies~SELECT Stuff((Select ', ' + A.nvcName FROM tblActors A LEFT JOIN tblTitleActor T ON A.intId = T.intActor WHERE intTitle = {{MLDB_MyMovies_{{clientname}}_ActiveID}} ORDER BY T.intSort FOR XML PATH(')),1,2,') AS ActiveActors~MLDB_My
2/09/2008 23:45:52   Subst Out   myClient2   Process_Command~MyMovies~SELECT Stuff((Select ', ' + A.nvcName FROM tblActors A LEFT JOIN tblTitleActor T ON A.intId = T.intActor WHERE intTitle = 678 ORDER BY T.intSort FOR XML PATH(')),1,2,') AS ActiveActors~MLDB_MyMovies_myClient2
2/09/2008 23:45:52   MLDatabase Error   Server   Error -2147217900([Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'AS'.) @ Line 1170 in procedure doCmd of frmMain


When ran in MLServer (test command button)

Code: Select all

2/09/2008 23:53:54   Processing (1)      MLServeCmd.MLDatabase|Process_Command~MyMovies~SELECT Stuff((Select ', ' + A.nvcName FROM tblActors A LEFT JOIN tblTitleActor T ON A.intId = T.intActor WHERE intTitle = {{MLDB_MyMovies_myClient2_ActiveID}} ORDER BY T.intSort FOR XML PATH('')),1,2,'') AS A
2/09/2008 23:53:54   Subst In      Process_Command~MyMovies~SELECT Stuff((Select ', ' + A.nvcName FROM tblActors A LEFT JOIN tblTitleActor T ON A.intId = T.intActor WHERE intTitle = {{MLDB_MyMovies_myClient2_ActiveID}} ORDER BY T.intSort FOR XML PATH('')),1,2,'') AS ActiveActors~MLDB_MyMov
2/09/2008 23:53:54   Subst Out      Process_Command~MyMovies~SELECT Stuff((Select ', ' + A.nvcName FROM tblActors A LEFT JOIN tblTitleActor T ON A.intId = T.intActor WHERE intTitle = 909 ORDER BY T.intSort FOR XML PATH('')),1,2,'') AS ActiveActors~MLDB_MyMovies_myClient2


I've noticed that 2 single quotes get transformed into one single quote when I run the query through a client. This gives me the error. Looks to me like an issue that should be fixed.

Thanks for the pointer to verbose logging, I was wondering what whas really passed on al query. I guess this query is a bit of a hack but it should work. My only other option is to retrieve all actors in seperate records, loop through and make a string... I'm hoping to avoid that.

Droezel
Member
Member
Posts: 75
Joined: Fri Aug 03, 2007 2:35 am
Contact:

Postby Droezel » Tue Sep 02, 2008 5:04 pm

Hah!

When I change the '' into '''' it works if ran from the client, but off course it chrashes when ran from MLServer's test command button.

Good enough for me as a fix. But maybe it should be looked into.

90CE
Developer
Developer
Posts: 573
Joined: Mon Nov 28, 2005 1:19 am
Location: Waukee, Iowa
Contact:

Postby 90CE » Tue Sep 02, 2008 5:21 pm

The new MLDB will have a function called create_table that will return a multiline text field that may provide what you are looking for.

command structure:

Code: Select all

mldatabase|create_table~Workout~SELECT Convert(char(10),DateTime,10) as 'Work Out ', Reps, Weight FROM History WHERE (UserName = '{{MLWorkout_User_Label}}') AND (RoutineID = {{Workout_RoutineID_1}})~Workout_Detail


provides the following:

Code: Select all

            Work Out          Reps       Weight
            08-17-08           20         32.5
            08-17-08           20         32.5


Seems to work well with a multiline text box.

User avatar
DaveB
Simply Incredible
Simply Incredible
Posts: 3143
Joined: Sun May 30, 2004 10:57 pm
Location: Mays Landing, NJ
Contact:

Postby DaveB » Sun Sep 07, 2008 8:05 am

Tracy; DId you ever get MLDB to the point that it will work on MS Server 2008? Last I tried, it crashed MLS.
Thanks!

Dave Bruner
:shock:

User avatar
DavidL
Simply Incredible
Simply Incredible
Posts: 11071
Joined: Sat Feb 08, 2003 9:39 am
Location: Metamora, Michigan
Contact:

Postby DavidL » Sun Sep 07, 2008 8:14 am

DAveB,
does it crash during installation, during mlserver startup? what error messages? My guess is there is a dependency file missing and we have to track which one.


Return to “MLDatabase”

Who is online

Users browsing this forum: No registered users and 1 guest