Our group routinely communicates with an FM server via DirectDBMS, so let me
offer some dumb questions/advice
1] are you sure you using the right JDBC driver for the version of the server
you are talking to
Is the JDBC driver located where TS can use it?
2] can you run a simple query in TS and get an answer
select * from Packages
Use TS to limit the response to 1 row
If you get a response, ask for the zero row!
@@request$resultset[0,*]
The column names may have some surprises for you (they certainly did
for us when we set this up)
If you cannot get a response from the select *, it has to be a driver/url
problem
Your JDBC for filmmaker should look like this.
<DataSource ID=“databasename">
<DSN>databasename</DSN>
<DriverClass>com.filemaker.jdbc.Driver</DriverClass>
<URL>jdbc:filemaker://111.11.11.111/databasename</URL>
<filemaker://111.11.11.111/oir%3C/URL%3E>
<JNDIName>databasename</JNDIName>
<Properties/>
</DataSource>
3] If you CAN connect, be sure to reference all actual column names (as found
in your select statement) within double quotes. We found that to be a failsafe
for us.
Like this
SELECT p."last", p."first", p."middle", p."ned", p."current_ic",
p."current_prof_design", p."current_Lab",p."Current_PD_Affective_Date"
FROM PersonnelFile p
where p."ned" = '<@ARG NEDID>'
order by p."last",p."first" asc
Dale Graham
[email protected]
> On Jul 26, 2019, at 10:53 AM, Robert Shubert <[email protected]> wrote:
>
> Yes that should work. I suspect it's a bug in TS.
>
> From: Wayne Irvine [mailto:[email protected]
> <mailto:[email protected]>]
> Sent: Thursday, July 25, 2019 4:03 PM
> To: [email protected] <mailto:[email protected]>
> Subject: Re: TeraScript-Talk: First time doing Direct_DBMS
>
> Ok, so in theory should this work?
> <image001.png>
>
> What am I missing?
>
> Wayne
>
>> On 26 Jul 2019, at 1:40 am, Robert Shubert <[email protected]
>> <mailto:[email protected]>> wrote:
>>
>> The alias t1 is set in the query by having it follow the table name, so yes,
>> "FROM test t1" is creating the alias t1
>>
>> @BOUNDVALS should work normally, in the query below there are none, so
>> nothing should've been reported.
>>
>> From: Wayne Irvine [mailto:[email protected]
>> <mailto:[email protected]>]
>> Sent: Wednesday, July 24, 2019 6:43 PM
>> To: [email protected] <mailto:[email protected]>
>> Subject: Re: TeraScript-Talk: First time doing Direct_DBMS
>>
>> <@SQL> just gives the same string as the DEBUG information.
>> <image001.png>
>>
>> It gives no information on how the t1 alias is set up (unless ‘FROM
>> testtable t1’ is how it is done). Either way, this statement used in the
>> Direct_DBMS action results in an error still.
>>
>> <@BOUNDVALS> is pink in Terascribe and ignored by Terascript.
>>
>> Wayne
>>
>>> On 24 Jul 2019, at 12:01 am, Robert Shubert <[email protected]
>>> <mailto:[email protected]>> wrote:
>>>
>>> You can see generated SQL with @SQL and @BOUNDVALS, just output those tags
>>> immediately after the query.
>>>
>>> -----Original Message-----
>>> From: Wayne Irvine [mailto:[email protected]
>>> <mailto:[email protected]>]
>>> Sent: Monday, July 22, 2019 7:03 PM
>>> To: [email protected] <mailto:[email protected]>
>>> Subject: Re: TeraScript-Talk: First time doing Direct_DBMS
>>>
>>> Well no luck so far on the Direct_DBMS action.
>>>
>>> Tried with a fresh new table and very simple SQL statement and got the same
>>> error.
>>>
>>> I would really love a way to actually log the SQL statement generated and
>>> sent by the Search Action. The one in the Debug uses aliases and you can’t
>>> see the steps beforehand to test it out.
>>>
>>> Wayne
>>>
>>>
>>>
>>>> On 12 Jul 2019, at 10:23 am, Wayne Irvine <[email protected]
>>>> <mailto:[email protected]>> wrote:
>>>>
>>>> This is Mac OSX Sierra Server 10.12.6 and Filemaker Server Advanced 12.
>>>>
>>>> What i would like to be able to log is the call the Search action makes to
>>>> the JDBC plugin. Then I could emulate tis is a Direct_DBMS action and
>>>> modify from there.
>>>>
>>>> One thing that has got me thinking is the actions next to Direct_DBMS.
>>>> Begin_Transaction and End_Transaction. Do these need to be before and
>>>> after the Direct_DBMS action? I’ve never seen documentation for how these
>>>> work.
>>>>
>>>> Wayne
>>>>
>>>>
>>>>
>>>>> On 12 Jul 2019, at 9:12 am, ubslab <[email protected] <mailto:[email protected]>>
>>>>> wrote:
>>>>>
>>>>> Wayne,
>>>>>
>>>>> Am I correct assume this is all on a MacOS server?
>>>>>
>>>>> If so, which version of OS and FileMaker are you using?
>>>>>
>>>>> - Steve K
>>>>>
>>>>> On 7/10/2019 7:18 PM, [email protected] <mailto:[email protected]> wrote:
>>>>>
>>>>>
>>>>>> Wayne,
>>>>>>
>>>>>> I recall needing to adjust datatype(s) in FM databases to work
>>>>>> correctly with Wtango and JDBC/ODBC.
>>>>>>
>>>>>> Perhaps a test FM db with only integer columns.
>>>>>>
>>>>>> - Steve K
>>>>>>
>>>>>>
>>>>>> On 7/10/19 7:06 PM, ubslab wrote:
>>>>>>
>>>>>>
>>>>>>> Wayne,
>>>>>>>
>>>>>>> Are you able to connect to FM table.columns in the Terascribe
>>>>>>> editor JDBC/ODBC panel?
>>>>>>>
>>>>>>> - Steve K
>>>>>>>
>>>>>>> On 7/10/19 6:45 PM, Wayne Irvine wrote:
>>>>>>>
>>>>>>>
>>>>>>>>> On 11 Jul 2019, at 12:38 am, Robert Shubert
>>>>>>>>> <[email protected] <mailto:[email protected]>>
>>>>>>>>> wrote:
>>>>>>>>>
>>>>>>>>> Wayne,
>>>>>>>>>
>>>>>>>>> First do a simple query like "SELECT 1" just to double check the DBMS.
>>>>>>>> I tried:
>>>>>>>> SELECT 1
>>>>>>>> SELECT 1 FROM Packages
>>>>>>>>
>>>>>>>> both gave an error.
>>>>>>>>
>>>>>>>>
>>>>>>>>> Then try grabbing just one row "SELECT packagename FROM Packages
>>>>>>>>> WHERE packagename = '{known name}’"
>>>>>>>> SELECT packagename FROM Packages WHERE packagename = 'Kessell Run’
>>>>>>>>
>>>>>>>> Error
>>>>>>>>
>>>>>>>>
>>>>>>>>> If that works, then we may be looking for a bug dealing with
>>>>>>>>> non-value. Try "SELECT packagename FROM Packages WHERE
>>>>>>>>> packagename IS NOT NULL”
>>>>>>>> SELECT packagename FROM Packages WHERE packagename IS NOT NULL
>>>>>>>>
>>>>>>>> Error
>>>>>>>>
>>>>>>>>
>>>>>>>>> What version of TS are you running? There was a JDBC fix in 7.1.1
>>>>>>>>> and I have another fix (which may be your problem) coming in 7.1.5.
>>>>>>>> 7.1.3.1
>>>>>>>>
>>>>>>>> Wayne
>>>>>>>>
>>>>>>>> Byteserve Pty Ltd
>>>>>>>> w: http://www.byteserve.com.au/ <http://www.byteserve.com.au/>
>>>>>>>> e: [email protected] <mailto:[email protected]>
>>>>>>>> p: +61 02 9960 6099
>>>>>>>> m: 0409 960 609
>>>>>>>> f: +61 02 9960 6088
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> ----------------------------------------
>>>>>>>>
>>>>>>>> To unsubscribe from this list, please send an email to
>>>>>>>> [email protected] <mailto:[email protected]> with
>>>>>>>> "unsubscribe terascript-talk" in the body.
>>>>>>>>
>>>>>>>
>>>>>>> ----------------------------------------
>>>>>>>
>>>>>>> To unsubscribe from this list, please send an email to
>>>>>>> [email protected] <mailto:[email protected]>with
>>>>>>> "unsubscribe terascript-talk" in the body.
>>>>>>>
>>>>>
>>>>>
>>>>> ----------------------------------------
>>>>>
>>>>> To unsubscribe from this list, please send an email to
>>>>> [email protected] <mailto:[email protected]> with
>>>>> "unsubscribe terascript-talk" in the body.
>>>>>
>>>>
>>>> Byteserve Pty Ltd
>>>> w: http://www.byteserve.com.au/ <http://www.byteserve.com.au/>
>>>> e: [email protected] <mailto:[email protected]>
>>>> p: +61 02 9960 6099
>>>> m: 0409 960 609
>>>> f: +61 02 9960 6088
>>>>
>>>>
>>>>
>>>>
>>>> ----------------------------------------
>>>>
>>>> To unsubscribe from this list, please send an email to
>>>> [email protected] <mailto:[email protected]> with "unsubscribe
>>>> terascript-talk" in the body.
>>>>
>>>
>>> Byteserve Pty Ltd
>>> w: http://www.byteserve.com.au/ <http://www.byteserve.com.au/>
>>> e: [email protected] <mailto:[email protected]>
>>> p: +61 02 9960 6099
>>> m: 0409 960 609
>>> f: +61 02 9960 6088
>>>
>>>
>>>
>>>
>>> ----------------------------------------
>>>
>>> To unsubscribe from this list, please send an email to
>>> [email protected] <mailto:[email protected]> with "unsubscribe
>>> terascript-talk" in the body.
>>>
>>>
>>>
>>>
>>> ----------------------------------------
>>>
>>> To unsubscribe from this list, please send an email to
>>> [email protected] <mailto:[email protected]> with "unsubscribe
>>> terascript-talk" in the body.
>>>
>>
>>
>> Byteserve Pty Ltd
>> w: http://www.byteserve.com.au/ <http://www.byteserve.com.au/>
>> e: [email protected] <mailto:[email protected]>
>> p: +61 02 9960 6099
>> m: 0409 960 609
>> f: +61 02 9960 6088
>>
>>
>> To unsubscribe from this list, please send an email to
>> [email protected] <mailto:[email protected]> with "unsubscribe
>> terascript-talk" in the body.
>>
>> To unsubscribe from this list, please send an email to
>> [email protected] <mailto:[email protected]> with "unsubscribe
>> terascript-talk" in the body.
>
>
> Byteserve Pty Ltd
> w: http://www.byteserve.com.au/ <http://www.byteserve.com.au/>
> e: [email protected] <mailto:[email protected]>
> p: +61 02 9960 6099
> m: 0409 960 609
> f: +61 02 9960 6088
>
>
> To unsubscribe from this list, please send an email to
> [email protected] <mailto:[email protected]> with "unsubscribe
> terascript-talk" in the body.
>
> To unsubscribe from this list, please send an email to
> [email protected] <mailto:[email protected]> with "unsubscribe
> terascript-talk" in the body.
----------------------------------------
To unsubscribe from this list, please send an email to [email protected]
with "unsubscribe terascript-talk" in the body.
