Firebird SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed Forums > Databases > Firebird SQL Development
Reload this Page

Dynamic SQL Error

Discuss Dynamic SQL Error in the Firebird SQL Development forum on Dev Shed.
Dynamic SQL Error Firebird SQL Development forum discussing administration, Firebird SQL syntax, or other Firebird SQL-related topics. Firebird is the evolution of Borland's Interbase product.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Page 1 of 2 1 2 >
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old July 15th, 2004, 10:10 AM
jccorner jccorner is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: May 2004
Posts: 45 jccorner User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 24 m
Reputation Power: 5
Dynamic SQL Error
I get an error when using a FbDataAdapter to fill a dataset. The code is just a duplicate of SqlDataAdapter code I've used for MSSQL. The error is:

Dynamic SQL Error
SQL error code = -204
Table Unknown
MasterName
At line 1, column 15.

Any ideas?? Greatly appreciative.

This might be useful to someone:

Code:
dbstr = "User=SYSDBA;Password=masterkey;Database=Masterdb.fdb;Datasource=localhost;Port=3050;"
dbstr &= "Dialect=3;Charset=NONE;Role=;Connection lifetime=15;Pooling=true;Packet Size=8192"

FBConn = New FbConnection(dbstr)
FBConn.Open()
FBDS.Clear()
FBAdapter = New FbDataAdapter("Select * From MasterName", FBConn)
FBAdapter.Fill(FBDS, "MasterName") <- Error on this line.
FBConn.Close()
Reply With Quote
  #2  
Old July 15th, 2004, 12:00 PM
jhlee24 jhlee24 is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: May 2004
Location: New Jersey, U.S.A.
Posts: 40 jhlee24 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 5 h 23 m 6 sec
Reputation Power: 5
Send a message via MSN to jhlee24 Send a message via Yahoo to jhlee24
Thumbs up Which dialect?
Dear all,

See which dialect you used for your database.
If it was set for Dialect3 AND case-sensitve, you have to use double-quoatation marks around the table and fields' names.

(Example)
"select * from \"MyTable\""

Hope that this would be helpful to you.

- JH
Reply With Quote
  #3  
Old July 15th, 2004, 12:35 PM
jccorner jccorner is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: May 2004
Posts: 45 jccorner User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 24 m
Reputation Power: 5
The dialect is set to 1, I changed it to 3 and attempted to change the code, but that was unsuccessful.
Reply With Quote
  #4  
Old July 15th, 2004, 01:01 PM
jhlee24 jhlee24 is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: May 2004
Location: New Jersey, U.S.A.
Posts: 40 jhlee24 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 5 h 23 m 6 sec
Reputation Power: 5
Send a message via MSN to jhlee24 Send a message via Yahoo to jhlee24
Smile Connection Strgin
If the dialect is set to 1, then your connection string also has "dialect = 1" instead of having "dialect = 3."

Try to run the same query statement from SQL editor to check if your statement is really valid.

- JH
Reply With Quote
  #5  
Old July 15th, 2004, 01:06 PM
jccorner jccorner is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: May 2004
Posts: 45 jccorner User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 24 m
Reputation Power: 5
Actually, I changed the statement:

dbstr = "User=SYSDBA;Password=masterkey;Database=LAPTOP:Arrestdb.fdb;...

and now I'm getting an error on the .Fill statement saying that the feature is not supported. Quick question, was I supposed to register the .DLL I downloaded, because I just referenced the .dll from where it was installed to??
Reply With Quote
  #6  
Old July 15th, 2004, 01:28 PM
jhlee24 jhlee24 is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: May 2004
Location: New Jersey, U.S.A.
Posts: 40 jhlee24 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 5 h 23 m 6 sec
Reputation Power: 5
Send a message via MSN to jhlee24 Send a message via Yahoo to jhlee24
Smile Ok....
Hi,

Ok..... let me see if you configured correctly first.
Please check the followings first.

1. .NET framework version (1.0 or 1.1)
=> there are two different versions of .NET data provider for FireBird and the one for 1.0 won't work on framework version 1.1. In this case, you will have a runtime error just like "~ not supported."

2. Make sure you add a reference to your project

3. Instead of using DataAdapter, try to use DataReader and see if it works.

If you have any more questions or issues, FEEL FREE to post yours here. I will be more than happy to answer your questions as long as I have solutions to yours.

Have a great afternoon!!!!

- JH
Reply With Quote
  #7  
Old July 15th, 2004, 01:29 PM
jhlee24 jhlee24 is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: May 2004
Location: New Jersey, U.S.A.
Posts: 40 jhlee24 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 5 h 23 m 6 sec
Reputation Power: 5
Send a message via MSN to jhlee24 Send a message via Yahoo to jhlee24
Smile full path
Also, try to give a full path & database name if it is located in your local laptop or pc.

Just avoid using any computer name or identifier at this time.

- JH
Reply With Quote
  #8  
Old July 15th, 2004, 02:01 PM
jccorner jccorner is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: May 2004
Posts: 45 jccorner User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 24 m
Reputation Power: 5
Well I greatly appreciate it.

I downloaded and installed the correct version, I'm running 1.1 and that's the provider I'm working with.

I referenced the project from where it is installed, in the Firebird folder in Program Files. I didn't know if I had to register the .dlls.

Yeah, I tried a datareader thinking it was just the fill command. But when I perform the executereader command, it gives me the same error: Feature not supported.

If I make the statement look like: database=C:\WINNT\system32\Masterdb.fdb from database=LAPTOP:Masterdb.fdb I get the old error, table unknown. I even tried database=LAPTOP:C:\WINNT\system32\Masterdb.fdb.

I know there has to be someone who got it to work in VB.net using the .Net provider.
Reply With Quote
  #9  
Old July 15th, 2004, 02:11 PM
jhlee24 jhlee24 is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: May 2004
Location: New Jersey, U.S.A.
Posts: 40 jhlee24 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 5 h 23 m 6 sec
Reputation Power: 5
Send a message via MSN to jhlee24 Send a message via Yahoo to jhlee24
Smile another try....
< Your code: >
FBAdapter = New FbDataAdapter("Select * From MasterName", FBConn)

< Change to: >
FBAdapter = New FbDataAdapter("Select * From \"MasterName\"", FBConn)

See you database if this table name is identical (case-sensitively). Normally, the table name is stored all UPPER CASE. You have to use the same case too.....

Make sure database connection "open" method works fine too.
Try to debug line by line by "Stepping into."
Reply With Quote
  #10  
Old July 15th, 2004, 02:31 PM
jccorner jccorner is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: May 2004
Posts: 45 jccorner User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 24 m
Reputation Power: 5
Quote:
Originally Posted by jhlee24
< Change to: >
FBAdapter = New FbDataAdapter("Select * From \"MasterName\"", FBConn)


That line of code does not compile. Tried that earlier.

Quote:
See you database if this table name is identical (case-sensitively). Normally, the table name is stored all UPPER CASE. You have to use the same case too.....


Yes, they are identical in case.

Quote:
Make sure database connection "open" method works fine too.
Try to debug line by line by "Stepping into."


Open statement executes without any hitches so I take it I'm connecting to the db correctly.

Have you been able to connect to the firebird server?? What language are you using?? Again, I appreciate the time and assistance.
Reply With Quote
  #11  
Old July 15th, 2004, 02:35 PM
jhlee24 jhlee24 is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: May 2004
Location: New Jersey, U.S.A.
Posts: 40 jhlee24 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 5 h 23 m 6 sec
Reputation Power: 5
Send a message via MSN to jhlee24 Send a message via Yahoo to jhlee24
Thumbs up Heavily using FB now...
Yes,

I am using FB very heavily with MSSQL2000 server. Also, I am using C# not VB.NET. See how ASP.NET works with FB server at www.freemywork.com.... It is my asp.net sample site.

- JH
Reply With Quote
  #12  
Old July 15th, 2004, 03:33 PM
jccorner jccorner is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: May 2004
Posts: 45 jccorner User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 24 m
Reputation Power: 5
Are you using the .Net provider provided by IBPhoenix?? I saw an OleDb provider in another post and was thinking about trying that to test.

I guess this is just one of the fallbacks in using new technology, not a lot of documentation or examples. I tried the IBPhoenix website but was unable to find anything. Is there anyplace where I might find some documentation??
Reply With Quote
  #13  
Old July 15th, 2004, 03:46 PM
jhlee24 jhlee24 is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: May 2004
Location: New Jersey, U.S.A.
Posts: 40 jhlee24 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 5 h 23 m 6 sec
Reputation Power: 5
Send a message via MSN to jhlee24 Send a message via Yahoo to jhlee24
Smile Yes..
Yes, I am using the general .NET data provider posted on FireBird website.

I still suspect that the error regarding to "table not found" has something to do with case-sensitivity issue or double-quotation issue. I used to have the similar problem before....

Once you can finally run one statement through .NET provider, it will be like coping and pasting the block of source codes for handling database from that point.

Surely, you will get it to work.....
If you want, I will give you a partial source code of my sample asp.net page even though it was programmed in C#.

- JH
Reply With Quote
  #14  
Old July 15th, 2004, 08:32 PM
jccorner jccorner is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: May 2004
Posts: 45 jccorner User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 24 m
Reputation Power: 5
Thank, I would appreciate that. You can send it to my email address, jconnor@cornerstonesystems.net. I'll be working on in through the evening and hopefully I'll figure it out.
Reply With Quote
  #15  
Old July 16th, 2004, 08:38 AM
jccorner jccorner is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: May 2004
Posts: 45 jccorner User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 24 m
Reputation Power: 5
Finally...
I finally figured it out late last night and now am working with the server. jhlee24, I have you to thank, it was your reference to double quotes stated earlier that lead me down the right path. The only difference was that you posted the code for C# as: Select * \"MasterName\" to add double quotes to the table. Only thing is in VB.net, there are supposedly no "escape characters" so that syntax is not acceptable in the code. For anyone else using VB.net and are trying to connect to the Firebird server, here's the code:

Code:
Dim FBConn As FbConnection
Dim FBAdapter As FbDataAdapter
Dim FBCmd As FbCommand
Dim FBReader As FbDataReader

dbstr = "User=SYSDBA;Password=masterkey;Database=C:\WINNT\system32\Masterdb.fdb;DataSource=localhost;Port=305  0"

FBConn = New FbConnection(dbstr)
FBConn.Open()
FBDS.Clear()
FBCmd = New FbCommand("SELECT * FROM ""MstrCodes""", FBConn)
FBAdapter = New FbDataAdapter(FBCmd)
FBAdapter.Fill(FBDS, """MstrCodes""")
FBConn.Close()

FBDV = New DataView
FBDV.Table = FBDS.Tables("""MstrCodes""")


Since we can't use escape characters, you'll have to use two double quotes back to back to add double quotes to a string.
Reply With Quote
Reply
Page 1 of 2 1 2 >

Viewing: Dev Shed Forums > Databases > Firebird SQL Development > Dynamic SQL Error

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Threaded Mode