how to call postgresql stored procedure in laravel 5

Question

I have tested with two types 1)

 DB::statement('call abc_cmw("$ipaddress","$cname","$recvd_date","$language","$address1","$address2","$address3","$pincode","$mobileno","$amobileno","$email_address","$idproofdetail","$Description","$remedies","$gretype","$fcount","$content1","$content2","$district_problem","$city_problem", "$block_problem","$village_problem","$username","$sugg_demand","$dept_name","$indiv_grp","$ac_problem")');

2)

DB::select('exec abc_cmw("$ipaddress","$cname","$recvd_date","$language","$address1","$address2","$address3","$pincode","$mobileno","$amobileno","$email_address","$idproofdetail","$Description","$remedies","$gretype","$fcount","$content1","$content2","$district_problem","$city_problem", "$block_problem","$village_problem","$username","$sugg_demand","$dept_name","$indiv_grp","$ac_problem")');

but both ways have same error

QueryException in Connection.php line 647: SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "call" LINE 1: call abc_cmw("$ipaddress","$cname","$re... ^ (SQL: call abc_cmw("$ipaddress","$cname","$recvd_date","$language","$address1","$address2","$address3","$pincode","$mobileno","$amobileno","$email_address","$idproofdetail","$Description","$remedies","$gretype","$fcount","$content1","$content2","$district_problem","$city_problem", "$block_problem","$village_problem","$username","$sugg_demand","$dept_name","$indiv_grp","$ac_problem"))

pumbo · Accepted Answer · 2017-04-10 06:02:58Z

2

In PostgreSQL functions are called using SELECT clause, e.g.: SELECT func()

You should avoid inserting SQL parameters directly because of the possibility of SQL injections. Use bindings instead:

DB::statement('SELECT abc_cmw(?, ?, ?)', [$param1, $param2, $param3]);

or

DB::statement('SELECT abc_cmw(:param1, :param2, :param3)', [
  'param1' => $param1, 
  'param2' => $param2, 
  'param3' => $param3
]);

edited Apr 10, 2017 at 6:02

answered Apr 10, 2017 at 5:54

pumbo

3,9162 gold badges29 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Nisha Garg Over a year ago

i want to know in your solution question mark replace with column name or i need to instert question mark for every column?

pumbo Over a year ago

You can use either named bindings (second example) or array bindings (first example). If you prefer array bindings, then insert question mark for every column.

pumbo Over a year ago

By the way, variables that are passed to functions are called parameters or arguments, not columns.

Nisha Garg Over a year ago

its giving me error SQLSTATE[22P02]: Invalid text representation: 7 ERROR: malformed array literal: "" DETAIL: Array value must start with "{" or dimension information. (SQL: SELECT abc_cmw(, cdssfsdf, 2017-10-04, ,dfsfsdfsd,,,,8888888888,,[email protected],,dfsdfsfsfs,,N,1,,,58,001,,,,G,ADR,G,03))

pumbo Over a year ago

Does your function accept an array as one of the parameters? If so, then I have bad news. Laravel doesn't support passing php array as a parameter of Postgres function. The only way around is to do it manually. Google "php pass array to postgres function"

|

Collectives™ on Stack Overflow

how to call postgresql stored procedure in laravel 5

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related