Passing parameter in Teradata Rest Service

Tools & Utilities
Enthusiast

Passing parameter in Teradata Rest Service

Hi,

I am using Teradata Rest Service and trying to pass a parameter for my query:

select ? as ip_address
from my_database.ip_table as g
where internallib.ip2ip3(ip_address) between g.start_ip and g.end_ip
;

This is how my request looks like:

{ url: 'http://my_server:1080/tdrest/systems/service_name/queries',
gzip: true,
json: true,
headers:
{ Authorization: 'Base 64 auth',
Accept: 'application/vnd.com.teradata.rest-v1.0+json',
'Accept-Encoding': 'gzip',
'Content-Type': 'application/json' },
body:
{ query: 'my query (see above)',
params: [ [ '10.244.200.50' ], [ '10.5.4.1' ] ],
format: 'array',
includeColumns: true } }

But I get a Bad Request error. 

How can I use parameters in my query? What am I doing wrong?

Tags (1)
3 REPLIES
Enthusiast

Re: Passing parameter in Teradata Rest Service

Above parameterised SQL syntax looks invalid to me, from POST data there are couple of parameters passed while SQL has only one parameter.

 

Below is example that I used on my system.

 

SQL

locking row FOR access SELECT Count(*) 
FROM   dbc.logonoff
WHERE  username=?
AND    logdate=?

Params:

[["dbc","2016-09-10"]]

 

Here is my Post data:

{
"query": "locking row for access select count(*)from dbc.logonoff where username=? and logdate=?",
"params": [
[
"dbc",
"2016-09-10"
]
],
"batch": "false",
"format": "OBJECT",
"includeColumns": "false",
"continueOnError": "false",
"logMech": "DEFAULT",
"spooledResultSet": "false"
}

 

Hope this helps :)

Enthusiast

Re: Passing parameter in Teradata Rest Service

Great example!

 

How would the request and corresponding sql query look like when the request contains a list of values/array that need to be filterd in the where clause ?

Thanks in advance!

Niklas

Enthusiast

Re: Passing parameter in Teradata Rest Service

Related, this post helped me figure out how to force LDAP over REST using logMech per the example included above, thanks...  here is what I did:

{"query":"select top 1 * from dbc.tables", "logMech": "ldap"}

 

// using c#           	    
                using (var client = new MyWebClient())
                {
			client.Credentials = new System.Net.NetworkCredential(un, pw);
                    	client.Headers[HttpRequestHeader.Accept] = "application/vnd.com.teradata.rest-v1.0+json";
                    	client.Headers[HttpRequestHeader.ContentType] = @"application/json";
                    	json = @"{""query"":""select top 1 * from dbc.tables"", ""logMech"": ""ldap""}";
                    	result = client.UploadString("http://urlstring:portnum/tdrest/systems/dbnamehere/queries", "POST", json);
                   if (result == null)
                    {
                        client.Dispose();
                        throw new System.Exception("TeraData Missing Result");
                    }
                    else
                    {
                        client.Dispose();
                        return Clean_TeraData_Format(result);
                    }
                }

    class MyWebClient : WebClient
    {
        // this is necessary as the connection from webclient remaining open but TD closed it.
        protected override WebRequest GetWebRequest(Uri address)
        {
            WebRequest request = base.GetWebRequest(address);
            if (request is HttpWebRequest)
            {
                (request as HttpWebRequest).KeepAlive = false;
            }
            return request;
        }
    }