Questions tagged [prepared-statement]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
2
votes
1answer
44 views

Catching slow queries with prepared statements

I use extended events to identify slow queries, defined as queries taking more than 2 seconds to complete, like this: CREATE EVENT SESSION SlowQueries ON SERVER ADD EVENT sqlserver....
5
votes
1answer
102 views

Tracking errors from prepared SQL statements

I use extended events to store database errors, like this: CREATE EVENT SESSION [ErrorCapture] ON SERVER ADD EVENT sqlserver.error_reported ( ACTION ( sqlserver.client_hostname, ...
0
votes
1answer
140 views

executing SQL query with temp tables using 1.6jdk

I created a batch to run a query that has temp tables as the below example IF OBJECT_ID ('tempdb..#TEMP1') IS NOT NULL DROP TABLE #TEMP1 select * into #TEMP1 from cutomer IF OBJECT_ID ('tempdb..#...
0
votes
1answer
336 views

Preparing query in postgreSQL with variable table name?

I'm trying to do an INSERT INTO a table whose name is in the variable myTable. In MySQL, I'd do it like this: SET @SQL = CONCAT('INSERT INTO ',myschema'.'mytable,' SELECT action_name, user, ...'); ...
1
vote
2answers
271 views

Reset global MySQL status variables

I'm currently looking into an issue where when running MySQL prepared statement I get an exception: SQL Exception: Can't create more than max_prepared_stmt_count statements (current value: 16382) ...
0
votes
1answer
278 views

PostgreSQL: PreparedStatement to execute set schema command

I need to execute in a Java application a SET schema ? statement, where the placeholder is replaced by a value provided at runtime by the caller. I assumed I could use PreparedStatement to overcome ...
1
vote
1answer
668 views

Repreparation of prepared statements in MySQL/MariaDB, how to find a cause?

On our server running MariaDB 10.1 (the latest Debian package), with low load (load average: 0.79, 0.88, 1.00), I often receive error message that "Prepared statement needs to be re-prepared". It is ...
2
votes
1answer
64 views

Performance Monitoring for Prepared SQL?

My company is looking into changing to full use of prepared SQL across the board for some applications. For a few days, I've been trying to answer the question of "Do we have a way to monitor for ...
0
votes
1answer
71 views

Is there a way to run parameterized update statements using only DB2 commandline and bash?

[DB2 LUW 10.5] I need to update a few hundred thousand rows in a DB2 table. Individual update statements will take longer to execute compared to precompiled approach. I cannot use perl or any such ...
0
votes
0answers
40 views

Prepared query refused by MySQL when using placeholders

I'm trying to make a prepared query but MySQL refuses to accept it when I use placeholders: This query works WITH x AS (SELECT 'root' as user_id) SELECT x.user_id FROM x LEFT JOIN user ON (x.user_id ...
1
vote
1answer
395 views

How to safely insert a jsonb into a table?

I wrote this prepared statement for inserting jsonb data into a table: PREPARE posts_insert(jsonb) AS INSERT INTO posts(data) VALUES($1) RETURNING ID; Though I'm concerned if this query has some ...
1
vote
1answer
584 views

MSSQL Server not using index when called with JDBC and Java PreparedStatement [closed]

I am using a MSSQL Server 2016 from my java application via JDBC with transaction isolation level READ_COMMITTED and with AUTOCOMMIT = false. I need to lock some rows of a table from differnet ...
0
votes
0answers
344 views

Performance impact of repeatedly PREPARE-ing the same query?

Relevant documentation: https://www.postgresql.org/docs/current/static/sql-prepare.html The performance benefits of preparing an SQL query once, and using execute to run it with different values is ...
0
votes
0answers
38 views

Statements vs PreparedStatements performance

I know that PreparedStatements are parsed once, so typically they execute faster. But consider following query: select * from plc join qsn on qsn.id = plc.fk_qsn join prs on prs.id = qsn....
3
votes
1answer
2k views

Alternative to Dynamic SQL inside trigger?

My database has the following tables: TAG ---------------------- | tag_id | tag_name | ---------------------- TAG_VALUE: Stores values associated to each tag ----------------...
2
votes
1answer
74 views

Why would a Greenplum procedure not recognize a view change?

I have a procedure that loops through a query that returns a set of dates, recreates a set of views using the date in the WHERE clause, and the calls a series of other procedures that use these views. ...
3
votes
1answer
112 views

Preparing CREATE statement

I try to have a view created on the fly. In my case it's a pivot view on a EAV-style table. Assuming a table which contains two attributes I use a statement like this, executed inside of a trigger ...
5
votes
2answers
8k views

Microsoft SQL Server: Prepared Statements

How do you create and use a prepared statement in SQL Server? In PostgreSQL, you do something like: PREPARE prepared (varchar, varchar, etc) AS INSERT INTO table(field,field,etc) VALUES ($1,$...
7
votes
1answer
9k views

Getting Error : [Err] 1615 - Prepared statement needs to be re-prepared with MySQL 5.6.30

Problem/Issue : Getting Error : [Err] 1615 - Prepared statement needs to be re-prepared I have a Stored Procedure which contains a Prepared Statement and a view DROP PROCEDURE IF EXISTS `sampleProc`...
3
votes
1answer
437 views

Persisting Prepared Statements

I know that the documentation states: Prepared statements only last for the duration of the current database session. Is there any way way to either save a prepared statement create code which ...
2
votes
0answers
102 views

JDBC Statement vs. Prepared Statement SQL Injection

as far as I understand a "normal" JDBC Statement which is created via conn.createStatement() is prone to SQL-Injection. A prepared statement protects from such attacks. So why/when should I use a ...
1
vote
0answers
466 views

MySQL: So many prepared statements in general log file

One of my clients complained serious performance issues in a web-application written in Java. I was suspecting that there might be some unoptimized queries which are causing the problem. I've enabled ...
14
votes
2answers
3k views

What is the sense and benefit of using SqlCommand.Prepare()?

I came across developer code where SqlCommand.Prepare() (see MSDN) method is extensively used in advance of execution of SQL queries. And I wonder what is the benefit of this? Sample: command....
0
votes
0answers
658 views

Fetching from parent/child table pairs

Let there are two tables: "parent" and "child" related by 1:n relation (to every "parent" row there correspond any number of "child" rows). Suppose we need to fetch a subset of parent table with all ...
1
vote
2answers
1k views

Benefits of using prepared statements in stored procedures?

Except from helping avoid SQL injection, what are the benefits - if any - of using prepared statements inside a stored procedure (assume query is only executed once in the procedure)? Does the engine ...
2
votes
1answer
410 views

Mysql Connector C++ statement values question

I just want to know, if a value, like an int for example, is actually converted into a string before being sent as part of the command. pstmt = con->prepareStatement("INSERT INTO test(id) VALUES (?...
1
vote
1answer
4k views

Syntax error using preparedstatements with postgresql

I have a Java application connecting to a PostgreSQL database. Using straightforward code like: ps = con.prepareStatement(sql); if (dataTypes != null && dataTypes.size() >...
0
votes
1answer
787 views

In MySQL why is the first batch executed through client-side prepared statement slower?

I have around a million lines to be inserted into a MySQL (InnoDB) database, so to speed things up I turned to batch/multi-line insert. To be specific, I connect to the database (I'm using Java, so it'...
2
votes
1answer
215 views

How compiled are prepared statements?

I found this explanation of how prepared statements are compiled, but I'm still unsure. What exactly is "compiled" in the context of a prepared statement? Is a C compiler executed to create a custom ...
1
vote
1answer
743 views

How do I report progress of a long running transaction in MySQL?

I have this transaction in a stored procedure: ... START TRANSACTION; WHILE @Codes > 0 DO EXECUTE qry_rand_stmt; EXECUTE qry_insert_stmt; SET @Codes = @Codes - ROW_COUNT(); END ...
0
votes
2answers
454 views

DBO execute returns true but doesn't execute

I am having a bit of trouble with some of my code. This is the code: $STMT3 = $PDO->prepare(' INSERT INTO `lotteries_entries` (steamID, lottery_id) VALUES (:STEAMID, :ID); UPDATE `...
3
votes
1answer
1k views

Prepared statement overhead

I am using libpqxx to communicate with a PostgreSQL 9.3 server. For multiple table inserts, if they are split into individual prepared statements, they cost approximately 2ms each. If they are all ...
9
votes
1answer
5k views

sp_prepexec (sp_execute) vs. sp_executeSQL

The meat of the question: are actual stored procedures the only mechanism that implements temp table caching or do system stored procedures such as sp_executeSQL / sp_execute also take advantage of ...
1
vote
1answer
118 views

How much should I care about prepared statements for simple queries?

I wonder how much I should care about prepared statements (and therefore try to optimize my code) in case of pretty simple statements. The question originates from the fact that I recently noticed ...
2
votes
1answer
421 views

Execution Plan in Microsoft SQL Server

I have a stored procedure in my database. As per my knowledge it's pre-compiled subroutine. Question 1: If I execute following queries from Hibernate, will database create a separate execution plan ...
0
votes
1answer
86 views

Can a connection's prepared statements be altered by Postgres's admin functionality? [closed]

I am trying to tamper proof as best as possible a peer to peer application. I am concerned that the prepared statements will be altered. Can the Postgres admin alter a connection's prepared ...
0
votes
0answers
253 views

Performance: Interleaving several statement handles in MySQL

Does interleaving several statement handles slow the system down? Example (Perl): $sth1->execute(...); $sth2->execute(...); $sth1->execute(...); $sth2->execute(...); (interleaved) vs $...
1
vote
0answers
5k views

How can I create a view in a stored procedure on MySQL?

I am trying to dynamically create views based on an SQL query and have a trigger recreate them when the fields in the view are to change. When I call the procedure I end up with a permission denied ...
3
votes
0answers
79 views

Tagged questions

I have similar to StackExchange network that utilises tagged questions. In my web application Users also can find questions by tags. The query below returns a set of tagged questions: SELECT ...
3
votes
1answer
240 views

Where is the generic plan stored when using PREPARE statement in PostgreSQL?

According to the document, if a prepared statement is executed enough times, the server may eventually decide to save and re-use a generic plan rather than re-planning each time. My question is where ...
5
votes
1answer
5k views

Prepared statement execution with variable number of parameters to be bound

Some stored procedures I work with need to interpolate WHERE criteria based on if procedure input parameters have been supplied. To avoid potential injection points, I'd like to utilize parameter ...
0
votes
3answers
185 views

prepared statements are not inserted

i am new to JDBC and trying following code. Could not find the error in it try{ Connection con = ConnectionProvider.getCon(); stmt=con.prepareStatement("insert into ...
10
votes
1answer
5k views

PostgreSQL PREPARE query with IN () parameters

I'm trying to prepare a query from PHP like: pg_prepare($con, "prep", "select * from test where tid in ($1)"); and then execute it with: $strpar = "3,4,6,8,10"; pg_execute($con, "prep", array($...
30
votes
1answer
17k views

SQL injection in Postgres functions vs prepared queries

In Postgres, are prepared queries and user defined functions equivalent as a mechanism for guarding against SQL injection? Are there particular advantages in one approach over the other?
1
vote
1answer
17k views

How to create an Sql table from SELECT with a multiple row DISTINCT constraint?

I want to create a table from a SELECT statement based on a multiple row DISTINCT, but PostgreSQL does not allow me to do it. I need to create a table where the elements were previously selected from ...
1
vote
1answer
1k views

Stored procedure and prepared statements

I have 2 stored procedures: CREATE PROCEDURE `GetEntryCount`(param_Criteria VARCHAR(500)) BEGIN IF (param_CRITERIA <> "") THEN SET @QUERY1 = concat('SELECT user_name,count(*) as count ...
1
vote
1answer
1k views

MySQL stored routine performance while using PREPARE

Instead of maintaining stored routines for each database in my current environment I have decided to create separate database just for stored routines storage. Mainly I am using them for reporting. ...
1
vote
1answer
14k views

How to return resultset from MySQL Stored Procedure using prepared statement?

DELIMITER $$ CREATE PROCEDURE List_IL() BEGIN DECLARE Project_Number_val VARCHAR( 255 ); DECLARE Temp_List_val VARCHAR(255); DECLARE Project_List_val VARCHAR(255); DECLARE FoundCount INT; ...
1
vote
3answers
2k views

PreparedStatement caching size on Oracle and SQL Server

I understand that PreparedStatements are complied and cached by database servers. I would like to know if all the preparedStatements are complied and cached on the server, in which case what is the ...
2
votes
1answer
2k views

column precision exceeded. ORA-01438 is so unmeaningful

I am not sure if this is a question to StackExchange DB Admin group. please let me know if its not. Let's suppose you have a Java batched prepared statement writing data to database. If one of the ...