QODBC - trying to run stored procedure

1

I am using QODBC and have gotten excellent results. I have a SQLQuery table defined in dct a /TURBOSQL and PROP:SQL commands work very nicely but I am having a problem running any of their stored procedures which return a record set.

https://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2342/

Basic setup
Open(SQLQuery)
SQLQuery{PROP:SQL} = ‘Select * from customers’
LOOP
NEXT(SQLQuery); IF ERRORCODE(); BREAK.
…
END
CLOSE(SQLQuery)

for select statements this works fine, but when trying to call a stored procedure it say no records available or other errors.

I have tried
SQLQuery{PROP:SQL} = ‘sp_tables’
SQLQuery{PROP:SQL} = ‘call sp_tables’ – EXECUTE does not work either

this is what the trace shows for the last command

Preparing Statement 0EDA950H : {CALL sp_tables()}
Error Occurred: 37000 [QODBC] [sql syntax error] Expected lexical element not found: =

Nothing seems to work - any ideas?

2

Maybe try PROP:SQLRowSet instead?

5 Likes
3

That worked exactly the way I wanted — THANK YOU SO MUCH!

I have never used that Property in all these years- Learn something new every day.

4

Hi Jane for solution and Kevin for asking

Thanks for that!
Been using QODBC extensively for many years and never got the Stored procedures to work, but it’s never been critical.
That worked perfectly

thanks
Johan

1 Like
5

I am still on my 30 day trial and everything i want is working. (I will pay for 1 yr so I can continue my project)

I am so tired of using QB (I have 2020) and i refuse to go online or keep paying $600/yr for desktop subscription or i loose access to my data. I am switching to TPS layouts. I only use it for simple timecard/billing/invoices so many of the files i will never use but want everything out of QB

After importing the QB SQL Database using QODBC into my CW DCT I wrote a utility that takes all the information from different stored procedures and rewrites the DCTX to do the following

Correct any spelling mistakes and relationship mistakes
Gave all files meaning prefixes vs the ones from the import
Create any missing keys needed for relationships
Create any Alias’ that are needed
Create all the relationships defined in the sp_columns.Related_To
Create initial values / triggers for GUIDs and create\Modify date\time
Create .inc file will all the dropdown choices for any fields with them
Create connection string / File name path assignments (for tps)

I also then

export all the QB tables using jFiles automatically - like 2 lines of code
import them all back into TPS files using jFiles - like 2 lines of code

Then let CW Application wizard create all my screens to view my data (work in progress)

It’s not perfect but i am 10000% further than I would be if I was trying to do all this by hand and all this in 25 days of on/off working on it.

It has been an eye opener seeing all the relationship QB has.