Re: [ceodbc-users] Problems Calling a Stored Procedure with cursor.callproc or cursor.execdirect
Brought to you by:
atuining
From: Jon L. <jo...@dm...> - 2011-06-23 15:47:28
|
I am also using the "Sql Server" driver. I looked at this url http://ceodbc.sourceforge.net/html/cursor.html and read the documentation. Can you please explain the arguments/syntax for the execdirect ()? All that is on the site is "Cursor.execdirect(statement)". Can you please give me an example of how you called the stored procedure using this method, so I can try it here on my end? Thanks Jon -----Original Message----- From: Anthony Tuininga [mailto:ant...@gm...] Sent: Wednesday, June 22, 2011 1:26 PM To: jo...@dm... Cc: ceo...@li... Subject: Re: [ceodbc-users] Problems Calling a Stored Procedure with cursor.callproc or cursor.execdirect I have tried this directly and all appears to be well. Note that I am using the "Sql Server" driver and not the "SqlServer" driver -- not the lack of a space. I'm not sure if this has any bearing or not but thought I would mention it. If you can narrow this down further that would be helpful. Thanks. See the documentation for help on the execdirect() method. If that is insufficent, let me know. Thanks. Anthony On Thu, Jun 16, 2011 at 10:26 AM, Jon Ludena <jo...@dm...> wrote: > I am having a problem calling a stored procedure in sql server using > the cursor.callproc(). The procedure runs but exits prematurely. If I > run this procedure directly from sql I get the desired results (in > this case 278 rows inserted into the desired table) but when I call > from python I only get 31 rows and then the program ends. I tried > using the cursor.execdirect() but I keep getting Database Errors > (improper syntax). Does anyone know the required arguments/parameters > for the exedirect() call or what might be causing the stored procedure to exit prematurely? > > Here is the python code: > > > > import ceODBC > connect=ceODBC.connect("""DSN=mydatabase;SERVER=xxx.xxx.x.xxx; > DRIVER={SQLServer};UID=user;PWD=password""", autocommit= True) cursor > = connect.cursor() cursor.execute(""" > CREATE TABLE mydatabase.dbo.Vision_TempTable > ( > CustId bigint, > PayProcId int, > WebUserId bigint, > SubTypeId bigint, > PayAmt decimal(18,2), > Paydate datetime, > PayType varchar(1), > DateCreated datetime, > PayStatus varchar(1), > Account varchar(30), > V_Account varchar(30) > )""")#Create a temp table which will be used in > the SP insert > connect.commit() > f=open('F:/clients/UTA/Vision/Data/ExternalPay_Data/lbox.txt') > CUST_ID=44 > PAYPROCID=4 > SUBTYPE_ID=64 > WEBUSER_ID=2432 > PAYTYPE='C' > PAYSTATUS='J' > for line in f: #parse a text file and insert values into temp table I > just created > lineLength=len(line.strip()) > if lineLength>=49: > visionAccount=int(line[10:17]) > visionAccount=str(visionAccount) > recipientID=line[17:29] > invoiceAmount=line[31:39] > invoiceAmount=float(invoiceAmount) > pmtType=line[39:41]#make sure it will always be ck > pmtDate=line[45:47]+'/'+line[47:49]+'/'+line[41:45] > cursor.execute("""INSERT INTO mydatabase.dbo.Vision_TempTable > > (CustId,PayProcId,WebUserId,SubTypeId,PayAmt,Paydate, > > PayType,DateCreated,PayStatus,Account,V_Account) > VALUES > (?,?,?,?,?,?,?,GETDATE(),?,?,?)""", > CUST_ID,PAYPROCID,WEBUSER_ID,SUBTYPE_ID, > > invoiceAmount,pmtDate,PAYTYPE,PAYSTATUS,recipientID,visionAccount) > > connect.commit() > cursor.callproc("mydatabase.dbo.VisionExternalPMTS")# turn over > control to SP > > f.close() > connect.close() > print('Done') > > > > Thanks > > > > Jon Ludena > > > > > > ---------------------------------------------------------------------- > -------- EditLive Enterprise is the world's most technically advanced > content authoring tool. Experience the power of Track Changes, Inline > Image Editing and ensure content is compliant with Accessibility > Checking. > http://p.sf.net/sfu/ephox-dev2dev > _______________________________________________ > ceodbc-users mailing list > ceo...@li... > https://lists.sourceforge.net/lists/listinfo/ceodbc-users > > |