Transactions And Sqlsrv Php Driver
Nov 20, 2007
Hi,
I am testing the sqlsrv driver for php5. I wonder if there is any way how to work with transactions.
What I basically need is some equivalent of:
mssql_query("BEGIN TRAN", $c);
The command sqlsrv_conn_execute($c, "BEGIN TRAN") returns null and error code 266, message [Microsoft][SQL Native Client][SQL Server]Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
Is it possible to use the driver the way I need? I mean, is there a way how to issue multiple statements (sqlsrv_stmt_execute()) in one transaction?
View 3 Replies
ADVERTISEMENT
Jan 19, 2008
Hi All,
To me the results of getSchemas appears wrong and the catalog/schema values the wrong way round!
Using latest version of Sql Server 2005 Express with 1.2.2828 JDBC driver.
Created a database called: db_connection_tests
Created some tables inside this database called:
main
child
lookupThese show in Sql Server Manager as:
dbo.main
dbo.child
dbo.lookupIf I now log on through JDBC as user "dddddddddddd" and run 2 metadata calls (getCatalogs() and getSchemas()) the results to me do not match, that is the schema and catalog seem to be wrong in getSchemas.
Why is the catalog DBO returned for schema d_connection_tests in getSchemas(), when not in results of getCatalogs()?
TABLE_SCHEM db_connection_tests
TABLE_CATALOG dbo
getDatabaseProductName: Microsoft SQL Server
getDatabaseProductVersion: 9.00.3042
getDatabaseMajorVersion: 9
getDatabaseMinorVersion: 0
meta.getDriverMajorVersion(): 1
meta.getDriverMinorVersion(): 2
meta.getDriverName(): Microsoft SQL Server 2005 JDBC Driver
meta.getDriverVersion(): 1.2.2828.100
List of schemas (snippet):
TABLE_SCHEM db_connection_tests
TABLE_CATALOG dbo
List of catalogs:
TABLE_CAT db_connection_tests
TABLE_CAT master
TABLE_CAT model
TABLE_CAT msdb
TABLE_CAT tempdb
Tried JTds driver and results for getSchemas() are different!
getDatabaseProductName: Microsoft SQL Server
getDatabaseProductVersion: 09.00.3042
getDatabaseMajorVersion: 9
getDatabaseMinorVersion: 0
meta.getDriverMajorVersion(): 1
meta.getDriverMinorVersion(): 2
meta.getDriverName(): jTDS Type 4 JDBC Driver for MS SQL Server and Sybase
meta.getDriverVersion(): 1.2.2
List of schemas:
TABLE_SCHEM db_connection_tests
TABLE_CATALOG null
Code
package test.com.database;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class SqlServeTest_DriverMs {
private Connection con;
private DatabaseMetaData meta;
public static void main(String[] args) throws ClassNotFoundException, SQLException {
SqlServeTest_DriverMs sqlServeTest = new SqlServeTest_DriverMs();
sqlServeTest.runTests();
}
protected void runTests() throws ClassNotFoundException, SQLException {
// Class.forName("net.sourceforge.jtds.jdbc.Driver"); // JTds Driver
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); // MS Driver
try {
// Jtds Drvier
// con = DriverManager.getConnection("jdbc:jtdsqlserver://localhost:1433/db_connection_tests;user=dddddddddddd;password=root");
// MS Driver
con = DriverManager.getConnection("jdbcqlserver://localhost:1433;user=dddddddddddd;password=root;database=db_connection_tests");
meta = con.getMetaData();
System.out.println("getDatabaseProductName: " + meta.getDatabaseProductName());
System.out.println("getDatabaseProductVersion: " + meta.getDatabaseProductVersion());
System.out.println("getDatabaseMajorVersion: " + meta.getDatabaseMajorVersion());
System.out.println("getDatabaseMinorVersion: " + meta.getDatabaseMinorVersion());
System.out.println("meta.getDriverMajorVersion(): " + meta.getDriverMajorVersion());
System.out.println("meta.getDriverMinorVersion(): " + meta.getDriverMinorVersion());
System.out.println("meta.getDriverName(): " + meta.getDriverName());
System.out.println("meta.getDriverVersion(): " + meta.getDriverVersion());
printOutSchemas();
printOutCatalogs();
} finally {
if (con != null) {
try {
con.close();
} catch (Exception e) { // Ignore error
}
}
}
}
protected void printOutSchemas() throws SQLException {
ResultSet rs = null;
try {
rs = meta.getSchemas();
System.out.println("List of schemas: ");
printResults(rs);
} finally {
if (rs != null) rs.close();
}
}
protected void printOutCatalogs() throws SQLException {
ResultSet rs = null;
try {
rs = meta.getCatalogs();
System.out.println("List of catalogs: ");
printResults(rs);
} finally {
if (rs != null) rs.close();
}
}
protected void printResults(ResultSet rs) throws SQLException {
final int cols = rs.getMetaData().getColumnCount();
while (rs.next()) {
System.out.println();
for (int i = 1; i <= cols; i++) {
System.out.println(' ' + rs.getMetaData().getColumnName(i) + ' ' + rs.getString(i));
}
}
}
}
View 1 Replies
View Related
Jan 1, 2008
I have been experimenting with the preview release of the SQL Server 2005 driver for PHP. I have used the PHP MSSQL extension a lot as well as connecting thru COM. I am glad we finally will see a Microsoft supported extension.
With the current release I am able to do most things my current MSSQL driver does. It does require some workarounds (there is no sqlsrv_num_rows nor sqlsrv_data_seek) here and there. But nothing major.
Now to get to the issue at hand. When I run a DELETE query, there is no statement resource returned and my application incorrectly detects an error.
Sample code:
$the_query = "DELETE FROM sometable WHERE id=5";
if ( ! $qid = sqlsrv_conn_execute ( $connection_id, $the_query ) )
{
echo $the_query."<br />";
foreach( sqlsrv_errors() as $error )
{
echo "SQLSTATE: ".$error['SQLSTATE']."<br/>";
echo "Code: ".$error['code']."<br/>";
echo "Message: ".$error['message']."<br/>";
}
exit();
}
Looks like a bug to me. The funny thing is that exactly the same bug was in the PHP MSSQL extension a while ago.
View 2 Replies
View Related
Feb 5, 2008
Running MS2k3 webserver with IIS and PHP 5.2.5
my php.ini is in the Windows folder containing among other things these 2 entries:
extension_dir ="C:PHPext"
extension=php_sqlsrv.dll
and the file php.sqlsrv.dll is located in the PHPext directory
I've restarted the IIS server with php running and working just fine. Is there anything else I could still check ?
Code Snippet
if (!extension_loaded('sqlsrv')) {
echo("NOT LOADED");
}
Produces: not loaded.
EDIT:
Dug up some logs:
The description for Event ID ( 2 ) in Source ( PHP-5.2.5 ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: php[2144]; PHP Warning: PHP Startup: Unable to load dynamic library 'C:PHPextphp_sqlsrv.dll' - The specified module could not be found.
in Unknown on line 0.
View 1 Replies
View Related
Dec 19, 2005
Hi,
The sqlsrv.exe process has been consuming a high number of cpu cycles and time and seems not be decreasing at all. Can somebody point me in the right direction as to how to identify the bottleneck and resolve the same at the earliest
Thanks in advance
View 3 Replies
View Related
Mar 22, 2004
Hi,
I m using SQL Server 2000's 120 days evaluation copy for testing my application. The prob i am facing is that whenever the rows in one of the table increses beyond 10K it gets cleared up.
I could not trace the reason for this. Plz some one suggest a possible solution for the same.
Thanks in advance.
View 1 Replies
View Related
Oct 19, 2006
trying to install sql server2005 on a windows 2003 server box.
getting msg below at the sql server . i looked at other posts on trying to uninstall SQL Native Access Client and norton antivirus. i could never find the snac on the add - remove programs and this server does not have a virus protection program yet.
here's the history of the installs on the server:
wanted to test a 2005 upgrade so:
1) installed sql server 2000 then sp4 then restored some databases to it - all OK
2) tried to upgrade to sql 2005 but ran into problems and left it at that.
had a disk drive crash on the d drive so lost the installs but not the operating system
when the drive was replaced, left alone for a while
then wanted to test a straight 2005 install
1) removed the broken 2005 attempt
2) removed the 2000
3) installed 2005 and got the error on the subject line:
TITLE: Microsoft SQL Server 2005 Setup
------------------------------
SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online.
i've gone through as many of the forums that i can and have tried several things - like uninstalling 2005 and installing pieces and parts but but nothing seems to work.
Thanks!
Dan <><
View 17 Replies
View Related
Feb 4, 2008
i am attempting to run phpbb using ms sql 2005 on the same box but get the following error during the setup
Could not connect to the database, see error message below.
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
i am not sure yet if this is an issue with sql, php or phpbb
php is installed in iis and has all the modules installed which the php msi installer supported. i can run php code but in this case when i'm running the install.php file for the phphbb setup, i fill in the values for the database and got that error
phpbb detects all the required server settings and sees that i have ms sql installed
any suggestions on what that error means or how to go about configuring odbc driver?
probably a simple issue. but i'm still new with sql stuff
View 1 Replies
View Related
May 22, 2005
Hi there,
I have decided to move all my transaction handling from asp.net to stored procedures in a SQL Server 2000 database. I know the database is capable of rolling back the transactions just like myTransaction.Rollback() in asp.net. But what about exceptions? In asp.net, I am used to doing the following:
<code>Try 'execute commands myTransaction.Commit()Catch ex As Exception Response.Write(ex.Message) myTransaction.Rollback()End Try</code>Will the database inform me of any exceptions (and their messages)? Do I need to put anything explicit in my stored procedure other than rollback transaction?
Any help is greatly appreciated
View 3 Replies
View Related
Oct 13, 2004
Hello,
I want to have a comparison about operation of a power builder application with sql server database, using sybase native driver and ole db driver.
View 2 Replies
View Related
Oct 28, 2004
Hi,
Please help share with me if you know the version compatibility matrix of Ms SQL Server, ODBC driver (sqlsrv32.dll), Driver Manager (odbc32.dll) and ODBC API spec. For instance, how can I know Ms SQL Server 2000 can work with which version of sqlsrv32.dll, a particular version of sqlsrv32.dll can work with which version of odbc32.dll and a certain version of sqlsrv32.dll/odbc32.dll conforms to which version of ODBC API spec (e.g. 3.5).
Any help will be appreciated.
Thanks,
vtluu.
View 1 Replies
View Related
Jul 20, 2005
Hi,Could some please tell me whether Microsoft provides Type 2 and Type 4jdbc driver ? For Type 4 MS providescom.microsoft.jdbc.sqlserver.SQLServerDriver driver. What is thecofiguration required for type 2 driver and what driver class filesrequired ?Its very urgent to me please reply.Ajay
View 1 Replies
View Related
Aug 13, 2003
Hi All,
Can anybody suggest me a website where I can find articles on Managing transactions with Sql server. Also a scenario where the transactions take place in a environment involving 2 different databases, Like the bank account and credit card transactions (specifically of 2 way kind)
Thanks
View 5 Replies
View Related
Apr 6, 2005
I have a web application with a shopping cart, how do I stop all the shopping cart transaction from going into the db log? Is this possible? These are are only transient data movements, and will never be need to to restore to, and they are cause log bloat. Or is there a better way to stop log bloat?
Thanks
View 2 Replies
View Related
Apr 17, 2000
Hi All,
How can we change connection properties in a DTS pkg with connection?
You can loop through the connection count but the connection ID is not static one.So can’t rely on that.
Is there another way of changing connection properties?
Thanks in Advance
Barath
View 4 Replies
View Related
Sep 5, 2000
Hi all....
I am currently designing a DTS Package to import data that is processed daily into a large database.
I have to design the package such that if any step fails when importing, I roll back the entire transaction.
I have designed the package with this in mind, checked "join transaction if present" and "rollback transaction on failure" in all of the workflows. I have also made all workflows serialized.
However, when I run the package, it fails on one of the data pumps with the error:
Transaction context in use by another session.
Any ideas?
Thank you,
Brian
View 2 Replies
View Related
Dec 1, 2000
I am replicating (finally!!) and on my publishers agent history I can see it says xx transactions with xx commands were delivered. (xx being the number)
Where can I look to see what the transactions or commands are?
Is there a place the system stores this information?
View 1 Replies
View Related
Oct 17, 2003
what is maximum limit of no. of transactions per sec. in sql server 2000
View 2 Replies
View Related
Sep 6, 2004
Is there a point to wrapping a single UPDATE or INSERT statement in an explicit TRANSACTION:
BEGIN TRANSACTION
INSERT INTO Table (...) VALUES (...)
COMMIT TRANSACTION
I understand ACID and concept of transactions. However, I thought they were only necessary for multi-statement operations. I'm maintaining code that does this and am wondering if this is necessary. Does SQL Server guarantee ACID for single statements? Are single UPDATE/INSERT statements prone to race condition like affects without using explicit transactions?
View 2 Replies
View Related
Apr 17, 2008
Are there any scenarios where an un-commited transaction would block further queries?
View 1 Replies
View Related
May 27, 2008
If you run the Begin Transaction code and then run a create such as an update query and you see that it effects the number of rows that you wanted it to effect is there a way to look at the actual data that changed before you Commit Transaction?
Thanks!
View 7 Replies
View Related
Jul 1, 2007
I have a table with around 240 columns and one of the column in the Table is the Inserttime ( DATETIME ) and I using a GETDATE() function in the stored Proc, when we insert data into the table. In the same Milli second 2007-06-27 09:32:58.303 , I have around 7600 records in the database. The Stored Proc is called for each Individual record and we don't bunch the transactions. Is this possible.
I did some bench marking on this server and I can insert only 700 - 800 records approx / sec on this particular table.
Thanks
View 6 Replies
View Related
Aug 1, 2007
I have a small database that I have been testing.I get an error about a transaction deadlock.The code is in stored procedures and I added transactions to the sp'sbut the error happened again.I wrapped the whole sp in just one transaction and I don't have anyindex on the tables.When I test just by running a program that sends 3 calls at a time itwill get a deadlocked transaction as I send 6 or 9 at a time.I am not sure how it can have a deadlocked transaction after I usedtransactions(begin and commit) in the sp's.Steve
View 4 Replies
View Related
Oct 8, 2007
I am working with transactions and use try catch to capture errors and in the event of an error i have to rollback the transaction. How can i perform this?, most of the errors which i forsee are either insertion of null values into non nullable columns or violation of Primary keys while inserting duplicates.
I started by coding the following way but it does not rollaback apparently the try catch does not work for above kind of errors..Can somebody help..
DECLARE @REPORTING_PERIOD VARCHAR(6)
BEGIN TRY
BEGIN TRANSACTION
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET @REPORTING_PERIOD =(Select REPORT_PERIOD_ID from dbo.T_REPORT_PERIOD where C_FLAG_ACTIVITY=1)
--Step 1
INSERT INTO [dbo].[T_COUNTRIES]
([C_COUNTRY]
,[LB_COUNTRY]
,[C_REGION]
,[FK_REPORTING_PERIOD])
SELECT [C_COUNTRY]
,[LB_COUNTRY]
,[C_REGION]
,@REPORTING_PERIOD
FROM [dbo].[IN_T_COUNTRIES]
--Step 2
INSERT INTO [dbo].[T_FLE]
([FK_P_FLE]
,[C_COSMOS]
,[LB_FLE]
,[C_PARETO]
,[C_OPCO_SCOPE]
,[C_LEVEL]
,[C_FLE_TYPE]
,[C_ACTIVITY]
,[F_MATERIAL]
,[C_MATERIAL_PRIORITY]
,[C_CALCULATION_METHOD]
,[F_CREDIT_RISK_MATERIALITY]
,[V_PARTICIPATION]
,[FK_REPORTING_PERIOD])
SELECT Null as [FK_P_FLE]
,[C_COSMOS]
,[LB_FLE]
,[C_PARETO]
,[C_OPCO_SCOPE]
,[C_LEVEL]
,[C_FLE_TYPE]
,@REPORTING_PERIOD
FROM [dbo].[IN_T_FLE]
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() as ErrorNumber,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
-- Test XACT_STATE for 1 or -1.
-- XACT_STATE = 0 means there is no transaction and
-- a commit or rollback operation would generate an error.
-- Test whether the transaction is uncommittable.
IF (XACT_STATE()) = -1
BEGIN
PRINT
N'The transaction is in an uncommittable state. ' +
'Rolling back transaction.'
ROLLBACK TRANSACTION;
END;
-- Test whether the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
PRINT
N'The transaction is committable. ' +
'Committing transaction.'
COMMIT TRANSACTION;
END;
END CATCH;
View 3 Replies
View Related
Nov 8, 2006
Hello All,
When i am working with Transactions i got one doubt.
If i am inserting any records into a table with primary key if a transaction is rolled back i am finding one primary ID is missing. Is it so.
View 3 Replies
View Related
Apr 2, 2007
I've been searching around and haven't found anything that simply states what I want to know.
I want to use a transaction within my CLR Stored Proc, to do so I've got System.Transactions referenced and I can access the current transaction via Transaction.Current.
My questions are
Will there always be a current transaction?
Do I need to create a new transaction if one doesn't already exist?
View 3 Replies
View Related
Sep 4, 2006
Hi world:
I have this issue:
I need to push rows from CE to SQL Server 2000 and after delete these rows of CE database only if all rows have been sent to SQL Server 2000.
I think the best is work with transactions. Since I know I can use transactions for this purpose, can anybody give me a link with push transaction examples ?
View 4 Replies
View Related
Dec 17, 2007
Hi all,
What does "Transactions/sec" counter in SQL 2005 under databases do in terms of performance. My counter shows almost 100% all the time in 4 terrbyte DB in superdome with many CPUs.
Which is the optimal value for this counter?
thanks in advance
Priw
View 1 Replies
View Related
Mar 31, 2008
Hi,
I am using C#.NET 2005, SQL Server 2000.
I have a few questions to ask. Firstly, can anybody help me by telling the difference between the code below and the same code with comments removed:
string localquery = "select bla bla"; SqlDataAdapter _sda; DataTable _dt; SqlCommand _cmd; bool isErrCatched = false; Exception ExcToThrow = new Exception(); using (SqlConnection _cn = NewConnection) // NewConnection returns a static SQLConnection object { _cn.Open(); _cmd = new SqlCommand(localquery, _cn); //SqlTransaction myTrans; //myTrans = _cn.BeginTransaction(); //_cmd.Transaction = myTrans; _dt = new DataTable(); _sda = new SqlDataAdapter(_cmd); try { _sda.Fill(_dt); _sda.Dispose(); //myTrans.Commit(); } catch (Exception exc) { //try { myTrans.Rollback(); } catch {} isErrCatched = true; ExcToThrow = exc; } finally { _cn.Close(); } _cmd.Dispose(); } _cmd = null; if (isErrCatched) throw ExcToThrow; return _dt;
My first question: Can there be a performance loss if I uncomment the lines about transaction usage? I mean, when I do this I start to get more timeouts.
My problem goes on. When I comment those lines and run a stress tool, I am getting "column X does not belong to table Y" errors. If those lines are not commented i am not getting this error, but I get timeout errors frequently. So, my second question: is there something wrong in my query or is there a bad coding practice I am following? Could someone offer a better and more robust sample for this code block?
By the way, connection pooling is on. And these errors are observed under high loads.
Thanks everybody.
View 3 Replies
View Related
Jun 7, 2006
I have transaction that will run on an hourly bases. I need to make sure that no one will start this transaction while it is running. I just need to know the system table that has all the transaction names in them and check to see if the transaction is running or not. what i am trying to do is not to have locks....just wanting to make sure that no one would run the same transaction twice. does anyone have any idea on how we can do this?
View 7 Replies
View Related
Mar 11, 2008
If you ask a .net developer he would likely say that he uses System.Transactions to manage transactions, a DBA on the other hand places transactions within the T-SQL of stored procedures.
What rules do architects and others, use when determining if the transactions should be placed in:
.net Middle Tier Components using System.Transactions for example.
T-SQL Stored Procedures.
Both .net Middle Tier Components and T-SQL Stored Procedures.
Thanks in advance,
Doug Holland
View 1 Replies
View Related
Nov 1, 2006
if i have a loop that runs through records in a dataset like thisfor(int i=0;i<ds.Tables[0].Rows.Count;++i) and in this loop i have several sql commands that run as a transaction in a try / catch block like : try{ // do stuff}catch{ trans.RollBack();}how can i keep the loop going even if a transaction failed. So the transaction works for each individual row. if row 100 fails for whatever i would like the loop to continue running, do i just simply remove the "throw" and it will continue looping ? my catch block currently looks like catch(Exception ex){transaction.Rollback();activity.Log("Transaction aborted, rolling back. Error Message: " + ex.Message + " Stack Trace: " + ex.StackTrace.ToString());throw; }thanks,mcm
View 4 Replies
View Related
Nov 3, 2006
I have a class that use a TransactionScope object for make make two operations into a database. The problem is that in a computer works well but in another I receive a error message: "MSDTC is not running in .SQLEXPRESS". I had watched the services in the computer and the "Microsoft Distributed Transaction Coordinator" is running.
Does somebody know what is happening?
Thankyou and sorry for my English.
View 2 Replies
View Related
