Verity Style Files
Does anyone know if it is possible to populate the CF custom fields with the properties fields (title, keywords, comments, etc) from Word documents automatically when Verity indexes by changing the Verity style files? I am able to populate the native fields for these properties in Verity by modifying style.sfl and I am able to add them to the search by modifying style.dft but I have not found away to return the values using Cold Fusion 7. -Kore ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201356 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: SQL INSERT error on large amount of text
Are you using cfqueryparam? If not what are you doing to escape characters like ' ,;etc? Kore Peterson John Croney [EMAIL PROTECTED] To:CF-Talk [EMAIL PROTECTED] net.com cc: Subject:SQL INSERT error on large amount of text 04/07/2004 10:44 AM Please respond to cf-talk I am getting a problem in inserting data into a table. I have a form, which is made up of INPUT and TEXTAREA fields. When trying to insert into a memo field from a textarea through CFQUERY I get an error [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Visual FoxPro Driver]Command contains unrecognized phrase/keyword. This only happens when there is a lot of text in the textarea but it would work fine if a small amount of text is submitted. I was told to try cfinsert but after installing ColdFusion I can no longer amend my tables to turn on auto number and I have to now enter text in all fields due to the tables now set that all fields are required. I also find that after saving a form a couple of times DreamweaverMX start to take very long to save a couple lines of script. This is driving me up the wall. Can any one help? [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: SQL INSERT error on large amount of text
The first place I would look is to see if the DB is experiencing contention
or deadlock due to the MAX(ID) used for creating a unique id. It is very
possible to return the same ID to two different users if they make a
request at the same time. Personally I avoid this method of finding a
unique id.
Kore Peterson
John Croney
[EMAIL PROTECTED] To:CF-Talk [EMAIL PROTECTED]
net.com cc:
Subject:Re: SQL INSERT error on large amount of text
04/07/2004
01:08 PM
Please respond
to cf-talk
I got it to work. I useCFSQLTYPE=CF_SQL_LONGVARCHAR
Thanks for help.
Do you know what could be causeing the other problems?
No I was not, I was using ParagraphFormat. I tride the cfqueryparam
but that cut the text down to a few lines. I tride useing MaxLength
attribute but nothing happen. This is my script:
CFQUERY
NAME=NewID
datasource=VFP
dbtype=ODBC
SELECT max(id) as LastID from News
/CFQUERY
cfset Image=Trim(#Form.txtImage#)
cfset NextID=#NewID.LastID#+1
cfset OptionNews=trim(#Form.optNews#)
CFQUERYNAME=qry_News DATASOURCE=VFP
INSERT INTO News(id,ctopic ,mnews ,ddate ,cphoto ,mphototext ,itype ,
lnew ,backgroundimg)
VALUES(#NextID#,
CFQUERYPARAM VALUE=#Form.txtTopic#,
CFQUERYPARAM VALUE=#Form.txtMemo# MAXLENGTH=,
{04/06/04},
[#Image#],
CFQUERYPARAM VALUE=#Form.txtText#,
#OptionNews#,
.
f.,
[none]
)
/CFQUERY
Are you using cfqueryparam? If not what are you doing to escape
characters
like ' ,;etc?
Kore Peterson
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
Re: Create View problem
Is key, ID, or description a reserved word? Kore Peterson Andy Ousterhout To:CF-Talk [EMAIL PROTECTED] [EMAIL PROTECTED] cc: ess.com Subject:Create View problem 03/12/2004 03:16 PM Please respond to cf-talk I am trying to create a view in MS ACCESS and am getting the following error: [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in field definition. Here is the create statement.I've tested the select portion separatey and that works. CREATE TABLE vieInvoices ( CustomerKey, CustomerName, Invoice, InvoiceNumber, PeachtreeInvoiceNumber, InvoiceDate, ShipDate, ShippingMethod, ShipToAddressKey, ShipToName, ShipToAddr1, ShipToAddr2, ShipToCity, ShipToState, ShipToZip, ShipToCountry, OrderNumber, ShippingCharge, SalesTax, Comment, EnteredByKey, EnteredByName, ChangedByKey, ChangedByName, ChangedOn,Line, Quantity, Key, ID, Description, TypeKey, TypeName, Price ) AS select tabOrders.Customers_FK , vieCustomerList.CustomerName, IIf([PeachtreeInvoiceNumber] Is Null,[tabInvoices].[InvoiceNumber],[PeachtreeInvoiceNumber]), tabInvoices.InvoiceNumber, tabInvoices.PeachtreeInvoiceNumber, tabInvoices.InvoiceDate, tabInvoices.ShipDate, tabInvoices.ShippingMethod, tabOrders.ShipToAddressKey, tabOrders.ShipToName, tabOrders.ShipToAddr1, tabOrders.ShiptoAddr2, tabOrders.ShipToCity, tabOrders.ShipToState, tabOrders.ShipToZip, tabOrders.ShipToCountry, tabInvoices.OrderNumber, tabInvoices.ShippingCharge, tabInvoices.SalesTax, tabInvoices.Comment, tabInvoices.EnteredBy_FK, tabPeople.Name, tabInvoices.ChangedBy_FK, tabPeople_1.Name, tabInvoices.ChangedOn, tabInvoicedItems.Line, tabInvoicedItems.Quantity, tabInvoicedItems.Item_FK, tabInvoicedItems.ID, tabInvoicedItems.Description, IIf([ItemType_FK] Is Null,55,[ItemType_FK]), IIf([ItemType_FK] Is Null,'Other',[tabitemTypes].[TypeName]), tabInvoicedItems.Price FROM (tabPeople AS tabPeople_1 RIGHT JOIN (((tabInvoices LEFT JOIN tabPeople ON tabInvoices.EnteredBy_FK = tabPeople.Person_PK) INNER JOIN tabOrders ON tabInvoices.OrderNumber = tabOrders.OrderNumber) INNER JOIN vieCustomerList ON tabOrders.Customers_FK = vieCustomerList.CustomerKey) ON tabPeople_1.Person_PK = tabInvoices.ChangedBy_FK) LEFT JOIN (((tabInvoicedItems LEFT JOIN tabOrderedItems ON (tabInvoicedItems.Line = tabOrderedItems.Line) AND (tabInvoicedItems.OrderNumber = tabOrderedItems.OrderNumber)) LEFT JOIN tabItems ON tabInvoicedItems.Item_FK = tabItems.Item_PK) LEFT JOIN tabItemTypes ON tabItems.ItemType_FK = tabItemTypes.ItemType_PK) ON tabInvoices.InvoiceNumber = tabInvoicedItems.InvoiceNumber; [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: OT: migrating to SQL Server 2000
Did you update stats? -Kore Rob [EMAIL PROTECTED] To:CF-Talk [EMAIL PROTECTED] omcc: Subject:Re: OT:migrating to SQL Server 2000 03/01/2004 03:40 PM Please respond to cf-talk Did your indexes come over ok? There is a setting somewhere on 7 I believe when moving data if you do not say you want indexes to go they wont. That could impact performace (more on selects I would imagine). Anyway I have done at least 3 SQL7 - SQL2000 and there were a few minor problems (and very obscure syntax changes) but not much to write home about. On Mon, 2004-03-01 at 13:16, brobborb wrote: Is there a HUGE difference between SQL 7 and SQL 2000???Because 2000 is just running really slow and we can't figure out why.Is there a difference in syntax or something?On SQL 7 a multiple insert query (400 inserts) finished in 400ms.in 2000, same query, but in 7 SECONDS!!! i've google some information and it seem others have come across this problem, but we could not fix ours to work correctly.I hope this is fixable :( [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: OT: migrating to SQL Server 2000
Are you on a RAID 5? Could cause slower inserts (write) and fast selects (read). Does your 2000 server have more RAM than the 7 server? -Kore brobborb [EMAIL PROTECTED] To:CF-Talk [EMAIL PROTECTED] on.rr.comcc: Subject:Re: OT:migrating to SQL Server 2000 03/01/2004 03:45 PM Please respond to cf-talk I just did a little test.I created a regular table, with one field.I used the Query Analyzer to insert 500 rows.took about 4 seconds. In SQL 7, i did the same thing, except with 1000 rows.The results showed up in a split second. So far i know this (i hope i'm right!) It's not a coldfusion compatibility problem It's not a crappy computer problem (the one running 2000 is 2.8ghz, the one running SQL 7 is 900mhz) It's not an indexing problem No problems with SELECT statements.Works fine, works great. PROBLEMS with INSERT statements This is how simple the insert statement is (500 times)INSERT INTO testy(name) VALUES (TEST01) maybe there's something wrong with that insert thats causing SQL 2000 to act funny :( I hate it when I dont know what the problem is!You feel so helpless :( hahaI am sure this is fixable because it has to be maybe we'll have torequest a different CD! - Original Message - From: Rob To: CF-Talk Sent: Monday, March 01, 2004 3:40 PM Subject: Re: OT: migrating to SQL Server 2000 Did your indexes come over ok? There is a setting somewhere on 7 I believe when moving data if you do not say you want indexes to go they wont. That could impact performace (more on selects I would imagine). Anyway I have done at least 3 SQL7 - SQL2000 and there were a few minor problems (and very obscure syntax changes) but not much to write home about. On Mon, 2004-03-01 at 13:16, brobborb wrote: Is there a HUGE difference between SQL 7 and SQL 2000???Because 2000 is just running really slow and we can't figure out why.Is there a difference in syntax or something?On SQL 7 a multiple insert query (400 inserts) finished in 400ms.in 2000, same query, but in 7 SECONDS!!! i've google some information and it seem others have come across this problem, but we could not fix ours to work correctly.I hope this is fixable :( [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Way OT: Internet Backbone
Are you looking for something like this?? http://research.lumeta.com/ches/map/ Kore Peterson Eric [EMAIL PROTECTED] To:CF-Talk [EMAIL PROTECTED] lmpls.comcc: Subject:Way OT: Internet Backbone 02/03/2004 03:55 PM Please respond to cf-talk We have a way OT issue to work through and thought you folks here may be able to help... We have a critical client with absolute mission critical functionality that needs to be able to function even in emergency and regional outages.What I am starting to research for the first time on this scale is the proper distribution of data centers and redundant blah blah blah...and I am at the point of backbones and providers.Is there such an animal as a map that demonstrates higher concentration of backbone areas?Would a major ISP even give up that info...we are sensitive to security obviously but before I start that route I was wondering if there was some generalized source that just says, hey here works great and here in each region.I hate to just toss a pin at a major city centers as I believe we could identify better and more relevant criteria. If you happen to have experience in such requirements, even drop us a line. We don't want to recreate the darned wheel. Does that make sense?Thanks. Regards, Eric J. Hoffman Managing Partner Datastream Connexion, LLC 1.888.690.2893 [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: HELP!!! I have a SQL Call That I cannot Make work...
SELECT the MAX(bid_amount),product_id columns from auction_bids as a table then do a self join to with bid_amount,product_id and return member_ID then join to the other tables. You will probably want to do a join to the auction_bids as well to try to restrict the number of rows you will use. Assuming you are using MS SQL it might look something like this? SELECT ab.Bid_Amount as HighBid, ab.Member_ID as Bidder, ab.product_id, ai.reserve_price, ai.date_enddate as ENDDATE, ai.product_identifier, ai.member_ID as SELLER, ml.userid as BIDDER_NAME FROM (SELECT MAX(abt.Bid_Amount), abt.product_id, abw.Member_ID FROM Auction_Bids abt, Auction_Items ait, Auction_Bids abw WHERE ai.status = 'active' AND ait.date_enddate GETDATE() AND abt.bid_amount = ait.reserve_price AND ait.product_ID = abt.Product_ID AND abt.Bid_Amount = abw.Bid_Amount AND abt.product_id = abw.product_id) ab, Auction_Items ai, member_list ml WHERE ab.Member_ID = ml.member_ID ORDER BY ai.date_enddate, ai.product_ID, ab.bid_amount DESC This assumes you don't have a tie for a winning bid. Kore Peterson Michael Grove [EMAIL PROTECTED] To:CF-Talk [EMAIL PROTECTED] .com cc: Subject:HELP!!! I have a SQL Call That I cannot 01/22/2004Make work... 10:12 AM Please respond to cf-talk I have been working on this for days and can not figure it out. I think it would be somthing easy, but just cant get it. I have an auction site that I am working on. I have two tables, Auction_Item table and Auction_Bid table. The item table houses the product id, auction close date, the reserve price and the seller id. The bid table houses the product_id, bid amount and bidder id. What I want to do is show a list of auctions that have reached their close date and have a bid exceeding the reserve price. I can do this. The trickey part is, I dont want to see all of the bids exceeding the reserve. Only the highest bid. here is what I have so far cfquery name=Auction_Sale datasource=#DatasourceName# dbtype=ODBC SELECT ab.Bid_Amount as HighBid, ab.Member_ID as Bidder, ab.product_id, ai.reserve_price, ai.date_enddate as ENDDATE, ai.product_identifier, ai.member_ID as SELLER, ml.userid as BIDDER_NAME FROM Auction_Bids ab, Auction_Items ai, member_list ml WHERE ai.status = 'active' AND ai.date_enddate #createODBCdate(now())# AND ab.bid_amount = ai.reserve_price AND ai.product_ID = ab.Product_ID AND ab.Member_ID = ml.member_ID ORDER BY ai.date_enddate, ai.product_ID, ab.bid_amount DESC/cfquery What am I missing? [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: HELP!!! I have a SQL Call That I cannot Make work...
I forgot the add the join for AI. WHERE ab.Member_ID = ml.member_ID AND ai.product_ID = ab.Product_ID Kore Peterson Database Development Specialist SEH - Minneapolis 612.758.6739 [EMAIL PROTECTED] nc.comTo:CF-Talk [EMAIL PROTECTED] cc: 01/22/2004Subject:Re: HELP!!! I have a SQL Call That I 11:53 AMcannot Make work... Please respond to cf-talk SELECT the MAX(bid_amount),product_id columns from auction_bids as a table then do a self join to with bid_amount,product_id and return member_ID then join to the other tables. You will probably want to do a join to the auction_bids as well to try to restrict the number of rows you will use. Assuming you are using MS SQL it might look something like this? SELECT ab.Bid_Amount as HighBid, ab.Member_ID as Bidder, ab.product_id, ai.reserve_price, ai.date_enddate as ENDDATE, ai.product_identifier, ai.member_ID as SELLER, ml.userid as BIDDER_NAME FROM (SELECT MAX(abt.Bid_Amount), abt.product_id, abw.Member_ID FROM Auction_Bids abt, Auction_Items ait, Auction_Bids abw WHERE ai.status = 'active' AND ait.date_enddate GETDATE() AND abt.bid_amount = ait.reserve_price AND ait.product_ID = abt.Product_ID AND abt.Bid_Amount = abw.Bid_Amount AND abt.product_id = abw.product_id) ab, Auction_Items ai, member_list ml WHERE ab.Member_ID = ml.member_ID ORDER BY ai.date_enddate, ai.product_ID, ab.bid_amount DESC This assumes you don't have a tie for a winning bid. Kore Peterson Michael Grove [EMAIL PROTECTED] To:CF-Talk [EMAIL PROTECTED] .com cc: Subject:HELP!!! I have a SQL Call That I cannot 01/22/2004Make work... 10:12 AM Please respond to cf-talk I have been working on this for days and can not figure it out. I think it would be somthing easy, but just cant get it. I have an auction site that I am working on. I have two tables, Auction_Item table and Auction_Bid table. The item table houses the product id, auction close date, the reserve price and the seller id. The bid table houses the product_id, bid amount and bidder id. What I want to do is show a list of auctions that have reached their close date and have a bid exceeding the reserve price. I can do this. The trickey part is, I dont want to see all of the bids exceeding the reserve. Only the highest bid. here is what I have so far cfquery name=Auction_Sale datasource=#DatasourceName# dbtype=ODBC SELECT ab.Bid_Amount as HighBid, ab.Member_ID as Bidder, ab.product_id, ai.reserve_price, ai.date_enddate as ENDDATE, ai.product_identifier, ai.member_ID as SELLER, ml.userid as BIDDER_NAME FROM Auction_Bids ab, Auction_Items ai, member_list ml WHERE ai.status = 'active' AND ai.date_enddate #createODBCdate(now())# AND ab.bid_amount = ai.reserve_price AND ai.product_ID = ab.Product_ID AND ab.Member_ID = ml.member_ID ORDER BY ai.date_enddate, ai.product_ID, ab.bid_amount DESC/cfquery What am I missing? [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
CFGRID
What is peoples favorite Cold Fusion based alternatives to CFGRID?Please feel free to supply some feedback based on personal experience as well as URLs. TIA, -Kore [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: CFGRID
Is there anyway to access GridMonger without having flash installed? Seems like MM site treats me poorly if I do not have flash installed and I do not want flash on my machine at this time. Heavy sigh. I hate the feeling of big brother pushing me where I don't want to go:( My current need would be low volume low row count. CFGRID can do the job but I would like to look at some alternatives. Most likely something I can quickly add to an administration section of a site. I am most interested in ease of use and time to market over efficiency. Kore Peterson Matt RobertsonTo:CF-Talk [EMAIL PROTECTED] [EMAIL PROTECTED] cc: base.comSubject:Re: CFGRID 12/29/2003 05:07 PM Please respond to cf-talk Well, I'm pretty well prejudiced because I wrote the thing, but I use it all the time and it works great for me and a lot of other folks... GridMonger.Its a free download at the MM Exchange. If you just want a grid you can set up fast and get results from, it remains my favorite tool.In 5 minutes you can have a searchable, sortable add/edit/delete grid. If you want efficient code then you'd be better off writing something dedicated to your specific job.Not knowing db platform, field counts, names, types, etc. in advance means you have to throw in a lot of conditional logic and evaluate()'s, which costs you.Not something you'd ordinarily notice, but also not something you want to put under heavy load without a lot of advance testing. HtH, -- --- Matt Robertson,[EMAIL PROTECTED] MSB Designs, Inc. http://mysecretbase.com --- -- [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: SQL Problem
Here is a long shot..
Have you double checked to be sure you don't have an ambiguous table name
or are connecting to the wrong datasource (IE a development datasource)?
Kore Peterson
Mickael
[EMAIL PROTECTED] To:CF-Talk [EMAIL PROTECTED]
netcc:
Subject:Re: SQL Problem
12/16/2003
12:59 PM
Please respond
to cf-talk
But it is not the date field that is causing the issue, it the what I am
using as the issue number
- Original Message -
From: Dwayne Cole
To: CF-Talk
Sent: Tuesday, December 16, 2003 1:18 PM
Subject: RE: SQL Problem
Try putting # signs around the date field.I had this problem before
after my service provider switch from ODBC to OLDBC.
Dwayne Cole, MS in MIS, MBA
Florida AM University
Certified Advanced ColdFusion Developer
850-591-0212
It can truly be said that nothing happens until there is vision. But it
is equally true that a vision with no underlying sense of purpose, no
calling, is just a good idea - all sound and fury, signifying nothing.
The Fifth Discipline - Peter Senge
-- Original Message --
From: Tangorre, Michael [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
Date:Tue, 16 Dec 2003 12:55:30 -0500
Try Trimming your vars especially since they were text before you
converted them. If it was char type text there might be some padding
that
you don't see..
-Original Message-
From: Mickael [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 16, 2003 12:56 PM
To: CF-Talk
Subject: Re: SQL Problem
Hi Michael,
Sorry for the double post but I thought there was something up with the
list, I posted the first one two hours ago didn't see it on the list and
sure enough as soon as I post again the both show up
- Original Message -
From: Tangorre, Michael
To: CF-Talk
Sent: Tuesday, December 16, 2003 12:48 PM
Subject: RE: SQL Problem
No need to post your question twice.. be patient, someone will respond
if
they know the answer.:-)
-Original Message-
From: Mickael [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 16, 2003 12:42 PM
To: CF-Talk
Subject: SQL Problem
Hello All,
I am having problems with an update statement that used to work fine
in
Access with CFMX.I have an issue tracking app that used to have the
issue
numbers stored as TEXT, I converted the column to Number a little
while
ago
and it worked fine.Recently users have been having problems updating
the
records.
My update statement is pretty simple
Update IssueRegister
SET IssueStatus = '#form.IssueStatus#',
Response = '#form.Response#',
ResponseUserId = '#session.activeuser.USER_NAME#',
DateofResponse = #CreateODBCdate(now())#
Where IssueNumber = #form.issuenumber#
The error that I get from CF is
The search key was not found in any record.
But if I look in the error window it show my statment as follows
Update IssueRegister_qr SET IssueStatus = 'Closed', Response =
'completed',
ResponseUserId = 'Mickael Elmalem1', DateofResponse = {d '2003-12-16'}
Where
IssueNumber = 1110
This look correct to me.So I tried running it in access's query
window
and
I get the same error, yet when I try to search the column for
issuenumber
1110 Access finds it through its own search utility.
This has me stumped.Any ideas anyone.
Thanks
Mike
_
_
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
Re: Oracle Sequences
You might need to increase your Sequence Number Cache size You can read about it on OTN. http://download-west.oracle.com/docs/cd/A87860_01/doc/index.htm ---cut--- When designing applications for Oracle Parallel Server, use sequence numbers whenever possible. To maximize the use of sequences, each instance's cache must be large enough to accommodate the sequences. The default cache size holds 20 sequence numbers. To increase this, for example to hold 200, use this syntax: ALTER SEQUENCE SEQUENCE_NAME CACHE 200; ---cut--- ---cut--- If sequences are insufficiently cached or not cached at all, severe performance problems may result with an increase in service times. This may also result in reduced scalability. If you experience performance problems, examine statistics in the V$SYSTEM_EVENT view as described below to determine whether the problem is due to the use of Oracle sequences: A problem with sequences appears in V$SYSTEM_EVENT as extended average wait times for row cache locks in the range of a few hundred milliseconds. The proportion of time waited for row cache locks of the total time waited for non-idle events will be very high. For the DC_SEQUENCES parameter, the ratio of DLM_CONFLICTS to DLM_REQUESTS will be very high. If this ratio exceeds 10 to 15% and the row cache lock wait time is a significant portion of the total wait time, then it is likely that the deterioration of service times is due to insufficiently cached sequences. ---cut--- Kore Peterson [EMAIL PROTECTED] u.ac.uk To:CF-Talk [EMAIL PROTECTED] cc: 12/15/2003Subject:Oracle Sequences 06:43 AM Please respond to cf-talk Anybody ever had problems with the request for a sequence timing out? I thought the database would have just queued them up?Why would CF keep hanging on them? I've put a lock around the offending sequence to see if that solves it but was wondering if there was any other possible problems out there? This sequence is getting hit pretty heavily at the moment Cheers -- -dc[ cf5, ora8.1.7, iis5 ] [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: The Value of CFQUERYPARAM
Oracle does a hash on the statement and if it does not have a match in memory then it will recompile the statement. Any change including whitespace or change of case can cause a recompile. The use of bind variables can prevent a recompile. If you choose not to use bind variables on a statement that is used often and you run into database performance problems expect a call from your DBA Kore Peterson Shawn McKee [EMAIL PROTECTED] To:CF-Talk [EMAIL PROTECTED] nd.comcc: Subject:RE: The Value of CFQUERYPARAM 12/08/2003 01:28 PM Please respond to cf-talk I just talked to my Oracle DBA and if the query is all constants SELECT M FROM S WHERE W = 'huh' it will get parsed once and put in the cache. SELECT M FROM S WHERE W = '#url.k#' Gets parsedevery time the contents of url.k changes SELECT M FROM S WHERE W = ? ? = 'huh' Is the solution for this because the QUERYPARAM is a bind variable that the DB can handle and knows no to parse again. Shawn McKee -Original Message- From: Tom Kitta [mailto:[EMAIL PROTECTED] Sent: Monday, December 08, 2003 12:58 PM To: CF-Talk Subject: RE: The Value of CFQUERYPARAM As far as I know, yes, it does recompile every time. And it is quite intuitive, just look at the debug of what is sent to the DB server for something like: SELECT M FROM S WHERE W = '#url.k#' and SELECT M FROM S WHERE W = 'huh' Assuming url.k is 'huh' you get the same thing sent to the DB server. However, if you use cfqueryparam what is sent: SELECT M FROM S WHERE W = ? ? = 'huh' So the DB compiles (only once) the query and puts in 'huh' as the argument. Summarizing it doesn't matter whatever 'huh' is static or from a variable, it is still treated the same way. Also, it would help to remember that #your_var_here# causes the value of the var to be printed and it becomes static text. TK -Original Message- From: Ian Skinner [mailto:[EMAIL PROTECTED] Sent: Monday, December 08, 2003 1:47 PM To: CF-Talk Subject: RE: The Value of CFQUERYPARAM Even though the value is static and unchanging, the query will be recompiled every time?This seems to be a bit counter intuitive to me, but it is basically what I'm trying to confirm. -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA -Original Message- From: Tom Kitta [mailto:[EMAIL PROTECTED] Sent: Monday, December 08, 2003 10:43 AM To: CF-Talk Subject: RE: The Value of CFQUERYPARAM Use CFQUERYPARAM as the DB engine will only compile you query once and hopefully cache it. Otherwise it will re-compile every time. TK -Original Message- From: Ian Skinner [mailto:[EMAIL PROTECTED] Sent: Monday, December 08, 2003 1:36 PM To: CF-Talk Subject: The Value of CFQUERYPARAM When writing a select statement, if I have a part of a WHERE clause that will be static, always the same value, and this value is not passed in with a variable or constant, is there any inherent value in cfqueryParam ... tags? An Example: WHERE STATUS = cfqueryParam value=M ... or WHERE STATUS = 'M' Is there any real advantage of one of these forms over the other? Just to repeat, these are hard coded, unchanging values NOT passed in with variables or constants.I understand the value of the cfqueryParam tags in conjunction with passing in data with variables, especially data from user forms. -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA Confidentiality Notice:This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Generating Primary Key in Oracle (part 2)
An Oracle sequence is not guaranteed to include every number so you would never want to use to for invoice numbers if you need to account for every value. An example of when a sequence number might be lost is if a transaction grabs a number and then does a rollback instead of committing. Using one sequence can cause a resource contention, a Hot Spot, issue if you have too many things trying to access the sequence at similar times. If you are worried about a B-tree index that is adding nearly sequential data from becoming out of balance or lopsided you can use a reverse key index in Oracle. -Kore Peterson Deanna Schneider [EMAIL PROTECTED] To:CF-Talk [EMAIL PROTECTED] .uwex.educc: Subject:Re: Generating Primary Key in Oracle (part 2) 12/05/2003 11:03 AM Please respond to cf-talk Blah! That would be an issue. But, it's not one that I'll ever have to deal with, as we don't do accounting type stuff. But, good to know for future reference. An example of where a common seq would cause problems is if the seq number becomes an invoice number, and the audit trail requires that every number in a sequence be accounted for.This is pretty outmoded thinking now, but some auditors still stick to the rule - I want to see every invoice,IN SEQUENCE, filed in those binders there to verify they are all accounted for. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: Pushing Timeout Alert to Client.
Could you set a meta refresh with a time that is a little smaller than your session timeout and have the refresh handle the warningyou want? -Kore Ian Skinner [EMAIL PROTECTED] To:CF-Talk [EMAIL PROTECTED] ource.org cc: Subject:Pushing Timeout Alert to Client. 11/17/2003 02:26 PM Please respond to cf-talk This may not best be done with a CF solution, so I'm open to any suggestions, the simpler the better. BACKGROUND We have a web application working with sensitive HIPPA information.As one could imagine, we don't really want to keep this information available for very long, so the application session times out, if inactive for a set time. If the session has timed out while open in the browser, any new action by the user causes the application to go to the start.All this is good as far as it goes, but it would be much nicer to the user, if they receive a warning of the pending and/or past expiration of the application. The main reason for this request, is that the application has some fairly complex data collection forms.It could be annoying, if a user opens one of these form, gets interrupted by the phone, comes back after the session has expired, enters all the data in the open form in their browser, submits the form to be told by the server: sorry your session has timed out, then being sent back to the start losing all their work. REQUEST I'm looking for ways I might be able to push, or in someway deliver a warning to the user?My bank's online banking website does this, and while I hate the bank itself, this is a nice feature.I'm just not sure how they do it.How they have a popup window open when the session is about to expire/has expired?Would this just be done with _javascript_?How else might this be done?Some kind of dynamic scheduled task?Something built in java and/or jsp?Something one of you has already built and can just give me. *smile* Thanks -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA Confidentiality Notice:This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
RE: Pushing Timeout Alert to Client.
How about setting the meta refresh greater than your session timeout and then have a page come up that tells the user of their current fate? Added benefit would be any sensitive info in the browser would be cleared by the refresh. -Kore Ian Skinner [EMAIL PROTECTED] To:CF-Talk [EMAIL PROTECTED] ource.org cc: Subject:RE: Pushing Timeout Alert to Client. 11/17/2003 03:01 PM Please respond to cf-talk That's an interesting Idea, and I will file it away in my head, but it's not really the solution to my current problem.We want the session to time out so that's good.I would just like to warn a user who has left the browser open that after it's timed out, any data you try to submit now is going to be lost, so you might as well start over first. Thanks -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA -Original Message- From: Robyn Follen [mailto:[EMAIL PROTECTED] Sent: Monday, November 17, 2003 12:37 PM To: CF-Talk Subject: RE: Pushing Timeout Alert to Client. While this doesn't quite answer your question as to an alert, it might help with your timeout issue. If you have a lot of big forms that the user has to fill out, and you're worried about timeouts while the user is filling out the form, you could have a hidden frame in the form, and an onFocus event on the fields that the user has to fill out.The function called each time the field gets focus could reload the hidden frame's page, which would then keep the session alive while the user fills out the form. For instance: Input Type=Text Name=txtMyTextField Value= > -Original Message- From: Ian Skinner [mailto:[EMAIL PROTECTED] Sent: Monday, November 17, 2003 3:26 PM To: CF-Talk Subject: Pushing Timeout Alert to Client. This may not best be done with a CF solution, so I'm open to any suggestions, the simpler the better. BACKGROUND We have a web application working with sensitive HIPPA information.As one could imagine, we don't really want to keep this information available for very long, so the application session times out, if inactive for a set time. If the session has timed out while open in the browser, any new action by the user causes the application to go to the start.All this is good as far as it goes, but it would be much nicer to the user, if they receive a warning of the pending and/or past expiration of the application. The main reason for this request, is that the application has some fairly complex data collection forms.It could be annoying, if a user opens one of these form, gets interrupted by the phone, comes back after the session has expired, enters all the data in the open form in their browser, submits the form to be told by the server: sorry your session has timed out, then being sent back to the start losing all their work. REQUEST I'm looking for ways I might be able to push, or in someway deliver a warning to the user?My bank's online banking website does this, and while I hate the bank itself, this is a nice feature.I'm just not sure how they do it.How they have a popup window open when the session is about to expire/has expired?Would this just be done with _javascript_?How else might this be done?Some kind of dynamic scheduled task?Something built in java and/or jsp?Something one of you has already built and can just give me. *smile* Thanks -- Ian Skinner Web Programmer BloodSource www.BloodSource.org Sacramento, CA Confidentiality Notice:This message including any attachments is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and delete any copies of this message. _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
Re: OT: SQL padding then to file question
Use the replicate function with the len function.
Something like below where 10 is the field size needed..
select personid + REPLICATE(' ', 10-len(personid) ),
Kore Peterson
Eric Creese
[EMAIL PROTECTED] To:CF-Talk [EMAIL PROTECTED]
rgcc:
Subject:OT: SQL padding then to file question
09/29/2003
02:41 PM
Please respond
to cf-talk
I have been given a task to get some data and put it into a file format for
another company with starting and end positions for each column. For
example this file requires the following
namewidth startend
unique id 10110
Indicator 1
title41215
first name301645
mid initial 14646
last name 354781
...and so on.
Here is my query
select personid,companyid
='I',perprefix,perfname,left(permname,1),perlname,addrline1+' '
+addrline2,city,state,left(zip,5),country = 'USA'
from peopleroleup
where country ='United States'
and companyidis null
How do I pad this out? I am using SQL2k Query Analyzer.
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
RE: Debugging a Runaway CF5 Server?
Is the database also running hard? Is there anything special happening on your database at the same time. (backup?) Have you checked the event viewer to see if there are any errors reported at the same time by any other processes or applications? Can you use your web server log to isolate what the possible cfm page(s) could be causing the problem? Kore Peterson Database Development Specialist SEH - Minneapolis 612.758.6739 Ben Koshy benlist@w3med To: CF-Talk [EMAIL PROTECTED] ia.net cc: Subject: RE: Debugging a Runaway CF5 Server? 10/31/2002 03:39 AM Please respond to cf-talk Never mind the PCODE Errors, I've tracked them to a MM Article because of the CFERROR type=monitor tag I was using...but the rest is all valid... :D -Original Message- From: Ben Koshy [mailto:benlist;w3media.net] Sent: Thursday, October 31, 2002 1:15 AM To: CF-Talk Subject: Debugging a Runaway CF5 Server? I've got a dedicated server running a fairly busy application and about once or twice a day, something is causing the Cold Fusion Server (Version 5) to max out at 100% for about 30 minutes and then settle back down again. While its maxxed out (P4 2Ghz Server), the server pages respond only after 40-60 seconds making the website almost inoperable. A simple restart of the CF Service solves the issue. I'm wondering while the server is maxxing , is there a way to look at what each thread is handling at that time to see which template caused the runaway problem? I realize this also could be a bug that's being triggered similar to the zero-byte email bug, but since the server resolves itself after 20-30 minutes, I doubt its that. I've thrown on some CFERROR tags and am cleaning up any rogue code/errors that seem to be cropping up, but so far the maxx out beast hasn't reared its ugly head. Recently I noticed the odd PCODE error popping up occassionaly on different templates if that's useful at all (see below)...on all the servers I run we don't get these types of errors. Does anyone have set best practice at hunting down these sorts of runaway server problems? Most of the time we can narrow it down to an infinite loop but its odd that the CFServer doesn't kill this runaway process after the 60 second execution limit so I'm thinking it could be a known bug. Any help is appreciated. :D PCODE Errors: = Diagnostics: unknown exception condition PCodeRuntimeContextImp::executeSQLTagCFQuery::endTag The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (45:2) to (45:105) in the template file D:\WWW.blah.COM\PERSONAL\SELL_HISTORY.CFM. Diagnostics: unknown exception condition PCodeRuntimeContextImp::executeSQLTagCFQuery::endTag The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (49:2) to (49:107) in the template file D:\WWW.blah.COM\SEARCH\..\INCLUDES\GLOBAL_VARS.CFM. Diagnostics: unknown exception condition PCodeRuntimeContextImp::executeSQLTagCFQuery::endTag The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (204:3) to (204:107) in the template file D:\WWW.blah.COM\FUNCTIONS\PRNT_LISTING.CFM. Diagnostics: unknown exception condition PCodeRuntimeContextImp::executeSQLTagCFQuery::endTag The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (84:2) to (84:120) in the template file D:\WWW.blah.COM\FUNCTIONS\AUCTION_COMPLETE.CFM. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
Re: Really big file manipulation
Your SQL server memory is mostly likely climbing trying to keep track of rollback information. Try using a nologged bulk copy when you create and load the temp table. If possible use update select or insert select for moving the data. Commit your transaction(s) more often if possible. Use cfqueryparm for all changing values (variables) in your SQL statments. Kore Peterson Database Development Specialist SEH - Minneapolis 612.758.6739 Randell B Adkins To: CF-Talk [EMAIL PROTECTED] [EMAIL PROTECTED] cc: OV Subject: Re: Really big file manipulation 10/21/2002 01:25 PM Please respond to cf-talk I at one time had a 500,000 byte file to import 5 days a week. But that had to go to one table. Sorry my recommendations is on MSSQL DTS. [EMAIL PROTECTED] 10/21/02 02:23PM Hey all - I have got a 76,000+ line data file (ZipUSA comma delim file) that I need to break into smaller tables in my DB. This needs to happen at least monthly, so I wrote a cfm template to automate the process. The problem I am having is speed and server load. I have tried using the following methods, and am wondering if anybody has any suggestions (apart from using SQL's DTS) Platform info: CF 5 on Win2k, IIS 5 writing to MSSQL 2000 The tables I am putting everything into are a state table, county table w/state reference, city table w/county reference, location/district table w/city reference and zip table w/district reference 1) Create temp SQL table, loop over file, write to temp table, query temp table, loop over query, and using conditional logic - break into table structure defined above, and drop temp table. This is the shortest method taking 20 minutes or so. The problem is that my SQL service's memory usage spirals out of control and when the template is finished, it never returns to normal. I have to restart the service to right it. 2) Same as above, but converts the data file into a CF function-made query and then do a query of query. (This is necessary to re-order data for break-down step) This one takes forever (an hour on the slowest run) and both the CF services and the SQL service take over the machine. 3) Read the data file, write a new one, then read that data file and loop over the lines, breaking it into the table structure above. This one also takes forever. Any suggestions at all on how to streamline the process would be really helpful, Thanks in advance Bill Henderson [EMAIL PROTECTED] ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: Really big file manipulation
Do you know which phase of the process is eating the memory? Kore Peterson Database Development Specialist SEH - Minneapolis 612.758.6739 Bill Henderson To: CF-Talk [EMAIL PROTECTED] cf-talk@uutil cc: ity.com Subject: RE: Really big file manipulation 10/21/2002 03:44 PM Please respond to cf-talk Tried these - I thought not committing was the problem at first, but there was no appreciable change. Same with the nologged bulk, this lightened the load, but not much. And I always use queryparams. Bill Henderson [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:kpeterson;sehinc.com] Sent: Monday, October 21, 2002 1:25 PM To: CF-Talk Subject: Re: Really big file manipulation Your SQL server memory is mostly likely climbing trying to keep track of rollback information. Try using a nologged bulk copy when you create and load the temp table. If possible use update select or insert select for moving the data. Commit your transaction(s) more often if possible. Use cfqueryparm for all changing values (variables) in your SQL statments. Kore Peterson Database Development Specialist SEH - Minneapolis 612.758.6739 Randell B Adkins To: CF-Talk cf- [EMAIL PROTECTED] [EMAIL PROTECTED] cc: OV Subject: Re: Really big file manipulation 10/21/2002 01:25 PM Please respond to cf-talk I at one time had a 500,000 byte file to import 5 days a week. But that had to go to one table. Sorry my recommendations is on MSSQL DTS. [EMAIL PROTECTED] 10/21/02 02:23PM Hey all - I have got a 76,000+ line data file (ZipUSA comma delim file) that I need to break into smaller tables in my DB. This needs to happen at least monthly, so I wrote a cfm template to automate the process. The problem I am having is speed and server load. I have tried using the following methods, and am wondering if anybody has any suggestions (apart from using SQL's DTS) Platform info: CF 5 on Win2k, IIS 5 writing to MSSQL 2000 The tables I am putting everything into are a state table, county table w/state reference, city table w/county reference, location/district table w/city reference and zip table w/district reference 1) Create temp SQL table, loop over file, write to temp table, query temp table, loop over query, and using conditional logic - break into table structure defined above, and drop temp table. This is the shortest method taking 20 minutes or so. The problem is that my SQL service's memory usage spirals out of control and when the template is finished, it never returns to normal. I have to restart the service to right it. 2) Same as above, but converts the data file into a CF function-made query and then do a query of query. (This is necessary to re-order data for break-down step) This one takes forever (an hour on the slowest run) and both the CF services and the SQL service take over the machine. 3) Read the data file, write a new one, then read that data file and loop over the lines, breaking it into the table structure above. This one also takes forever. Any suggestions at all on how to streamline the process would be really helpful, Thanks in advance Bill Henderson [EMAIL PROTECTED] ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the
RE: Really big file manipulation
When you are looping through and doing your inserts is the loop inside the cfquery tag or outside? Kore Peterson Database Development Specialist SEH - Minneapolis 612.758.6739 Bill Henderson To: CF-Talk [EMAIL PROTECTED] cf-talk@uutil cc: ity.com Subject: RE: Really big file manipulation 10/21/2002 04:32 PM Please respond to cf-talk As far as I can tell, if I am relying on SQL (as opposed to CF) for the bulk of the work, the insert into the temp table seems to be the hog. I am using cfflush to send progress reports to the browser, and when it hits the inserts it starts to bog down. The memory spike takes place somewhere between the 6,000th and 12,000th record, and stays spiked until I restart the MSSQLSERVER service. Bill Henderson [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:kpeterson;sehinc.com] Sent: Monday, October 21, 2002 1:52 PM To: CF-Talk Subject: RE: Really big file manipulation Do you know which phase of the process is eating the memory? Kore Peterson Database Development Specialist SEH - Minneapolis 612.758.6739 Bill Henderson To: CF-Talk cf- [EMAIL PROTECTED] cf-talk@uutil cc: ity.com Subject: RE: Really big file manipulation 10/21/2002 03:44 PM Please respond to cf-talk Tried these - I thought not committing was the problem at first, but there was no appreciable change. Same with the nologged bulk, this lightened the load, but not much. And I always use queryparams. Bill Henderson [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:kpeterson;sehinc.com] Sent: Monday, October 21, 2002 1:25 PM To: CF-Talk Subject: Re: Really big file manipulation Your SQL server memory is mostly likely climbing trying to keep track of rollback information. Try using a nologged bulk copy when you create and load the temp table. If possible use update select or insert select for moving the data. Commit your transaction(s) more often if possible. Use cfqueryparm for all changing values (variables) in your SQL statments. Kore Peterson Database Development Specialist SEH - Minneapolis 612.758.6739 Randell B Adkins To: CF-Talk cf- [EMAIL PROTECTED] [EMAIL PROTECTED] cc: OV Subject: Re: Really big file manipulation 10/21/2002 01:25 PM Please respond to cf-talk I at one time had a 500,000 byte file to import 5 days a week. But that had to go to one table. Sorry my recommendations is on MSSQL DTS. [EMAIL PROTECTED] 10/21/02 02:23PM Hey all - I have got a 76,000+ line data file (ZipUSA comma delim file) that I need to break into smaller tables in my DB. This needs to happen at least monthly, so I wrote a cfm template to automate the process. The problem I am having is speed and server load. I have tried using the following methods, and am wondering if anybody has any suggestions (apart from using SQL's DTS) Platform info: CF 5 on Win2k, IIS 5 writing to MSSQL 2000 The tables I am putting everything into are a state table, county table w/state reference, city table w/county reference, location/district table w/city reference and zip table w/district reference 1) Create temp SQL table, loop over file, write to temp table, query temp table, loop over query, and using conditional logic - break into table structure
Re: Oracle Error no descriptor for this position
Oracle's Metalink has an article about a problem with Cold Fusion, Oracle and Bullseye conflict causing this error. Doc ID: 171452.995 Kore Peterson Database Development Specialist SEH - Minneapolis 612.758.6739 Chris Norloff To: CF-Talk [EMAIL PROTECTED] cnorloff@norl cc: off.com Subject: Oracle Error no descriptor for this position 10/18/2002 08:39 AM Please respond to cf-talk We're getting the Oracle error ORA-24334: no descriptor for this position with the following query. Not having much luck finding out what's going on, or getting info from the MM CF forum, CF-Talk archives, or Google. Any ideas? (CF 4.5.1 SP2 on Solaris 8, Oracle 8i db, with 8.1.7 client) thanks, Chris Norloff SELECT job_id, provider_code, NVL(financial_no, '0') financial_no, mailing_facility, job_title, process_category, class, local_permit_no, permit_type, total_piece, total_weight, cqt_total_pieces, cqt_weight, location_zip, owner_name, gca_version FROM m_job_summary WHERE job_id = 'QD2AES_1' AND provider_code = 'QUAD' AND mailing_facility = '22203-1553' Oracle's description of the error: ORA-24334 no descriptor for this position Cause: The application is trying to get a descriptor from a handle for an illegal position. Action: Check the position number. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm
Re: supposedly simple query
Is message or thread a reserved/keyword for your database? Try using cfqueryparam. Kore Peterson Database Development Specialist SEH - Minneapolis 612.758.6739 Jeremy Bunton To: CF-Talk [EMAIL PROTECTED] jeremy@thinkc cc: reate.com Subject: supposedly simple query 10/15/2002 03:23 PM Please respond to cf-talk I have this VERY simple query like cfquery name=getmessages datasource=mydns select * from message where thread = #threadnumber# /cfquery where #threadnumber# is set by cfset threadnumber=#trim(URL.thread)# coming from a cflocation url=messages.cfm?thread=cfoutput#formthread#/cfoutput If I hard code in a number no problem it works but for some odd reason using the above I get [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'thread = 45'. Both fields are intergers. What ya think. Jeremy ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: Time Required for Updating DB
I think you could do this in a single qry using UPDATE... FROM. Microsoft example UPDATE titles SET ytd_sales = titles.ytd_sales + sales.qty FROM titles, sales WHERE titles.title_id = sales.title_id AND sales.ord_date = (SELECT MAX(sales.ord_date) FROM sales) yours might be something like SET Product.price= New.newprice FROM New,Product WHERE New.dealerPartNo = Product.dealerPartNo Kore Peterson Database Development Specialist SEH - Minneapolis 612.758.6739 Mosh Teitelbaum mosh.teitelbaum@ To: CF-Talk [EMAIL PROTECTED] evoch.com cc: Subject: RE: Time Required for Updating DB 10/08/2002 08:12 AM Please respond to cf-talk Jeez... I forgot to include the CFQUERY tags. I keep telling myself, no more technical emails when I'm exhausted, no more technical emails when I'm exhausted. You'd think I'd finally start listening to myself 8^). Anyway, just wrap the SQL query in an appropriate CFQUERY tag and you should be fine. It's still probably going to take several minutes to perform (you *ARE* running 20,001 queries). Good luck. -- Mosh Teitelbaum evoch, LLC Tel: (301) 625-9191 Fax: (301) 933-3651 Email: [EMAIL PROTECTED] WWW: http://www.evoch.com/ -Original Message- From: Srimanta [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 08, 2002 3:40 AM To: CF-Talk Subject: Re: Time Required for Updating DB Mosh, Tried the codes. Still no luck. The query runs for about 8 to 10 minutes and then the screen shows hundreds of lines of the SQL and no data is updated. Srimanta - Original Message - From: Mosh Teitelbaum [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] Sent: Tuesday, October 08, 2002 8:03 PM Subject: RE: Time Required for Updating DB Thanks Mosh Yup. 8^) I am trying to update price field in Table named Product with values from field newprice in Table named New. The primary key in both the tables is dealerpart no (Text) which is unique. Note: All the records need to be updated CFQUERY name=UpdatePrice Datasource=XYZ Select New.newprice,New.dealerpartno,New.publisher, Product.publisher,Product.dealerpartno,Product.price From New,Product /CFQUERY cfloop query=UpdatePrice Update Product SET Product.price= #UpdatePrice.newprice# WHERE Product.dealerpartno = #UpdatePrice.dealerpartno# /cfloop Note: There are 2 records in both the tables. Srimanta: There are a few problems here. The first is with your SELECT query: Select New.newprice, New.dealerpartno, New.publisher, Product.publisher, Product.dealerpartno, Product.price From New, Product This query (aside from producing two dealerpartno columns) does not specify how to join the two tables. It therefore creates (if I remember the terminology correctly) a Cartesian Product of the two tables. This is, essentially, a result set of every single possible combination of the records from each table. For example, if your 2 tables had the following data: New.newPrice Product.price = a 1 b 2 the result set would be: newPrice price = a 1 a 2 b 1 b 2 So, since both of your tables have 20,000 records, your SELECT query is returning a result set with 20,000 x 20,000 records (400,000,000 records). Looping 400 million times is probably what's taking so long 8^). Instead, your query should look something like: SELECT New.newprice, New.dealerpartno AS newDealerPartNo, New.publisher AS newPublisher, Product.publisher AS oldPublisher, Product.dealerpartno AS oldDealerPartNo, Product.price FROM New, Product WHERE New.dealerPartNo = Product.dealerPartNo This query will produce a result set
Re: SQL question
TRY TO_CHAR(date_col, 'MONTH') Kore Peterson Database Development Specialist SEH - Minneapolis 612.758.6739 Gyrus [EMAIL PROTECTED] To: CF-Talk [EMAIL PROTECTED] reeuk.comcc: Subject: Re: SQL question 10/08/2002 09:07 AM Please respond to cf-talk - Original Message - From: Thane Sherrington [EMAIL PROTECTED] Is there a way to use a Select query to get just the month out of a date in a SQL query? I have a database that has a date field in it (so 04-10-02) and I want to select all the records from a specific month. --- Flicking through my reference books, SQL Server and MySQL both have a month() function. Not sure about Access, check MSDN. Or - and this is something that has reduced my CF-Talk posts no end - try it out! ;-) - Gyrus - [EMAIL PROTECTED] work: http://www.tengai.co.uk play: http://www.norlonto.net - PGP key available ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: Randomizing Query Results
oracle uses the DBMS_RANDOM package to create random numbers. Kore Peterson Database Development Specialist SEH - Minneapolis 612.758.6739 David Jones djones@applie To: CF-Talk [EMAIL PROTECTED] dx.com cc: Subject: RE: Randomizing Query Results 10/08/2002 08:54 AM Please respond to cf-talk What database does this work on? I should have specified what database I am using. I am developing for Oracle 8i. Thanks, Dave -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED]] Sent: Monday, October 07, 2002 10:42 AM To: CF-Talk Subject: Re: Randomizing Query Results David Jones wrote: What is the best way to randomize the results of a CF query? Offload it to the database: SELECT* FROM table ORDER BYRandom() Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
RE: Random Stuff
Can you use the random function on the database instead? Kore Peterson Database Development Specialist SEH - Minneapolis 612.758.6739 Mosh Teitelbaum mosh.teitelbaum@ To: CF-Talk [EMAIL PROTECTED] evoch.com cc: Subject: RE: Random Stuff 10/02/2002 10:15 AM Please respond to cf-talk How about CreateUUID()? -- Mosh Teitelbaum evoch, LLC Tel: (301) 625-9191 Fax: (301) 933-3651 Email: [EMAIL PROTECTED] WWW: http://www.evoch.com/ -Original Message- From: Adrian Lynch [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 02, 2002 10:42 AM To: CF-Talk Subject: Random Stuff I have a udf that creates a random string that works great. The problem is, looping over this for multiple inserts into a db causes problems because it loops to quickly. Someone mentioned that all the random functions in CF use the current time/date as a seed. The way around this is to either slow down the loop, or to append something on the end of the string, in this case the index of the loop. Does anyone have a way to generate random numbers as fast as I need them? Adrian Lynch Thoughtbubble Ltd -- United Kingdom http://www.thoughtbubble.net Ph: +44 (0) 20 7387 8890 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s) . Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0) 20 7387 8890 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/index.cfm?sidebar=listsbody=lists/cf_talk FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
Re: CF Engine Posting to SQL TWICE
Does the web server log show one hit or two?
Kore Peterson
Database Development Specialist
SEH - Minneapolis
612.758.6739
Vance Duke
vance.duke@imagi To: CF-Talk
[EMAIL PROTECTED]
nuity.com cc:
Subject: CF Engine Posting to SQL
TWICE
10/01/2002 12:20
PM
Please respond to
cf-talk
Ok, I have been doing CF now for over 5 years and I have never seen this
happen before and would like a fresh pair of eyes.
Form submits Category and Placement. Action page run the queries and
then stops. The debug information shows the queries and the information
inserted only 1 time. But the database, received 2 rows of the exact
same data. A SQL Trace shows the queries running 2 times as if they
were in a loop, approx 20 ms apart.
The code reads:
cfquery name=Renumber datasource=#DSN#
update ArticleCategory
set Placement = Placement+1
where Placement = '#Placement#'
/cfquery
cfquery name=InsertArticleCategory datasource=#DSN#
insert into ArticleCategory(ArticleCategory, Placement)
values('#form.ArticleCategory#', '#form.Placement#')
/cfquery
The debug shows:
Renumber (Records=0, Time=0ms)
SQL =
update ArticleCategory
set Placement = Placement+1
where Placement = '1'
InsertArticleCategory (Records=0, Time=10ms)
SQL =
insert into ArticleCategory(ArticleCategory, Placement)
values('test3', '1')
But the Database shows:
ID CategoryPlacement
18 test3 2
19 test3 1
It even renumber the first entry correctly by setting the
placement=placement+1
The code is only running once. Why is the CF engine processing it
twice. In addition, this does not happen 100% of the time. Rather, it
is about 50-60% of the time.
If I run the debug queries in Query Analyzer, it only does the operation
1 time. Therefore I am pointing at the CF engine.
We are running CF 5 on Win2k SP 2, IIS.
Any help would be appreciated.
__
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Need help with a SQL statement
What Column(s) is the Primary key?
From Microsoft Docs
[INTO] Is an optional keyword that can be used between and the target
table.
Kore Peterson
Database Development Specialist
SEH - Minneapolis
612.758.6739
Tony Weeg
tony@navtrak. To: CF-Talk [EMAIL PROTECTED]
net cc:
Subject: RE: Need help with a SQL
statement
09/27/2002
02:35 PM
Please respond
to cf-talk
is it a null value.
they show up as dupes too!!
.tony
Tony Weeg
Senior Web Developer
Information System Design
Navtrak, Inc.
Fleet Management Solutions
www.navtrak.net
410.548.2337
-Original Message-
From: Mitko Gerensky-Greene [mailto:[EMAIL PROTECTED]]
Sent: Friday, September 27, 2002 3:33 PM
To: CF-Talk
Subject: OT: Need help with a SQL statement
Hello,
Could anybody help me with a simple SQL statement?
I am trying to insert a new row into a SQL Server 7.0 database using the
Enterprise manager.
The statement is:
INSERT p_pha_plan
(state_code, pha_code, fiscal_year, version_number,
status_code, pdf_file_name, submission_date_time,
approval_date_time, HA_Code)
VALUES ('TX', 253, 2002, 1, 10, 'tx253fy2002v01.pdf',
7 / 7 / 2002, NULL, 'tx253')
The error I am getting is:
[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY
constraint 'PK_p_phaplan'. Cannot inser duplicate key in object
'p_pha_plan'.
There is not a second entry like the one I am trying to insert. Why,
then, SQL Server does not allow the insert?
Thanks in advance,
Mitko
__
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Need help with a SQL statement
SELECT * FROM p_pha_plan WHERE state_code = 'TX' AND pha_code = '253' AND
fiscal_year= '2002' AND version_number = 1 Yields no rows?
Also on your insert you should use ' around data that is of type CHAR
INSERT p_pha_plan
(state_code, pha_code, fiscal_year, version_number,
status_code, pdf_file_name, submission_date_time,
approval_date_time, HA_Code)
VALUES ('TX', '253', '2002', 1, 10, 'tx253fy2002v01.pdf',
7 / 7 / 2002, NULL, 'tx253')
Kore Peterson
Database Development Specialist
SEH - Minneapolis
612.758.6739
Mitko
Gerensky-Green To: CF-Talk [EMAIL PROTECTED]
e cc:
mitko@stranni Subject: RE: Need help with a SQL
statement
k.net
09/27/2002
03:20 PM
Please respond
to cf-talk
Oops, the version_number field should be smallint, 2, i.e.
key: version_number, smallint, 2
Mitko
-- Original Message --
From: Mitko Gerensky-Greene [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
Date: Fri, 27 Sep 2002 15:16:08 -0500
I have not designed this database and I am only guessing on some matters.
It is a very strangely (or poorly?) designed DB since many of its tables do
not have unique ID field...
The table I am trying to insert into is called p_pha_plan.
It has 4 key fields and 4 that are not key:
key: state_code, char, 2
key: pha_code, char, 3
key: fiscal_year, char, 4
key: version_number, char, smallint
status_code, char, 2
pdf_file_name, char, 30
submission_date_time, datetime, 8
approval_date_time, datetime, 8
HA_code, char, 5
The only fields allowing NULL are approval_date_time and HA_code
Hope this explains it a bit more...
Thanks,
Mitko
-- Original Message --
From: [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
Date: Fri, 27 Sep 2002 14:40:39 -0500
What Column(s) is the Primary key?
From Microsoft Docs
[INTO] Is an optional keyword that can be used between and the target
table.
Kore Peterson
Database Development Specialist
SEH - Minneapolis
612.758.6739
Tony Weeg
tony@navtrak. To: CF-Talk
[EMAIL PROTECTED]
net cc:
Subject: RE: Need help with
a SQL statement
09/27/2002
02:35 PM
Please respond
to cf-talk
is it a null value.
they show up as dupes too!!
.tony
Tony Weeg
Senior Web Developer
Information System Design
Navtrak, Inc.
Fleet Management Solutions
www.navtrak.net
410.548.2337
-Original Message-
From: Mitko Gerensky-Greene [mailto:[EMAIL PROTECTED]]
Sent: Friday, September 27, 2002 3:33 PM
To: CF-Talk
Subject: OT: Need help with a SQL statement
Hello,
Could anybody help me with a simple SQL statement?
I am trying to insert a new row into a SQL Server 7.0 database using the
Enterprise manager.
The statement is:
INSERT p_pha_plan
(state_code, pha_code, fiscal_year, version_number,
status_code, pdf_file_name, submission_date_time,
approval_date_time, HA_Code)
VALUES ('TX', 253, 2002, 1, 10, 'tx253fy2002v01.pdf',
7 / 7 / 2002, NULL, 'tx253')
The error I am getting is:
[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY
constraint 'PK_p_phaplan'. Cannot inser duplicate key in object
'p_pha_plan'.
There is not a second entry like the one I am trying to insert. Why,
then, SQL Server does not allow the insert?
Thanks in advance,
Mitko
__
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics.
