Can I Use Stored Procedures With PHP Via ODBC Bridge?
Jan 18, 2006
Hey Everyone,
I am a stored procedures newbie, so I wanted to ask if it is possible
to use them with PHP via an ODBC-ODBC bridge connected to a MS SQL
server. I am simply use the commands from the php odbc library to
connect and execute the SQL commands, I am trying to avoid building
dynamic queries, so stored proceddures seem to be the answer. I know
that there are only certain environments stored procedures can be used
within. Thank you for all of your help.
View 2 Replies
ADVERTISEMENT
Sep 14, 2006
i have a query that i need to extend but just dont have the knowledge to do so, so hoping someone here could help me
this is my current query:-
SELECT ES.PatientID,
DATEDIFF(day, EP.DateofBirth, GetDate())/365 AS Age,
CASE EP.SexCode WHEN 'M' THEN 'MALE' ELSE 'FEMALE'END AS Gender,
COUNT(H.HRDID)AS HRD,
ES.FourRegularDrugs,
isnull(ES.FourRegularDrugsNo,0) as NoOfDrugs,
ES.ReadmissSixMonths,
PCE.[Primary diagnosis]
FROM tblStudyServices ES
INNER JOIN tblPatient EP ON EP.PatientID = ES.PatientID
INNER JOIN PAS.dbo.[PAS Patient Details] PP ON PP.[PAS key] = EP.PatientInternalNumber
INNER JOIN PAS.dbo.[PAS Admission Details] [PAD] ON [PAD].[PAS key 1] = PP.[PAS key]
INNER JOIN PAS.dbo.[PAS Consultant Episode] PCE ON PCE.[PAS key 1] = PAD.[PAS key 1] AND PAD.[PAS key 2] = PCE.[PAS key 2]
LEFT JOIN tblStudyServicesHRD H ON H.ServicesStudyID = ES.ID
WHERE
PAD.[Method of Admission] = 'AE'
AND
(PP.[Death Indicator] <> 1 OR PP.[Death Indicator] IS NULL)
AND
(CONVERT(CHAR(10),PAD.[Discharge Date],103) > DATEADD(year, -1, CONVERT(CHAR(10),GETDATE(),103))
OR PAD.[Discharge Date] IS NULL)
AND
DATEDIFF(day, EP.DateofBirth, GetDate())/365 >= 18
AND
ES.Consent=0
the problem arises with the Discharge Date. which basically says that the discharge date must be within a year or is null
a patient may have 3 admissions within that year but i need to only bring back the latest discharge as this is the latest admission.
pas key 1 pas key 2 discharge date
1 1 01/Jan/2006
1 2 01/Mar/2006
1 3 01/Apr/2006
so i want to bring back only the record that has latest discharge date - 01/Apr/2006
i also need to accomodate the following scenario where the patient is still in hospital and discharge date is null - so therefore bring back the record with the null discharge date as this is the latest admission
pas key 1 pas key 2 discharge date
2 1 01/Jan/2006
2 2 01/Mar/2006
2 3 <NULL>
any help is greatly appreciated as ill be learning something new!!
Cheers,
Craig
View 16 Replies
View Related
Jan 6, 2007
when i am running a Stored Procedures, system always returns me error message below and Stored Procedures stops. please help
Microsoft OLE DB Provider for ODBC Drivers error
'80004005'
[Microsoft][ODBC SQL Server Driver] Received an unrecognized datatype 0 from
TDS data stream
sometime it returns error messge like, TDS Buffer Length Too Large
or
Unknown token received from SQL Server
or
Protocol error in TDS stream
or
Bad token from SQL Server: Datastream processing out of sync.
or
Invalid cursor state
or
TDS Buffer Link Too Large
or
Function sequence error
Many thanks, Please help, appreciated
View 4 Replies
View Related
Jul 23, 2005
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies
View Related
Jan 18, 2008
Hi,
is there a solution outside for getting of DB2 for OS/390 into SQL server?
I mean not replication or copying of data with flat files or an ETL tool, but a kind of integration of DB2 tables as they would be "normal" mssql tables.
Oracle has Transparent Gateways for os/z DB2 and many other non-Oracle databases, is there some similar for SQL Server?
Could it be realisable with an ODBC client for DB2?
Thanks
View 1 Replies
View Related
Sep 30, 2006
Hi,
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Thank you in advance for any help on this matter
View 1 Replies
View Related
Aug 26, 2005
I have a setup with a bridge table. There are about 5 different tables
on one side of the bridge (all with compatable PK columns) one of which
is called 'mobilesub', and one on the other side called
'allcostcenters'. The bridge table is called 'subaccountcostcenter'.
I can enter data for mobilesub in the bridge table. But then when I try
to enter the info into the bridge table for any of the other tables,
such as localsub, there is a conflict like this:
INSERT statement conflicted with TABLE FOREIGN KEY constraint
'FK_subaccountcostcenter_mobilesub'. The conflict occurred in database
'test1', table 'mobilesub'.
The statement has been terminated.
Is there some rule against using a bridge table that references several
different tables, and I'm just not aware of it. Because I've done
everything I can to make sure the info from the different tables don't
conflict . . .
The same error comes up if I do the localsub table first--in that case
the foriegn key messing me up is FK_subaccountcostcenter_localsub. So
it's not something with the individual tables.
I need experienced advice lol
Thanks
View 1 Replies
View Related
May 29, 2015
I have a bridge table used in my current design, the relation ship is FactTable -> DimRoom -> BridgeTable -> BedGrpoup. Fact table is joined with the Dim room dimension on room key, the bridge table maps values from the DimRoom and BedGroup dimension. The scenario I have is that the mapping between room and bed group can change during which I'm recording it in the effective date attribute in the bridge table.
Dimension usage on the cube is having a many to many relationship with bridge table.Example, if a fact occurred in room 100 which belonged to Bed Group A in Jan 2015 and then the room changed to Bedgroup b in feb 2015 and another fact happened in the room then I should have 1 count for jan in A and 1 in B for feb, I get that right when sliced by room, but when I slice by Month the cube does not honour time dim and displays both. I tried mapping the date from the bridge table to the fact as a regular relationship but that does not work either.
View 2 Replies
View Related
Nov 6, 2007
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
Thanks,
Jason
View 16 Replies
View Related
Apr 29, 2008
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
View 1 Replies
View Related
Jun 13, 2007
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
View 3 Replies
View Related
May 13, 2008
Greetings:
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
Thanks!
View 5 Replies
View Related
Apr 7, 2006
We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.
For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.
I can't update the sys.Objects or sys.Procedures views in 2005.
What effect will this flag (is_ms_shipped = 1) have on my stored procedures?
Can I move these out of "System Stored Procedures" and into "Stored Procedures"?
Thanks!
View 24 Replies
View Related
Apr 23, 2008
Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?
How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.
View 1 Replies
View Related
Mar 26, 2008
Hello
I'm start to work with SSIS.
We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:
SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'
EXEC @RETVAL = sp_executeSQL @SQLSTRING
How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)
I found a way but I think i'ts only a workaround:
1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)
Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?
Thanks for an early Answer
Chaepp
View 9 Replies
View Related
Jun 16, 2007
Hi,
Do you know how to write stored procedures inside another stored procedure in MS SQL.
Create procedure spMyProc inputData varchar(50)
AS
----- some logical
procedure spMyProc inputInsideData varchar(10)
AS
--- some logical
--- go
-------
View 5 Replies
View Related
May 8, 2008
I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.
The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?
Thanks in advance
John
View 5 Replies
View Related
Nov 1, 2007
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
View 1 Replies
View Related
Jan 17, 2006
Hey guys,I'm trying to get the column names from a table to which I'm connectedthrough OBDC. I don't want to use linked server. I get my data withOpenrowset.I found the SQLDescribeCol and SQLColumns ODBC functions.Can anybody tell me how to use those functions in a SQL Server storedprocedure?Thanks a lot
View 1 Replies
View Related
Sep 25, 2006
Dear all,
I am using SQL 2000 and would like to dynamically assign ODBC data source to transform data task. Do you have a stored procedure to perform read/write from/to ODBC data source? I would like to input data source and table name.
Thank you and Best regards,
Chaivat
View 5 Replies
View Related
May 15, 2008
i have created the folowing function but keep geting an error.
Only functions and extended stored procedures can be executed from within a function.
Why am i getting this error!
Create Function myDateAdd
(@buildd nvarchar(4), @avdate as nvarchar(25))
Returns nvarchar(25)
as
Begin
declare @ret nvarchar(25)
declare @sqlval as nvarchar(3000)
set @sqlval = 'select ''@ret'' = max(realday) from (
select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'
execute sp_executesql @sqlval
return @ret
end
View 3 Replies
View Related
Jun 25, 2001
Can anyone tell me if this can be done?
I'm using CR8 against SQL Server 7 and am trying to use a stored procedure as my data source.
Basically my boss would like to move all the code that is now client-side(formula fields, parameters, suppressions, etc.) to the server-side.
I connect by Database>ODBC and then choose my sp here.
I get the error: "There are no fields in the file"
All my sp is doing is accepting two parameters: a report type and a user name, and then generating a report based on this data.
I can post the exact sp but it is a pretty long IF THEN ELSE block.
I checked the Seagate site and it said to convert the database driver to the native driver;
I guess this would mean to connect by: Database>More Data Sources>Microsoft SQL Server
But we need to connect by ODBC since we don't want the popup for the Login to Database, Server, etc., to be entered by the client.
Can someone tell me if there is a way around this to connect by ODBC using stored procedures.
Thanks in Advance.
View 1 Replies
View Related
Jun 3, 2004
I have a stored procedure written in MS SQL Server2000 which takes argument(OUTPUT) as a cursor, and fills in the cursor with the record from the table.
I have to run this stored procedure from my C application program running in SUN OS2.9 with the help of ODBC calls.
Can anyone guide me through steps as to how to run the store procedure from my C program and receive records with the help of the cursor.
The store procedure is as follows
CREATE PROCEDURE testCursor @xyzCursor cursor varying OUT AS
DECLARE temp CURSOR
LOCAL
FOR SELECT * FROM table
OPEN temp
SET @xyzCursor=temp
RETURN(0)
GO
View 3 Replies
View Related
Mar 11, 2006
HiWe're trying to use call a stored procedure to update information in aremote Ingres database. We've linked the server, and can readinformation using SELECT * FROM OPENQUERY (..........), but we can'tfind a suitable syntax for executing a procedure.Using SELECT * FROM OPENQUERY and passing the EXEC statement in astring gives a message about not returning any columns - not surprisingas there aren't any, and trying to execute the procedure more directlyusing:-EXECUTE abrs..vipdba.ats_reader_pi0 ........Gives the errorCould not execute procedure 'ats_reader_pi0' on remote server 'abrs'.[OLE/DB provider returned message: Parameter type cannot be determinedfor at least one variant parameter.]Any bright ideas?Chloe
View 1 Replies
View Related
Apr 18, 2008
Hi,
I have a ms access application with a button. Clicking this button runs a sql server 2000 stored procedure. The stored procedure updates product information. When I click the button it updates the products fine. When the users (with lesser permissions than me) click the button, no error message is sent but the transactions are rolled back. Does anyone know why or how this could happen? Please let me know before I pull out the last strands of hair from my head.
Thank you,
Anand
View 4 Replies
View Related
Jun 17, 2006
hi
i need to use only one stored procedure and access many tablesso how write a stored procedure for that dohelp me looking forward for a reply to the earliest i am developing web page using asp.net using c# and sqlserver as backend
looking forward for a replygayathri
View 1 Replies
View Related
Dec 15, 2006
I am interested to know about stored procedures in Mssql .Can anyone please help me out.
Thanx in advance.
View 1 Replies
View Related
Feb 24, 2007
Hello I have two stored procedures
@ID INT
AS
SELECT (CASE WHEN NUM >= 10 THEN CAST(PAID AS FLOAT) / CAST(NUM AS FLOAT) * 100 WHEN NUM < 10 THEN 0 END) AS PER
FROM (SELECT (SELECT COUNT(*) AS Expr1
FROM Event_data AS D LEFT OUTER JOIN
Events AS E ON E.id = D.Event_id
WHERE (D.Transaction_type = 1) AND (D.Player_id = @ID)) AS NUM,
(SELECT COUNT(*) AS Expr1
FROM Event_data AS D LEFT OUTER JOIN
Events AS E ON E.id = D.Event_id
WHERE (D.Transaction_type = 1) AND (D.Transaction_value > 0) AND (D.Player_id = @ID)) AS PAID) AS X
and
@ID INT
AS
SELECT P.*,'/' + DBO.GETCHIPFOLDER(@ID) + '/' + ISNULL(P.PHOTO,'BLANK.GIF') AS PIC,ISNULL(
(SELECT SUM(TRANSACTION_VALUE)
FROM EVENT_DATA WHERE PLAYER_ID=@ID AND TRANSACTION_TYPE=1
GROUP BY PLAYER_ID),0) AS WINNINGS FROM PLAYERS P
undefined P
The first returns a percentage for player wins, the second gives me a photo and sums the player winnings
I would like to combine the results so I can get the percentage and wininngs in one query, in another matter all together I would like create a procedure like the first but instead of returning only one player, I would like to return the percentage for each player
Thanks in advance for any light you can shine on this.
View 4 Replies
View Related
Feb 25, 2007
I have a question about stored procedures, Is it better to use stored procedures even if I only use it once at my site? Or is it better to write the sql-part directly in the sqldatasource?
And am I forced to create two different stored procedures even if they are exactly the same except the "Where-part"?
Now I have around 40 stored procedures, and quite many of them looks the same except the where-part...
(Iam just a beginner with SQL)
View 2 Replies
View Related
May 2, 2007
Hello every one,
I m working in aspx 2.0 with sql server 2005, please tell me how can I create Stored Procedures for two or more tables not a single table(select,insert,update,delete please send me the queries which can help me in easy way I will very thankful to you
Thank you
View 5 Replies
View Related
Jun 19, 2007
I am learning to make a ASP web site and feel that if i can do it the harder way using some stored procedures instead of using multiple datasources on each page requiring that it might be better. So i am wondering what are these used for:DECLARE vs just entering "@param1 varchar(30)"When i use "DECLARE @rc int" i get the error "Incorrect syntax near DECLARE"How to return values to ASP page in Visual Studio 2005 How to use @@rowcount - doesn't seem to work for me?i tried using DECLARE @rc intSET @rc = @@rowcountWhen to use GO, BEGIN etcIf i want to use the variable only in the procedure, and not needed to be inputed, do i need to put it in the CREATE PROCEDURE (section)?Should i use my own stored procedures or VS2005 created ones using datasources? not really procedures but SQL, in SQL can i do like IF ELSE? if i use my own i cant use the Optimistic Concurrency right? and whats that?
View 1 Replies
View Related
Jul 5, 2007
Is there a website or somewhere i can go to read up on stored procedures??
View 2 Replies
View Related
Aug 2, 2007
cREATE PROCEDURE emp @val varchar(50)AS
declare @test varchar(50)declare @a varchar(50)set @a= @val + '_a'set @test = 'alter table dbo.rights_user add ' + @a + ' varchar(50) null'
execute(@test)
GO
this is my procedure...anyth wrong here...i can able to execute procedure only with 3 char..egexec emp 'na'exec emp 'hr10'--->wen i try like this cannot..showing datataype mis match
View 2 Replies
View Related
