The tag has no usage guidance.

learn more… | top users | synonyms

0
votes
0answers
45 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 ...
0
votes
1answer
7k 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
146 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 store procedure (assume query is only executed once in the procedure)? Does the engine ...
0
votes
0answers
32 views

Mariadb Prepared Statements stopped working, they were OK last week, I need help locating the issue

I have a number of stored procedures that were working perfectly well last week, but this week are causing me problems. The first problem is I seem to be getting the following: Mariadb SQL ...
10
votes
2answers
188 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: ...
0
votes
0answers
47 views

How to collect prepared statements in performance_schema on MySQL 5.7

Just installed MySQL 5.7.10 64bit locally (Windows environment) and prepared statements are not being logged in performance_schema. In release notes it's being specified this feature is available ...
0
votes
0answers
57 views

MySQL prepared statement error

Good afternoon, I'm trying to use a prepared statement within a stored procedure in MySQL, but when I attempt to link the variable I get a syntax error. Here is the code SET _sql = ...
0
votes
0answers
93 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 ...
4
votes
1answer
1k 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 ...
2
votes
1answer
51 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
714 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
224 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 ...
2
votes
1answer
75 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 ...
14
votes
1answer
5k 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?
0
votes
1answer
215 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
82 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
408 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 ...
1
vote
1answer
71 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
245 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
63 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
235 views

Using prepared statements in libpqxx with variable numbers arguments

I am trying to do some streamlining of a PostgreSQL database application. I am collecting data in a real-time setting using a Windows application and libpqxx. I would like to look into using ...
0
votes
0answers
182 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
2k 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
70 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 ...
2
votes
1answer
135 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 ...
1
vote
3answers
1k 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 ...
0
votes
3answers
102 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 ...
7
votes
1answer
2k 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", ...
1
vote
1answer
664 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 ...
0
votes
1answer
761 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. ...
9
votes
3answers
16k views

How to use insert delay with the InnoDB engine and use less connection for insert statements?

I'm working on an application which involves a lot of database writes, approximately ~70% inserts and 30% reads. This ratio would also include updates which I consider to be one read and one write. ...
2
votes
1answer
1k 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 ...
1
vote
1answer
9k 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; ...
2
votes
3answers
4k views

How do i subtract a number of days from a prepared statement parameter in db2?

I have a C# application using OLEDB to connect to a DB2 database. Basically, I want to do this: SELECT CALC.WEEK, COUNT(CALC.ID) AS TRANSACTIONS , SUM(CALC.SUBTOTAL) AS REVENUE FROM ( ...
4
votes
2answers
3k views

DB2: Error when using NOT EXISTS with SYSIBM.DUAL in a batch query

I have data in a java object as data1, data2. data1 and data2 together forms a composite key in myTable where I want to insert the object. The writing is happening as a batch. Like 10 insert ...
7
votes
1answer
1k views

List Prepared Statements

Anybody know how I can list the prepared statements that are in a SQL Server instance?
5
votes
2answers
19k views

Can I execute a prepared statement inside a MySQL SELECT?

I'm running a search query in MySQL to return items from a products and pricelist table. When a user does a search query I need to get products from the products table LEFT JOINED with all ...
11
votes
2answers
21k views

How to use prepared statements within MySQL stored procedures?

I'm using mysql and I need to somehow use the column curid returned by the prepared statement in the later query. I use prepared statements because as I've read, it is the only way to pass a variable ...