Latest post Wed, Apr 21 2010 2:20 PM by maryd. 5 replies.
Page 1 of 1 (6 items)
Sort Posts: Previous Next
  • Tue, Sep 8 2009 9:33 PM

    Problem using Database Query in Deko

    We have, in the past, successfully created Deko pages that query an ODBC database for our local election night coverage.  We did this on an older Deko 500, and it worked great year after year.  We have since upgraded to a Deko 3000 Hybrid, which has the database and automation options installed (running Deko version 5.0, build 3410-1)

    We set up the ODBC Data Source Administrator exactly the same way as the older Deko machine, and have the correct drivers for FileMaker Pro insatlled.  Everything seems to connect correctly, except when we hit the "copy to layer" button on the "Database" view window, the corresponding text in the layer disappears completely, and is not replaced with the information from the field in the database.  It looks like it is being replaced with a null set of data.  Even when I have the frequency set to, say, 5 seconds, and the auto button checked, any text I type into the layer will disappear each time the database is queried. 

    From the looks of the help file, there used to be a "create query" and "read query" button in the older version of Deko, which appears to have been replaced by a "Copy to Layer" and "Get From Layer" button respectively.  I understand that, but what am I doing wrong?

    Filed under: , , , ,
  • Wed, Sep 9 2009 9:14 PM In reply to

    Re: Problem using Database Query in Deko

    First, can you tell me what version of the Deko software you are running?

    The ODBC driver for FileMaker Pro is not supplied with Windows XP. So presumably, you got the driver from the FileMaker site. I went to their site and I see that they have 2 drivers available - one is for when you are importing TO FileMaker and the other is when you are exporting FROM FileMaker (i.e., it's a data source). Hopefully, you used the latter. Then, when you configured ODBC, you should have created a "User DSN".

    To test the connection, in the Deko application, go to View->Database and the Database window should open.

    As a test, create a new graphic with a single layer and select that layer. Now do the following:

    1) Go to the "Database Name" and use the down arrow to select the DSN you created. Is that available? If so, click the "connect" checkbox. Is your database listed there?

    2) Next, go to the "From Table" and use the down arrow to select the table you are using in the FileMaker file. Is that table listed there?

    3) Next, go to the "Retrieve Field" and select your the name of the field you want to retrieve from the list of available fields using the down arrow. Is your field listed there?

    4) Next, choose the filed you are using to search on using the down arrow in the "Where Field" window. Is your field listed there?

    5) Now select the value to look for in the field specified in step4 by selecting the value in the "Equals" box. Is that value listed there?

    6) Finally, click on the "Copy to layer" button.

    7) The layer should now update to the value from the database.

    The behavior you are seeing where the data in the layer changes to something else is correct behavior.

    As an example, let's say you have a database named "DB", which has a table named "Table" which has 2 columns named "RowNum" and "Value" and there are two rows in the column, where row 1 has a "RowNum" entry of 1 and a "Value" entry of "$5.00" and row 2 has a "RowNum" entry of 2 and a "Value" entry of "$10.00. 

    You could specify: Database Name = "DB", Retrieve Field = "Value", From Table = "Table", Where Field = "RowNum" and Equals="1". When you click on "Copy to Layer", it should change the current text in that layer to "$5.00".

    To test to see exactly what possible values can get returned, you can temporarily change the "Where Field" value to the name of the field you want returned (in the example above that field is named "Value") and then click on the the down arrow in the "Equals" field. In the example above, you would see "$5.00" and "$10.00" as the only possible values that could be returned.

    In your actual database, it could be that your are returning a " " and that's overwriting the data that's already in that field.

    If you still have questions, pelase contact support at (800)639-7364.

     

    Dave

     

     

     

     

     

  • Thu, Sep 10 2009 10:01 PM In reply to

    Re: Problem using Database Query in Deko

    DekoCast Dave:

    First, can you tell me what version of the Deko software you are running?

    >Hi Dave.  We are using Version 5.0 Build 3410-1, on a Deko 3000/Hybrid.

     

    The ODBC driver for FileMaker Pro is not supplied with Windows XP. So presumably, you got the driver from the FileMaker site. I went to their site and I see that they have 2 drivers available - one is for when you are importing TO FileMaker and the other is when you are exporting FROM FileMaker (i.e., it's a data source). Hopefully, you used the latter. Then, when you configured ODBC, you should have created a "User DSN".

    >Yes, that is all correct.  The driver was supplied with FileMaker and was installed with the program.

     

     

    To test the connection, in the Deko application, go to View->Database and the Database window should open.

    As a test, create a new graphic with a single layer and select that layer. Now do the following:

    1) Go to the "Database Name" and use the down arrow to select the DSN you created. Is that available? If so, click the "connect" checkbox. Is your database listed there?

    2) Next, go to the "From Table" and use the down arrow to select the table you are using in the FileMaker file. Is that table listed there?

    3) Next, go to the "Retrieve Field" and select your the name of the field you want to retrieve from the list of available fields using the down arrow. Is your field listed there?

    4) Next, choose the filed you are using to search on using the down arrow in the "Where Field" window. Is your field listed there?

    >Yes, all of the above works perfectly.

     

     

    5) Now select the value to look for in the field specified in step4 by selecting the value in the "Equals" box. Is that value listed there?

    >This is where we seem to have a problem.  There are no values that show up in this drop-down field.  Only "$FieldValue" appears.  I know that on our previous machine (using the same version of FileMaker and the same drivers), these values did appear, and we were able to successfully draw data from FileMaker into a Deko layer.

     

     

    6) Finally, click on the "Copy to layer" button.

    7) The layer should now update to the value from the database.

    The behavior you are seeing where the data in the layer changes to something else is correct behavior.

    As an example, let's say you have a database named "DB", which has a table named "Table" which has 2 columns named "RowNum" and "Value" and there are two rows in the column, where row 1 has a "RowNum" entry of 1 and a "Value" entry of "$5.00" and row 2 has a "RowNum" entry of 2 and a "Value" entry of "$10.00. 

    You could specify: Database Name = "DB", Retrieve Field = "Value", From Table = "Table", Where Field = "RowNum" and Equals="1". When you click on "Copy to Layer", it should change the current text in that layer to "$5.00".

    To test to see exactly what possible values can get returned, you can temporarily change the "Where Field" value to the name of the field you want returned (in the example above that field is named "Value") and then click on the the down arrow in the "Equals" field. In the example above, you would see "$5.00" and "$10.00" as the only possible values that could be returned.

    In your actual database, it could be that your are returning a " " and that's overwriting the data that's already in that field.

    >In the database itself, the values are clearly there, but you are correct, we seem to be returning a "" instead of one of the values.  Any other ideas would be greatly appreciated!!

    --Shawn

     

     

     

     

    If you still have questions, pelase contact support at (800)639-7364.

     

    Dave

     

     

     

     

     

     

  • Fri, Sep 11 2009 12:36 AM In reply to

    Re: Problem using Database Query in Deko

    Shawn,

    I just looked at the FileMaker website again and I looked at this guide: http://www.filemaker.com/downloads/pdf/fm10_odbc_jdbc_guide_en.pdf

    The configuration is a little more involved than some of the other ODBC drivers. In that document, P. 16 is where the configuration begins and there's a discussion of setting privileges . They also discuss out of date driver versions - which could be an issue.

    At the bottom of Page 18, it talkes about "Verifying Acess". This is a Test Connection and it's where you enter the Database User Name and Password to get access to the FileMaker database. Did you do the "Test Connection" and receive a "Connection test was successful" message?

    The Deko program makes a connection with the ODBC driver and sends the SQL Query command. After that, it's the driver that actually connects to the database.

     

    Dave

  • Tue, Apr 13 2010 1:04 AM In reply to

    • John Cruz
    • Not Ranked
    • Joined on Thu, Oct 13 2005
    • Bogotá, Colombia
    • Posts 7
    • Points 80

    Re: Problem using Database Query in Deko

    Hi there, I have the exact same problem with my Deko 1000 only that I'm trying to work with a MySQL database.
    I've installed the last version of the MySQL ODBC Driver, and tried several configurations. I get to the same point as pittsfieldtv: At the 5th step the drop down list displays just $FieldValue, I tried entering a value in the "Equals" box, but i get no result on the layer.

    The version of Deko is 5.0 Build 3430-1

    Anyone has got a solution to this?

    Thanks

    John

    Media Composer 8.6.5 - HP Z840-32GB-Nvidia Quadro M4000-DNxIO ISIS 5500 64TB + 64TB Expansion ISIS 5000 32TB + 32TB Expansion Interplay 3.5 iNEWS 3... [view my complete system specs]
    Filed under: , ,
  • Wed, Apr 21 2010 2:20 PM In reply to

    • maryd
    • Not Ranked
    • Joined on Wed, Aug 23 2006
    • Posts 12
    • Points 1,895
    • Moderator: Avid Motion Graphics

    Re: Problem using Database Query in Deko

    There are more recent versions of Deko3000, though I do not know if that would solve these database problems.  Version 5.2 is available, and version 5.3 will be available within the next few weeks.

    Databases work very well with Deko.  If you are having continuing problems with specific database types you should contact Avid Customer Support, or the Customer Support for your type of database.

Page 1 of 1 (6 items)

© Copyright 2011 Avid Technology, Inc.  Terms of Use |  Privacy Policy |  Site Map |  Find a Reseller