# Run SQL statement

**URL:** <https://discuss.elastic.co/t/run-sql-statement/105651>\
**Category:** Logstash\
**Created:** [October 29, 2017, 6:19am UTC](https://discuss.elastic.co/t/run-sql-statement/105651 "2017-10-29T06:19:29Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Sumit\_Gupta1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sumit_gupta1/32/22861_2.png) [@Sumit\_Gupta1](https://discuss.elastic.co/u/Sumit_Gupta1)\
**Post date:** [October 29, 2017, 6:19am UTC](https://discuss.elastic.co/t/run-sql-statement/105651/1 "2017-10-29T06:19:29Z")

</div>

Hi,  
I want to run following SQL statment:

input {  
jdbc {  
jdbc\_driver\_library =\> "/app/logstash-5.6.2/lib/ojdbc7.jar"  
jdbc\_driver\_class =\> "Java::oracle.jdbc.driver.OracleDriver"  
jdbc\_connection\_string =\> "jdbc:oracle:thin:@localhost:1521/apextst"  
jdbc\_user =\> "**_"  
jdbc\_password =\> "_**"  
jdbc\_fetch\_size =\> 3000  
statement =\> "select round(sum(used.bytes) / 1024 / 1024/1024 ) || ' GB' "Database Size",  
round(free.p / 1024 / 1024/1024) || ' GB' "Free space"  
from (select bytes from v$datafile  
union all select bytes from v$tempfile  
union all select bytes from v$log) used,  
(select sum(bytes) as p from dba\_free\_space) free  
group by free.p"  
}  
}  
output {  
stdout { codec =\> rubydebug }  
elasticsearch {  
hosts =\> ["localhost:9200"]  
index =\> "logstash-database-info"  
document\_type =\> "mytype"  
}  
}

This is giving following error:

Sending Logstash's logs to /app/logstash-5.6.2/logs which is now configured via log4j2.properties  
[2017-10-29T01:09:56,411][INFO][logstash.modules.scaffold] Initializing module {:module\_name=\>"netflow", :directory=\>"/app/logstash-5.6.2/modules/netflow/configuration"}  
[2017-10-29T01:09:56,445][INFO][logstash.modules.scaffold] Initializing module {:module\_name=\>"fb\_apache", :directory=\>"/app/logstash-5.6.2/modules/fb\_apache/configuration"}  
[2017-10-29T01:09:56,814][ERROR][logstash.agent] Cannot create pipeline {:reason=\>"Expected one of #, {, } at line 9, column 83 (byte 419) after input {\n jdbc {\n jdbc\_driver\_library =\> "/app/logstash-5.6.2/lib/ojdbc7.jar"\n jdbc\_driver\_class =\> "Java::oracle.jdbc.driver.OracleDriver"\n jdbc\_connection\_string =\> "jdbc:oracle:thin:@localhost:1521/apextst"\n jdbc\_user =\> "DBA\_WORK"\n jdbc\_password =\> "DBA\_WORK123"\n jdbc\_fetch\_size =\> 3000\n statement =\> "select round(sum(used.bytes) / 1024 / 1024/1024 ) || ' GB' ""}

I can able to small SQL statement but facing problem to execute big SQL statement.

Can you please help this?

How can i execute big SQL statment?

---

<div class="post-metadata">

**Author:** ![Sumit\_Gupta1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sumit_gupta1/32/22861_2.png) [@Sumit\_Gupta1](https://discuss.elastic.co/u/Sumit_Gupta1)\
**Post date:** [October 29, 2017, 7:26am UTC](https://discuss.elastic.co/t/run-sql-statement/105651/2 "2017-10-29T07:26:24Z")

</div>

I'm using following statment:

statement\_filepath =\> "/app/logstash-5.6.2/bin/data.sql AND timestamp \>= :sql\_last\_value"

This is giving following error:

input {  
jdbc {  
# This setting must be a path  
# File does not exist or cannot be opened /app/logstash-5.6.2/bin/data.sql AND timestamp \>= :sql\_last\_value  
statement\_filepath =\> "/app/logstash-5.6.2/bin/data.sql AND timestamp \>= :sql\_last\_value"  
...  
}  
}

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [October 30, 2017, 6:52am UTC](https://discuss.elastic.co/t/run-sql-statement/105651/3 "2017-10-30T06:52:44Z")

</div>

> statement =\> "select round(sum(used.bytes) / 1024 / 1024/1024 ) || ' GB' "Database Size",  
> round(free.p / 1024 / 1024/1024) || ' GB' "Free space"  
> from (select bytes from v$datafile  
> union all select bytes from v$tempfile  
> union all select bytes from v$log) used,  
> (select sum(bytes) as p from dba\_free\_space) free  
> group by free.p"

This is a double quoted string so you can't have double quotes inside the string. In many cases you'd be able to escape the double quotes inside the string but I'm not sure that works here. Do you really have to mix single a double quotes inside the SQL statement?

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [October 30, 2017, 6:53am UTC](https://discuss.elastic.co/t/run-sql-statement/105651/4 "2017-10-30T06:53:31Z")

</div>

> statement\_filepath =\> "/app/logstash-5.6.2/bin/data.sql AND timestamp \>= :sql\_last\_value"

The "AND timestamp \>= :sql\_last\_value" part must, of course, go inside data.sql.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [November 27, 2017, 6:53am UTC](https://discuss.elastic.co/t/run-sql-statement/105651/5 "2017-11-27T06:53:36Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.